Using the target-key-change option

If you want to allow changes to source table columns that are part of the target key for replication, you can use the target-key-change option so that the Apply program correctly processes these changes.

About this task

When setting up replication you specify a target table index that Apply uses to locate rows to update. If changes are made to source table columns that are part of this target key, Apply needs to know the before value of the column to locate the correct target row. You define the source table registration to include before images for these columns, and Apply reads the before values from the CD or CCD table. You also exclude the before-image columns from the subscription-set member so that they are not replicated to the target.

When you specify that you want changes to target key columns to be processed by using before-image values, Apply is able to find the row with the old key value and update the row by using the new values.

Restrictions

You cannot use the target-key-change option for source tables that are registered to capture updates as delete/insert pairs.
You cannot map an expression in a source table to a key column in a target table if the Apply program updates the target table based on the before images of the target key column (that is, if the TARGET_KEY_CHG column of the IBMSNAP_SUBS_MEMBR table has a value of Y for that target table).

Procedure

Define the source registration to capture before-image values of any source table columns that are part of the target key, or alter any existing registration to include before-images for these columns.
When a source key column is changed, the Apply program reads these before-image values from the CD or CCD table to determine which target row to update.

When you create the subscription-set member, specify the target-key-change option, and exclude the before-image columns. Use one of these methods:

Method Description

Method	Description
ASNCLP command-line program	In the CREATE MEMBER command, use the TGT KEY CHANGE ON keyword, and also use the EXCLUDE keyword for any before-image columns that are part of the key. These columns are not needed in the target table. The following example excludes the before-image column XPART_VALUE from being replicated to the target: `CREATE MEMBER IN SETNAME XSET APPLYQUAL AQCOPY2 ACTIVATE YES SOURCE ADMF001.DEC_TABLE2 TARGET NAME ADMF001.TG_DEC_TABLE2X DEFINITION IN DB JTRGDB TST2D2 TGT KEY CHANGE ON EXCLUDE(XPART_VALUE);`
Replication Center	Follow these steps: Right-click the subscription set and select Add Member. In the Add Members to Subscription Sets window, click Change. In the Member Properties window, click the Column Selection tab and remove any before-image columns from the Selected Columns list. Also in the Member Properties window, click the Target-Table Index tab and click Let the Apply program use before-image values to update target-key columns.

ASNCLP command-line program

In the CREATE MEMBER command, use the TGT KEY CHANGE ON keyword, and also use the EXCLUDE keyword for any before-image columns that are part of the key. These columns are not needed in the target table.

The following example excludes the before-image column XPART_VALUE from being replicated to the target:

CREATE MEMBER IN SETNAME XSET APPLYQUAL AQCOPY2 ACTIVATE YES SOURCE ADMF001.DEC_TABLE2
TARGET NAME ADMF001.TG_DEC_TABLE2X DEFINITION IN DB JTRGDB TST2D2 TGT KEY CHANGE ON
EXCLUDE(XPART_VALUE);

Replication Center

Follow these steps:

Right-click the subscription set and select Add Member.
In the Add Members to Subscription Sets window, click Change.
In the Member Properties window, click the Column Selection tab and remove any before-image columns from the Selected Columns list.
Also in the Member Properties window, click the Target-Table Index tab and click Let the Apply program use before-image values to update target-key columns.

When you follow these steps, the Apply program inserts a row in the ASN.IBMSNAP_SUBS_COLS table for each before-image column with a COL_TYPE value of P. This value means that the before-image column is used in a predicate to locate the correct row to update in the target table when a source key column is changed.