SQL Forwarding Agent Configuration - Batch

Database

Profile defining the database that the agent is supposed to connect and forward data to.

When selecting the Browse button next to the field it will open a browser where one and only one database profile can be selected. For further information about database profile setup, see Database Profile.

UDR Type

The UDR type the agent accesses as input type.

When selecting the Browse button next to the field it will open the UDR Internal Format Browser and one and only one UDR type can be selected.

SQL Statement

The user enters an SQL statement that the system should send to the database.

By right clicking in the pane, selecting MIM Assistance..., the MIM Browser appears.

The user can select a MIM value to be used in the SQL query. 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 blue color as "$(Workflow.Batch Count)" in the text field.

By right clicking in the pane, selecting UDR Assistance..., the UDR Internal Format Browser appears.

The user can select a field from the UDR specified in the UDR Type selector. The name of the UDR field name for example "UDR.Fieldname" will be displayed in green color as "$(UDR.Fieldname)" in the text field. If the input type UDR is changed after writing the SQL syntax the GUI validation will fail (unless the different UDR's have identical field names). The field value will be used as an input variable in the SQL Statement in the same way as MIM values do.

There is support for Stored Procedures. When using the forward agent use JDBC to call a stored procedure in the same way as a normal call.

The exact supported syntax for stored procedures varies between databases. An example of a procedure with two input arguments could have a SQL statement looking like this:

Commit Criteria

Use Prepare Call

Enable this checkbox to allow the SQL Forwarding agent to use prepareCall when using the agent to conduct the SQL operations. When left unchecked, the SQL Forwarding agent will use preparedStatement for all its SQL operations.

Commit Window Size

The number of UDRs to be processed between each database commit command. This value may be used to tune the performance. If tables are small and contain no Binary Objects, the value may be set to a higher value than the default. The default is 1000.

A number field where it is possible to enter an integer number. If the check box is enabled the agent will call commit on the database after reaching the specified number of successful executions. It will also call commit when receiving endBatch. If the check box is disabled, the agent inserts every 500 UDRs and only does a commit when receiving endBatch.

Exception Handling

Route on SQL Exception

Check to prevent the workflow from aborting when selected exceptions occur. Such exceptions are filtered by the rule that you specify in the  Regular Expression Criteria editing pane. Instead of aborting the workflow due to these exceptions, the workflow proceeds to the agent to which you now can route the selected exceptions.

Clear to abort the workflow on the occurrence of any exception.

Upon Exception, route the entire executed batch instead of single UDR

Select this check box to prevent the workflow from retrying each UDR if prepareStatement.execute fails, causing an Exception. If you select this check box, an errorUDRList is routed instead of an errorUDR.

Regular Expression Criteria

Use the Java Regular Expression syntax convention when you enter the expression that selects the SQL error messages. The SQL errors that match these criteria, enable the agent to identify the data that should be routed further along the workflow.

When the agent identifies erroneous data it generates an Agent Message Event. For further information, see Agent Event.