SQL Forwarding in Real-Time 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.
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 connects and forwards data to. When selecting the Browse button next to the field it will open a browser where only one database profile can be selected. For further information about database profile setup, see Database (3.0). |
| UDR Type | The UDR type that 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 Using the MIM Browser, you can select a MIM value to be used in the SQL query. The value of the MIM is used in the SQL query during execution. The name of the MIM Value for example "Workflow.Workflow Name" is displayed in blue color as "$(Workflow.Workflow Name)" in the text field. Using the UDR Internal Format Browser by selecting UDR Assistance, you can select a field from the UDR specified in the UDR Type selector. The name of the UDR field name for example "UDR.Fieldname" is 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 fails (unless the different UDRs have identical field names). The field value is used as an input variable in the SQL Statement in the same way as MIM values are used. There is support for Stored Procedures. When using the forwarding 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 the following SQL statement: Example - SQL statement for a procedure with two input arguments CALL test_proc($(UDR.field1), $(UDR.field2)) Note! The statement syntax of the statement is not validated in the GUI, but references to MIM values are validated. If an incorrect SQL statement is entered, this generates an exception during runtime. |
| Volume (UDRs) | The number of UDRs to be processed between each database commit command. This value may be used to tune performance. If tables are small and contain no Binary Objects, the value may be set to a higher value than the default. Default is 1000. |
| Time (sec) | The commit window in seconds. If there is no commit within the time set, when the time set is reached, the agent performs a database commit. The default value is 60. |
| No of threads | The number of worker threads. Modify this option for performance tuning. The number of worker threads should be less than the connection pool size. The default value is 2. |
| Queue Max Size | The maximum number of batches of UDRs in the queue. The size depends on the configured volume. See the Volume (UDRs) option. The default value is 10000. |
| Queue full retry intervals (s) | If the queue is full, the agent waits for the configured interval before trying to insert data into the queue again. The default value is 5. |
| Queue full max retries | Set the maximum number of retries if the queue is full. If the number of retries is exceeded, an errorUDRList is sent out containing all the failed UDRs. The default value is 3. |
| UDR TTL time (ms) | The UDR Time to Live time in milliseconds. A UDR can only remain in the queue for the time set. If a UDR expires, the MIM value |
| Route on SQL Exception | Select this check box to route error information upon SQL exception. 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 can now 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.* |
| Retry Commit for every UDR in batch | In case of failure during an insert or commit, if you select this option, the agent retries for every single UDR in the batch. You determine the number of UDRs to be processed by setting the Volume (UDRs) option. |
| 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
Input/Output Data
The input/output data is the type of data an agent expects and delivers.
The agent routes out commitUDRs every time a commit is performed. How often a commit is performed is determined by the configuration of the Volume (UDRs) and Time (sec) options, which you set in the SQL forwarding agent configuration dialog.
The following fields are included in the commitUDR:
| Field name | Description |
|---|---|
commitTimestamp (long) | This field contains the timestamp in milliseconds when a commit occurs. |
lastUDR (DRUDR) | This field is populated with the last UDR in the batch that was committed. |
The agent routes out errorUDR or errorUDRList UDRs if an error occurs. See .3.2 Handling Erroneous UDRs - Real-Time.
MIM
For information about the   MIM and a list of the general MIM parameters, see Administration and Management(3.0).
Publishes
| MIM Name | Description |
|---|---|
| Agent Name (string) | This MIM parameter contains the name of the SQL forwarding agent. |
| Inbound UDRs (long) | This MIM parameter contains the UDRs routed to the agent. |
| No of discarded expired UDRs (int) | This MIM parameter contains the number of UDRs that have been discarded as they have exceed the configured TTL. |
| Outbound UDRs (long) | This MIM parameter contains the UDRs routed from the agent. |
Accesses
The agent does not access any MIM parameters.
Events
Agent Message Event
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.