SQL Forwarding in Batch Workflows(3.3)
- Jenni Wallin
The SQL forwarding agent inserts UDR data into a database table according to your definitions of mapping between UDR fields and database table columns. This section is for describing the Batch workflow version of the SQL forwarding agent. For the Real-Time version, you can refer to SQL Forwarding in Real-Time Workflows(3.3).
The agent also enables you to populate database columns with MIM values either by using a plain SQL statement, or by invoking a stored procedure that inserts the data.
Note!
Supported database commands depend on the JDBC driver of the database.
Configuration
SQL Tab
The SQL tab contains configuration data that is related to the target database and the UDR Type.
| Setting | Description |
|---|---|
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 (3.0). |
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 a SQL statement 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: Example CALL test_proc($(UDR.field1), $(UDR.field2)) Note! The statement syntax of the statement will not be validated in the GUI, but references to MIM values are validated. If an incorrect SQL statement is entered this will generate an exception during runtime. |
Commit Window Size | The number of UDRs to be processed between each database 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. |
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 that you now can route the selected exceptions to. Note! Since the error message contains linefeed, the regular expression has to adjusted according to this. Start the regular expression with "(?s)" to ignore linefeed, for example: (?s).*ORA-001.* Clear to abort the workflow on the occurrence of any exception. |
| Upon Exception, route 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 this 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(3.0). |
Note!
specific database tables from the Platform database should never be utilized as targets for output as this might cause severe damage to the system in terms of data corruption that in turn might make the system unusable.
Transaction Behavior
Emits
This agent does not emit anything.
Retrieves
The database transaction in the SQL forwarding agent is not consistent with the batch transaction behavior, that is the normal batch transaction safety is not guaranteed for this agent.
If a workflow aborts, the database transaction may have been partly or completely done, however the input file will be reprocessed and consequently can cause duplication of data if an INSERT statement is used in the forwarding agent.
Input/Output Data
The input/output data is the type of data an agent expects and delivers.
The agent consumes the selected UDR type.
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
There are no message events for this agent.
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: sql-statement
Example - SQL debug event
SQL: INSERT INTO test_table (NUM, DATA) VALUES (?, ?)
The debug message is sent when the SQL agent creates its SQL string to send to the database.