Replication Guide and Reference

Capture triggers for non-IBM sources

Capture triggers are used for replication from non-IBM databases. They capture changed data from a source table and make the changed data available for replication. Capture triggers perform the same task as the Capture program does for DB2, but in a different manner. DJRA generates the Capture triggers.

DJRA, working through DB2 DataJoiner, creates Capture triggers at the non-IBM source database when you define the source tables as replication sources. Capture triggers capture committed changes made to source data and place the captured changes into a staging table, called the consistent change data (CCD) table. The CCD table has a nickname in DB2 DataJoiner that programs that want to replicate the changes (for example, the Apply program) can access. See Staging data for more information about CCD tables.

There are three triggers for each source table: DELETE, UPDATE, and INSERT.

How the Capture triggers capture the data changes

The Capture triggers work with the following objects: the CCD table, the register control table, the pruning control table, and the register synchronization control table.

DJRA generates SQL (when you define a table as a replication source) that, when run:

• Creates Capture triggers on the source table.

• Creates the CCD table on the source server. There is one CCD table for each source table.

• Inserts a row into the register control table (to represent the new source table).

• Creates a nickname for the CCD table in the DB2 DataJoiner database.

Whenever a delete, update, or insert operation occurs at the defined source, a Capture trigger records the change into the CCD table. When the Capture triggers retrieve changed information, they can also obtain before and after column data to put into the CCD table.

The Apply program then reads the CCD table (through DB2 DataJoiner nicknames), copies the changes to the target server, and applies the changes to the target table. Figure 21 shows the relationship between the Capture triggers, the source table, the register control table, and the CCD table.

Figure 21. Capture Triggers at the Source Server

Capture Triggers and pre-existing triggers

When DJRA creates and places Capture triggers on a non-IBM database, you might encounter the following situations:

• On Oracle: If an attempt is made to create a Capture trigger on a table where there is a pre-existing trigger with the same name, or where the pre-existing trigger performs an identical event (insert-before, insert-after, delete-before, delete-after, update-before, update-after), Oracle issues the following message: ORA-04081 (trigger name already exists). If this error is generated, the Capture trigger is not created.

• On Informix: If an attempt is made to create a Capture trigger on a table where there is a pre-existing trigger with the same name, or where the pre-existing trigger performs an identical event (insert, delete, update), Informix issues an -741 error and will not create the Capture trigger.

• On Microsoft SQL Server or Sybase: If an attempt is made to create a Capture trigger on a table where there is a pre-existing trigger with the same name, or where the pre-existing trigger performs an identical event (insert, delete, update), Microsoft SQL Server and Sybase do not generate error or warning messages indicating a conflict. Microsoft SQL Server and Sybase replace the pre-existing trigger with the new Capture trigger. However, DJRA does check to see if a trigger already exists. If a trigger with the same events exists, DJRA creates the new triggers but all lines within the trigger body are commented out. You must determine how you want to merge pre-existing triggers with the new triggers. Then you can uncomment the lines in the new triggers.

If you anticipate conflict between DJRA's Capture triggers and pre-existing triggers, put the content of both triggers into one trigger. For each table event, append the pre-existing business trigger to the end of the Capture trigger script that is generated by DJRA.

[ DB2 List of Books | Search the DB2 Books ]