Overview of Trigger-based Transactional Replications

A database trigger is code that is automatically executed in response to certain events on a database table. Syniti Data Replication supports trigger-based transactional replications (mirroring or synchronization) for a number of databases.

If you plan to define a trigger-based replication (mirroring or synchronization), you need to provide information in the Source and/or Target Connection wizards so that triggers can be created to log table changes for replication.

For each table involved in the replication, Syniti DR creates 3 triggers in the source table that fire when a specific event occurs on a record:

  • INSERT trigger which fires when a new record is being inserted in the table

  • UPDATE trigger which fires when a record is modified

  • DELETE trigger which fires when a record is deleted

If the replication is later deleted, the triggers are removed by Syniti DR. However, note that if you change a replication from mirroring to refresh, the triggers on the source table are not deleted. All transactions will continue to be recorded in the log tables. If you are not planning to reset the replication to mirroring, it is better to delete the replication, so that the triggers are removed, and create a new refresh replication.

Data retrieved using the triggers is stored in log tables that are specified in your Source/Target connection. The master log table can be an existing table or one created specifically to hold Syniti DR information. It contains general information about the transactions, like user name, timestamp, table name. A log table (_DBM__LOG_x) is also created for  each source table in the replication, and contains the data changes identified by the triggers, as well as trigger objects  _DBM__TRG_OBJS.

Note that Syniti DR does not create a tablespace. If you want to have a table space named SYNITIDR, you must create it beforehand using a SQL tool such as the Execute SQL Query dialog in the Management Center. Run a statement like

CREATE TABLESPACE SYNITIDR

When creating a connection, it is important to set the retention time to keep the log table size under control. The higher the value, the more data is kept in the log tables. Try to estimate the number of transactions occurring in all the source tables during a retention period and be sure that the database and table space have enough storage capacity for all those transactions. The Replication Agent cleans up the log tables periodically, based on the retention setting in the connection dialog. If the engine is not running, the log tables are not cleaned up. This might create space problems in the database as the logs grow in size. If you stop the engine and you are not planning to run it again, be sure to remove all the mirroring synchronization replications.

In addition, if you have many table replications in a single group, using a single connection, all the replications share a master log table. Access to the log table for each source table can become a bottleneck if there are many transactions using the same master log and log tables. Syniti DR may report errors about locked tables during replication. Although Syniti DR is able to recover from these errors and continue replicating, a better approach is to prevent the errors by splitting the replications into multiple groups with multiple connections and multiple master log tables.  First, create multiple source connections to the database. Use the Transactional Setup field in the Connection Properties of each connection to open the Manage Transactional Log Settings dialog and create a new master log table for each connection.

During replication:

  • When a record is inserted in the source table, the INSERT trigger fires and inserts one record in the master table and one record in the log table associated with the source table. The record inserted in the log table contains all the original values of the INSERT statement.

  • When a record is deleted from a table, the DELETE trigger fires and inserts one record in the master table and one record in the log table associated with the source table. The record inserted in the log table contains the key values of the deleted record.

  • When a record is updated, the UPDATE trigger fires and inserts one record in the master table and two records in the log table associated with the source table. The two records inserted in the log table contain all the record values before and all the records after the update.

Your system administrator needs to create and define appropriate table spaces and databases to hold the log tables. They should be large enough to handle the expected amount of replication data.