SQL Collection in Batch Workflows(3.1)

Owned by Jenni Wallin
May 10, 2023
3 min read

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.

SettingDescription

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 (3.0).

SQL Expression

The user enters an SQL statement specifying the query  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 SizeThe 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  MIM and a list of the general MIM parameters, see Administration and Management(3.0).

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(3.0).

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.Â