Data Merger Activity

The Data Merger activity performs the insert, update, and delete operations on the parent-child table. The configuration allows you to generate the required exception table SQL script file to create exception tables in the database. If an exception occurs while performing a database operation, the record is inserted in the created exception or opaque table.

General

Note: When you migrate a project created in an earlier version to 8.1.0 version, you must have to refactor it to be able to see the schema for Data Merger activity. To refactor your project, in TIBCO Business Studio, in the Project Explorer pane, right click the project name, select Refactor > Repair BusinessWorks Projects.

The General tab contains the following fields:

Field Literal Value/Process Property/Module Property? Description
Name No The name to be displayed as the label for the activity in the process.

See Entity Naming Conventions.

Configuration Resource Yes Shared configuration resource containing information about the ADB Configuration connection.
Schema No

The DBMerger schema which is configured with the schema table is based on the XSD generated by the DBMerger schema.

  • Create Exception Table: Applies the exception table script to the database.
  • Script Exception Table: Saves the exception table script to a specific file and can be applied to the database later. You are prompted to select the directory where the trigger files are to be created. Two files are generated at the specified location:
    • An SQL file containing the script for triggering to the database
    • A cleanup SQL file which contains a script to clean up the exception tables that are created.

    The filenames are created based on the name of the schema.

Note: You cannot apply or generate the exception tables if you are offline; you must be connected to the database to fetch the table metadata.
Note: TIBCO recommends not to change the table names after they are created. However, if it is required, perform the following steps before running the process:
  1. Back up the respective old tables and create the tables with the backup records.
  2. Use the Script Exception Table button on the Data Merger activity for generating the queries.

Description

Provide a short description for the activity.

Advanced

Data Merger Activity: Advanced Tab

This tab has the following fields:

Field Literal Value/Process Property/Module Property? Description
Operation Options
Timeout (sec) Yes Activity timeout in seconds.

Default: 180 seconds.

Upsert Yes Updates a row in the destination table if the row exists. If no such row exists, it performs an insert. For more information about the upsert operation behavior, see Upsert Operations on Parent-Child Tables.
Set Empty String to Default(Oracle) Yes Either on or off. The default setting is off. It works only if the following three criteria are met:
  • Oracle database is used
  • Insert operation is being performed
  • JDBC Property CatalogOptions property is set to 1

When you set a default value to a field in creating table, if the property value is set to on, the empty strings are changed to the default value.

Interpret Empty String as Null Yes When set to true, the column whose value is empty is treated as null and inserted as null.

When set to false, the column whose value is empty is treated as empty and inserted as empty.

Note: This field is applicable only when you use Oracle or MS SQL database.
Pre Commit Options
Stored Procedure Yes The value entered here represents the name of a stored procedure the merger calls after the database insert, update, or delete operation and before the commit.
Date And Time Pattern Options
Date Yes Enter the date in the yyyy-MM-dd format.
Time Yes Enter the time in the HH:mm:ss format.
Timestamp Yes Enter the timestamp in the yyyy-MM-dd HH:mm:ss.S format, where S is milliseconds.
Batch And Bulk Insert Options
Bulk Insert Size Yes All incoming messages to be inserted are stored until this size is reached. Then, a bulk insert operation is performed on the destination table.

Default: 1.

Batch Commit Size Yes This property specifies the number of records to be batched before invoking a commit operation.

The default value is 1. You can set this property if you want to enable batch commit.

Handle Bulk Insert Error Yes This property indicates whether the bulk insert error handling feature in the Data Merger activity is enabled or not.

Default: off.

Schema Mapping Options
Design Time Yes Specifies the design time database object schema. It can be a list of comma-separated schemas.
Runtime Yes Specifies the runtime database object schema. It can be a list of comma-separated schemas.

Input

This tab displays the input schema. You must map the input for the parent-child tables.

Output

This tab displays the output schema. If the table does not have a primary key, all columns of the table are displayed. However, if the table has a primary key, then only the primary key column is displayed.

The ADB_STATUS field displays the status of whether a record is processed or not. This field can have two values - Success and Fail.

The ADB_ERROR_TEXT field displays the details of the error. This error might be caused when any record fails due to an SQL exception and the ADB_STATUS field has value Fail.

Data Merger Activity: Output Tab

For more information about how different data types are mapped to the XSD type, refer to Output Mapping.

Fault

A fault is generated in the following scenarios:

  • When the XSD validation fails or the JDBC connection fails, the ADBValidationException occurs.
  • The ADBSQLException occurs and any one of the following scenarios is true:
    • The database connection is lost.
    • The exception and opaque table names are not specified.
    • The exception and opaque tables are not created in the database.
    • The "insert into exception table" fails and the opaque table is not specified or not created.
Data Merger Activity: Fault Tab

The Fault tab lists exceptions that are thrown by this activity.

Error Schema Element Data Type Description
ADBSQLException String

It has a msg and msgCode field for the exception.

ADBValidationExeption String

It has a msg and msgCode field for the exception.

msg String The error message returned by the plug-in.
msgCode String The error code returned by the plug-in.