MUPIP REPLICATE option to replicate trigger updates

Final Release Note

With the -trigupdate option of the MUPIP REPLICATE SOURCE START command, database updates made by triggers are included in the replication stream. By default they are not. Previously, there was no mechanism to replicate database updates made by triggers. Notes:

• An instance can have multiple Source Servers, some started with the -trigupdate option and some without it, depending on the type of replication needed by each connection.
• A Source Server started with the -trigupdate option does not replicate trigger definition changes made by MUPIP TRIGGER and $ZTRIGGER(). Having the same trigger definitions on both sides of a replication connection would at best result in duplication of updates, and in the worst case cause anomalies where updates on one side conflict with updates on the other side.
• While a Source Server started with the -trigupdate option replicates updates made by the ZTRIGGER command, it does not replicate the record for the ZTRIGGER command itself, as it would otherwise cause triggers to execute on the replicating secondary instance.
• The -trigupdate option requires the -secondary option, i.e., only the initial startup of an active Source Server, or switching a passive Source Server to an active role support the -trigupdate option.
• When switching an instance from a primary role to a secondary role where it will receive database updates generated by triggers, trigger definitions should be removed (see below) before starting replication, to avoid duplication and anomalies. Conversely, when switching an instance without triggers from a secondary role to a primary role, triggers need to be installed before it comes up in a primary role.
• When creating a new replicating secondary instance from a backup of another instance, with the intention of having the new instance replicated to by a Source Server using the -trigupdate option, we recommend removing triggers before bringing it online, and at least, reviewing trigger definitions for those that can result in redundant or conflicting updates.
• To remove triggers, turn replication off, remove triggers, and turn replication back on. This will preserve the Journal Sequence Number used by replication.
• The $ZTWORMHOLE intrinsic special variable can be used to pass information from an originating instance to a replicating instance and can be set inside a trigger. Depending on the requirement, this may be a simpler alternative.

The -zerobacklog option requires the -shutdown option to be specified, since the former is not meaningful without the latter, issuing a CLIERR error otherwise. Previously, MUPIP REPLICATE accepted the -zerobacklog option without -shutdown which is meaningless.

[#722 (closed)]

Description

Database updates made by triggers are not propagated by the replication stream because the design point was that database updates from triggers are derivable from the primary update. However, this means that times (and other data such as process ids) are not replicated and hence not easily recreated. The proposed enhancement adds an option when starting a Source Server to include trigger updates in the replication stream.

Draft Release Note

With the -trigupdate option of the MUPIP REPLICATE SOURCE START command, database updates made by triggers are included in the replication stream. By default they are not. Previously, there was no mechanism to replicate database updates made by triggers. Notes:

• An instance can have multiple Source Servers, some started with the -trigupdate option and some without it, depending on the type of replication needed by each connection.
• A Source Server started with the -trigupdate option does not replicate trigger definition changes made by MUPIP TRIGGER and $ZTRIGGER(). Having the same trigger definitions on both sides of a replication connection would at best result in duplication of updates, and in the worst case cause anomalies where updates on one side conflict with updates on the other side.
• While a Source Server started with the -trigupdate option replicates updates made by the ZTRIGGER command, it does not replicate the record for the ZTRIGGER command itself, as it would otherwise cause triggers to execute on the replicating secondary instance.
• The -trigupdate option requires the -secondary option, i.e., only the initial startup of an active Source Server, or switching a passive Source Server to an active role support the -trigupdate option.
• When switching an instance from a primary role to a secondary role where it will receive database updates generated by triggers, trigger definitions should be removed (see below) before starting replication, to avoid duplication and anomalies. Conversely, when switching an instance without triggers from a secondary role to a primary role, triggers need to be installed before it comes up in a primary role.
• When creating a new replicating secondary instance from a backup of another instance, with the intention of having the new instance replicated to by a Source Server using the -trigupdate option, we recommend removing triggers before bringing it online, and at least, reviewing trigger definitions for those that can result in redundant or conflicting updates.
• To remove triggers, turn replication off, remove triggers, and turn replication back on. This will preserve the Journal Sequence Number used by replication.
• The $ZTWORMHOLE intrinsic special variable can be used to pass information from an originating instance to a replicating instance and can be set inside a trigger. Depending on the requirement, this may be a simpler alternative.

The -zerobacklog option requires the -shutdown option to be specified, since the former is not meaningful without the latter, issuing a CLIERR error otherwise. Previously, MUPIP REPLICATE accepted the -zerobacklog option without -shutdown which is meaningless.

[#722 (closed)]