Database Forwarding Agent Configuration

Owned by Jenni Wallin
Last updated: Sept 12, 2024 by 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.

SettingDescription

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.

  • Direct - Insertion of data is performed directly.

  • Stored Procedure - Insertion of data is performed 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 Typeis 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 clean up.

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.

Note!

The correct name of the SP Target Table  must be selected, or else a Database collection agent will be able to collect pending data that is not supposed to be collected. This may cause data duplication.

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.

Note!

specific database tables from the Platform database must never be utilized as targets for output. This may cause severe damage to the system in terms of data corruption that in turn may make the system unusable.

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.

Note!

Potential changes in the database table will not be visible until you click Refresh 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.


Note!

When using Function as Value Type, question marks embedded in strings are not allowed. The system will interpret a question mark as a parameter.

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.
 

SettingDescription

Commit Window Size

The number of UDRs (rows) to be inserted or removed 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. Default is 1000. The window size can be set to any value between 1-60000, where setting 1 means that commit is performed after 1 UDR, and setting 60000 means that commit is performed after 60000 UDRs.

Rows are inserted for each UDR that is fed to the agent. All UDRs are stored in memory between each database commit command, to enable rollback. Rows are removed at the next workflow startup in case of a crash recovery.

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.