Changing columns in the replication key

You can change which columns make up the replication key without having to stop replication or redefine the Q subscription.

About this task

About this task

This feature allows you to dynamically add, drop, and reorder the columns that make up the key, which replication uses to uniquely identify rows and prevent conflicts. You can add columns to the key at any position.

You use an SQL signal insert to specify the makeup of the key. The replication programs automatically change the key.

Restrictions

  • To use this feature on z/OS®, the Q Capture and Q Apply programs must be at function level 1140.103. On Linux®, UNIX, and Windows, the programs must be at Db2® 11.5.4 and function level 105.
  • This feature is not supported for peer-to-peer replication or stored procedure targets.
  • Replication key columns cannot be large object (LOB) or XML columns.
  • All columns that are used in the key must be part of the Q subscription.
  • No more than 40 replications key columns can be modified in a single transaction.

Procedure

Procedure

Insert a REDEFINE_REPL_KEY signal into the IBMQREP_SIGNAL table at the Q Capture server.
Within the signal, use the SIGNAL_INPUT_IN field to specify the Q subscription name and a semicolon-separated list of columns that you want to make up the redefined replication key.

For example, the following signal insert specifies that one Q subscription name (T2001) and four columns (HIST_INT2;HIST_INT3;HIST_CHAR10;HIST_CHAR6) should be used as the redefined replication key:

INSERT INTO capture_schema.IBMQREP_SIGNAL(SIGNAL_TIME, SIGNAL_TYPE,
   SIGNAL_SUBTYPE, SIGNAL_INPUT_IN, SIGNAL_STATE)
   VALUES (CURRENT_TIMESTAMP, 'CMD', 'REDEFINE_REPL_KEY',
   'T20001;HIST_INT2;HIST_INT3;HIST_CHAR10;HIST_CHAR6', 'P');