Database Collection Agent Configuration(4.0)

You open the Database collection agent configuration dialog from a workflow configuration: you can right-click the agent icon and select Configuration..., or double-click the agent icon.

Source Tab

The Source tab contains configurations related to the placement and handling of the source database table and its data, as well as the UDR type to be created and populated by the agent.

SettingDescription

UDR Type

Type of UDR to be created and populated.

Database

Profile name of the database that the agent will connect to and retrieve data from. The list is populated each time the configuration dialog is opened. For further information about database profile setup, see Database (3.0).

Refresh must be selected if changes have been made in the customer database. This will update the presented information in the Source tab.

Note!

The Database collection 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.

Table Name

Name of the working table in the selected Database, in which the data to be collected resides. The list is populated each time a new Database is selected. For further information and an example of a working table, see Working Table in Tables and Stored Procedures(4.0).

Note!

For Oracle the default schema is omitted; the name of a table that belongs to the current database user appears without the schema or user prefix.

Transaction ID Column

Name of the column in the selected Table, which is utilized for the transaction ID. The list is populated each time a Table Name is selected. The column must be of the data type number, with at least twelve digits.

Remove

If enabled, this option will remove the collected data rows from the working table. If you select this option, you can also configure the Commit Window Size in the Advanced tab, which is 1000 by default.

Mark as Collected

If enabled, this option will assign the value -1 to the transaction ID Column for all the collected rows. The default commit window size is 1000.

Run SP

If enabled, this option executes a user defined stored procedure that is responsible for the handling, most often removal, of the collected data.

Note!

It is important that this procedure actually deletes the data or sets the Transaction ID to -1, to avoid the data being recollected.

For further information and an example of such a stored procedure, see After Collection Stored Procedure in Tables and Stored Procedures(4.0).

Ignore

Select to have the collected data remain in the table even after collection. Note that while the data state remains unchanged after collection, the transaction ID value is updated.

By keeping the data in the table you can collect it repeatedly while designing and testing a workflow, for example.

Assignment Tab

The Assignment tab contains the mapping of column values to UDR fields. The content and use of this tab is described in detail in Assignments in Database Configuration (4.0).

If the Source 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 clicked for the assignments to be updated with the configurations in the Source tab.

Note!

Potential changes in the database table will not be visible until the Refresh button for the database, in the Source tab, has been clicked.

Only the value types UDR Field, To UDR and NULL, described in Value Types in Database Configuration (4.0), are available for selection.

Condition Tab

In the Condition tab, query constraints may be added to limit the selection of data. The statement must follow the standard SQL WHERE-clause syntax, except for the initial where and the final semi-colon (;) which are automatically appended to the entered condition statement. It is, for instance, possible to include an order by statement to get the rows sorted.

The condition statement may contain dynamic parameters, represented by question marks that in run-time will be replaced by a value. If the text area contains question marks,  Assign Parameters...  must be selected, to be able to assign values to these parameters. The assignments are made in the Parameter Editor dialog.

In this dialog each parameter, represented as a question mark in the condition statement, appears as one row. The value types available are MIM Entry and Constant. Since constant values are also possible to be given directly in the conditionstatement, MIM Entry is most likely to be used here.

Advanced Tab

The Advanced tab contains a setting for performance tuning and allows viewing of the generated SQL statement, based on the configuration in the Source, Assignment and Condition tabs.

ItemDescription

Commit Window Size

The number of UDRs (rows) to be removed between each database commit command. This value is used to tune the performance. If tables are small and contain no Binary Objects, the value may be set higher 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.

This setting is only applicable if the Remove option is selected in the Source tab.

Generated SQL Statement

In this area the SQL statement to be used to query the database is shown. It may be used for debug purposes or for pure interest.

In order for the statement to appear, the Source and Assignment tabs have to be properly configured. If not, information about the first detected missing or erroneous setting is displayed.