SQL Collection in Batch Workflows(4.3)
- Jenni Wallin
The SQL collection agent collects rows from any database table and inserts them as UDRs into a workflow.
When the workflow starts, the agent will execute a query in SQL, based on the user configuration and retrieve all rows matching the SQL statement. For each row retrieved, a UDR will be created and populated according to the mapping made in the SQL collection agent configuration window.
Note!
Supported database commands depends on the JDBC driver of the database.
Configuration
SQL Tab
The SQL tab contains configurations related to the SQL query to use to retrieve information from the source database, as well as the UDR type to be created and how the UDRs will be populated by the agent.
Setting | Description |
|---|---|
Database | Profile name of the database that the agent will connect to and retrieve data from. For further information about database profile setup, see Database(4.2). |
SQL Expression | The user enters an SQL statement specifying the query Usage Engine should send to the database. The user can select a MIM value to be used in the SQL query by using the MIM browser. The value of the MIM will be used in the SQL query during execution. The name of the MIM Value for example "Workflow.Batch Count" will be displayed in the color blue as "$(Workflow.Batch Count)" in the text field. There is support for Stored Procedures. When using the collection agent to produce output from the procedure the JDBC support for output parameters is used. The character "?" is used to mark an output parameter in the SQL statement in the agent. An example of a procedure with one input argument and one output argument could have a SQL statement looking like this: CALL test_proc( $(Analysis_1.TestMIM), ? )The procedure will be called and the value of the output parameter ("?") will be assigned to the configured UDR field. When using output parameters only one UDR will be produced in the batch. The exact supported syntax for stored procedures varies between databases. For example calling an Oracle function can be done via: begin ? := test_func( ); end;Note!The statement syntax of the statement will not be validated in the GUI, but references to MIM values are validated. If a incorrect SQL statement is entered this will generate an exception during runtime. |
UDR Type | Type of UDR mapped from the result set and routed into the workflow. When selecting the Browse button next to the field the UDR Internal Format Browser will open and one and only one UDR type can be selected. |
ResultSet Size | The result set size gives the JDBC driver a hint to the number of rows to fetch from the database when more rows are required for the ResultSet object. If you set the ResultSet Size to 0, the JDBC driver decides the best size. |
UDR Fields | The table represents the mapping from the result set, returned when executing the statement entered in the SQL field to a specified Value in the UDR. |
Transaction Behavior
Emits
The agent emits commands changing the state of the file currently processed.
Command | Description |
|---|---|
Begin Batch | The agent will emit beginBatch before the first UDR from the result set is routed into the workflow. |
End Batch | The agent will emit endBatch after the last row in the result set has been mapped to a UDR and routed into the workflow. |
Retrieves
The agent retrieves commands from other agents and based on them generates a state change of the file currently processed.
Command | Description |
|---|---|
Hint End Batch | When hintEndBatch is called the agent will call endBatch followed by beginBatch (if more records exists in the result set). It will then continue to process the result set. |
Cancel Batch | Cancel Batch is not supported by the agent. |
Input/Output Data
The intro/output data is the type of data an agent expects and delivers.
The agent produces the UDR type selected from the UDR Type in the SQL Tab in the configuration.
MIM
For information about the Usage Engine MIM and a list of the general MIM parameters, see Administration and Management(4.1) .
The agent does not publish nor access any MIM parameters.
Events
Agent Message Events
An information message from the agent, stated according to the configuration done in the Event Notification Editor.
For further information about the agent message event type, see 4.3.15 Agent Event.
Ready with batch afterÂ
XÂ UDRs.Reported when a complete batch is collected.
Debug Events
Debug messages are dispatched in debug mode. During execution, the messages are displayed in the Workflow Monitor.
You can configure Event Notifications that are triggered when a debug message is dispatched. For further information about the debug event type, see Debug Event(4.1).
The agent produces the following debug events:
SQL: XXX
The debug message is sent when the SQL agent creates its SQL string to send to the database.Â
Â