Migrating to SQL Replication ARCH_LEVEL 1021 on Linux, UNIX, and Windows

When you upgrade your SQL Replication servers to DB2® Version 10.5 Fix Pack 7 and later on Linux, UNIX, and Windows, you must migrate the control tables to architecture level 1021.

Before you begin

The ARCH_LEVEL values in the IBMSNAP_CAPPARMS table at the Capture control server and the IBMSNAP_APPLEVEL table at the Apply control server must be 1001. If you are migrating from an earlier version, you must migrate the control tables to ARCH_LEVEL 1001 before you start this procedure.

About this task

ARCH_LEVEL value 1021 corresponds to InfoSphere® Data Replication Version 10.2.1, but this version supports DB2 for Linux, UNIX, and Windows sources and targets at V9.7, V10.1, V10.5, and V11.1.

For information on setting the COMPATIBILITY value in the IBMSNAP_CAPPARMS table to enable coexistence between different levels of Capture and Apply program, see SQL Replication compatibility for ARCH_LEVEL 1021 and 1001 on Linux, UNIX, and Windows.

With this procedure you upgrade both the Capture control server and Apply control server to ARCH_LEVEL 1021.

Quiesce all applications that update the source database.
Ensure that the Capture program has read all log records and inserted all replicated data into the CD table.
Ensure that the Apply program has applied all replicated data from the CD table.
Stop the Capture program.
Stop the Apply program.
On the Capture control server, upgrade the DB2 instance to Version 10.5 Fix Pack 7 or later. From this point, do not use the replication administration tools to modify your configuration (for example, add a registration) until the Capture control tables are migrated.
Locate the asncapluwv1021.sql migration script in the SQLLIB/samples/repl/mig1021/sql directory.
Change all instances of !capschema! to match your schema name.

Run the SQL statements in the script that pertain to the Capture control tables:

UPDATE !capschema!.IBMSNAP_CAPPARMS SET ARCH_LEVEL = '1021';
UPDATE !capschema!.IBMSNAP_CAPPARMS SET COMPATIBILITY = '1021'
ALTER TABLE !capschema!.IBMQREP_IGNTRANTRC ALTER COLUMN TRANSID SET DATA TYPE VARCHAR( 12) FOR BIT DATA;

ALTER TABLE ASN.IBMSNAP_CAPMON ADD NUM_END_OF_LOGS INTEGER;
ALTER TABLE ASN.IBMSNAP_CAPMON ADD LOGRDR_SLEEPTIME INTEGER;
ALTER TABLE ASN.IBMSNAP_CAPMON ADD NUM_LOGREAD_F_CALLS INTEGER;
ALTER TABLE ASN.IBMSNAP_CAPMON ADD TRANS_QUEUED INTEGER;
ALTER TABLE ASN.IBMSNAP_CAPMON ADD NUM_WARNTXS INTEGER;
ALTER TABLE ASN.IBMSNAP_CAPMON ADD NUM_WARNLOGAPI INTEGER;

Because the script changes the data type of the TRANSID column in the IBMQREP_IGNTRANTRC table, perform a REORG on IBMQREP_IGNTRANTRC at the Capture control server.
On the Apply server, upgrade the DB2 instance to Version 10.5 Fix Pack 7 or later. From this point, do not use the replication administration tools to modify your configuration (for example, add a subscription-set member) until the Apply control tables are migrated.
Locate the asnappluwv1021.sql migration script in the SQLLIB/samples/repl/mig1021/sql directory.
Run the Apply control table migration statement in the script:
```
UPDATE ASN.IBMSNAP_APPLEVEL SET ARCH_LEVEL = '1021';
```
Start the Capture program in one of the following modes, depending on which version you migrated from:
From ARCH_LEVEL 1001

Start Capture with startmode=warmsi.

From ARCH_LEVEL earlier than 1001
Start the Capture program with startmode=cold. A cold start prompts Capture to deactivate all registrations. If you want to avoid full refreshes of target tables that would be triggered by the Capture cold start, set the LOADX_TYPE column in the ASN.IBMSNAP_SUBS_MEMBR table to a value of 6 with the following SQL:
```
UPDATE ASN.IBMSNAP_SUBS_MEMBR SET LOADX_TYPE=6
WHERE apply_qual=apply_qualifier and SET_NAME=set_name
```
Start the Apply program with loadxit=y. Apply detects any inactive registrations and inserts CAPSTART signals to activate these registrations.
Resume your applications.