What are the steps involved in implementing autoadd of sequences? (4299874)

What are the steps involved in implementing autoadd of sequences?

What are the steps involved in implementing autoadd of sequences?

General information. 

There are two Capture parameters that need to be set together if auto add of sequences is desired. The parameters are SP_OCT_AUTOADD_SEQ and SP_ OCT_AUTOADD_ENABLE. They both need to be set to 1 on source sp_ctrl as below:

sp_ctrl>set param SP_ OCT_AUTOADD_ENABLE 1

sp_ctrl>set param SP_OCT_AUTOADD_SEQ 1

The parameters are live and take effect immediately.

In addition to the above, the active config file should contain the wildcard entries along with the keyword “expand”.  Henceforth, any sequence added on the source will be brought into replication without any further action required on the part of the user.

The parameter SP_ OCT_AUTOADD_ENABLE is a pre-requisite for enabling auto add functionality. Out of the two type of objects that are supported for replication, namely, tables and sequences, the parameter will enable the auto add for tables without the need to have to set any additional parameter. To include the sequences in auto add functionality, the parameter SP_OCT_AUTOADD_SEQ needs to be enabled by setting it to 1 as well. The explanation behind having to set an additional parameter for sequences and not for the tables is, sequences are not replicated in all strategies. For example, reporting or peer to peer environment do not require sequence replication. So this parameter serves as a barrier and allows user to control the auto add for this type of object. The auto add is commonly used in all kind of replication strategies, and so that the feature does not pick up sequences by default, the parameter SP_OCT_AUTOADD_SEQ is devised.

Here is the description of the two parameters as taken from the Reference Guide: SP_OCT_AUTOADD_SEQ This parameter determines whether SharePlex automatically adds sequences to replication when they are created after activation. When enabled, it causes SharePlex to replicate the CREATE statement if the name of the sequence satisfies a wildcard in the active configuration file. SharePlex then maintains the object on the target throughout future DDL and DML changes. To auto-add sequences, both this parameter and the SP_ OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.3. IMPORTANT! To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table. Default: 0 (off) Range of valid values: 0 or 1 (flag) Takes effect: immediately For other details on the auto add features, please refer to SharePlex Administrator Guide and SharePlex Reference Guide.