Database Forwarding Agent Configuration
- Yeok Hong Ng (Deactivated)
- Kevin Wu
- Jenni Wallin
- Chee Hove Teng
You open the Database forwarding agent configuration dialog from a workflow configuration. To open the Database forwarding agent configuration, click Build → New Configuration. Select Workflow from the Configurations dialog. When prompted to select workflow type, select Batch. Click Add Agent and select Database from the Forwarding tab of the Agent Selection dialog.
Target Tab
Database forwarding agent configuration dialog - Target tab
The Target tab contains configurations related to the target database table and the UDR type that will populate it with data.
Setting | Description |
|---|---|
UDR Type | Type of UDR to populate the target database table. |
Database | Profile defining the database that the agent is supposed to connect and forward data to. The list is populated each time the configuration dialog is opened. For further information about database profile setup, see Database Profile. Note!When you change the Database profile or update the content of the database. Click the Refresh button to update the information that is displayed in the Target tab. The Database forwarding agent does not support Fast Connection Failover (FCF) used when using an Oracle RAC enabled database for the database agent. |
Use Default Database Schema | Select this check box to use the default database schema for the chosen database and user. Note!This is not applicable for all database types. Use Default Database Schema is available for selection only when accessing Oracle databases. Tables within the default schema will be listed without schema prefix. |
Access Type | Determines if the insertion of data is to be performed directly into the target table, or via a stored procedure.
|
Table Name or SP Name | Depending on the selected Access Type, the target database table name, or the stored procedure name, is selected. The list is populated each time a new Database or Access Type is selected. For further information and an example of a working table, see Working Table in Tables and Stored Procedures. For further information about the stored procedure, see Database Forwarding Target Stored Procedure in Tables and Stored Procedures. Note!For Oracle the default schema is omitted; the name of a table or SP that belongs to the current database user appears without the schema prefix. |
Transaction ID Column | Name of the column in the selected table, or the parameter from the selected stored procedure, which is used for the Transaction ID Column. The list is populated each time a Table Name or SP Name is selected. The column must be of the data type number for Oracle and bigint for PostgreSQL, SAP HANA, MSSQL and Sybase, which can support at least 12 digits. |
Cleanup SP | If the selected Access Type is Stored Procedure, the agent does not automatically clean up the target table, in case of a workflow abortion (Cancel Batch). If that is the case, the customer must supply a stored procedure that manages the cleanup. For further information and an example of a Cleanup Stored Procedure, see Cleanup Stored Procedure in Tables and Stored Procedures. |
SP Target Table | Name of the target table for the stored procedure. This field is only enabled if the >Access Type is Stored Procedure. If this agent is chained with a Database collection agent in another workflow, both agents need to be aware of the mutual table. In the collection agent, a table to collect from is always selected. However, in the forwarding agent, it is possible to select the update of the table to be done via a stored procedure. If that is the case, the target table for the stored procedure must be selected here. For further information, see Pending Transaction Table in Inter-Workflow Communication, Using Database Agents. |
Run SP | If enabled, this option causes a user defined stored procedure to be called when the forwarding process terminates. It will then receive the transaction ID for the forwarded rows as input. This option is used for transaction safety when the table is read from another system, to ensure no temporary rows are read. Rows are classified as temporary until End Batch is reached. In case of a crash before End Batch is reached, the workflow needs to be restarted for the temporary rows to be expunged. |
Assignment Tab
Database forwarding agent configuration dialog - Assignment tab
The Assignment tab contains the assignment of values to each column or stored procedure parameter. The content and use of this tab is described further in Assignments in Mapping Assignments between Database Fields and UDR Fields.
The Column Name column does not necessarily contain column names. If Stored Procedure is selected as the Access Type, this column will hold the names of all incoming parameters that the stored procedure expects.
If the Target tab is correctly configured and the Assignment tab is selected, the table will automatically be populated, as if Refresh was clicked. If assignments already exist in the Assignment tab, then Refresh must be manually selected, for the assignments to be updated with the configurations in the Target tab.
All Value Types, described in Value Types in Mapping Assignments between Database Fields and UDR Fields, except for To UDR, are available for selection.
Advanced Tab
Database forwarding agent configuration dialog - Advanced tab
The Advanced tab contains a setting for performance tuning and viewing the generated SQL statement, based on the configuration in the Target and Assignment tabs.
Setting | Description |
|---|---|
Commit Window Size | The number of UDRs (rows) to be inserted or removed between each database Rows are inserted for each UDR that is fed to the agent. All UDRs are stored in memory between each database |
General SQL Statement | In this area, the SQL statement that will be used to populate the database, is shown. This field may not be edited, however, it is useful for debug purposes or for pure interest. In order for the statement to appear, the Target and Assignment tabs have to be properly configured, or else information about the first detected missing or erroneous setting is displayed. |