Migrating to SQL Replication Version 10.1 (Linux, UNIX, and Windows)

When you upgrade your servers to Db2® 10.1 for Linux®, UNIX, and Windows, you must migrate the SQL Replication control tables and change-data (CD) tables, and in some cases your consistent-change-data (CCD) tables, because of changes to the structure of Db2 recovery log records.

Log sequence numbers (LSN), which help the SQL Replication programs keep data synchronized between source and target, have lengthened from 10 bytes to 16 bytes so that Db2 can differentiate between multiple members in a Db2 pureScale® configuration.

You must migrate the Capture control tables and CD tables when Capture is upgraded to Version 10.1 and the Apply control tables when Apply is upgraded to Version 10.1 to handle 16-byte LSNs in the columns that store synchpoint data.

If you are not ready to upgrade all of the servers in your environment, SQL Replication Version 10.1 offers compatibility features that enable you to replicate between Version 10.1 and older versions:

• The new COMPATIBILITY column in the IBMSNAP_CAPPARMS control table enables SQL Replication to handle mixed-level servers. After Capture is upgraded to Version 10.1, you set COMPATIBILITY=0801 until you have upgraded all of the Apply programs that Capture works with to Version 10.1. This setting prompts the programs to continue to use 10-byte LSNs in all control tables. When you are ready, you set COMPATIBILITY=1001 and the programs begin using 16-byte LSNs.
  Important: Capture and Apply must be current in their processing before you before you upgrade Capture to Version 10.1. Capture must have read all the log records in the Db2 log and inserted all transactions into the CD table. The Db2 10.1 log read API does not read log records from local log files that were created in a prior version of Db2. The Apply program must have applied all the transactions in the CD table because the LSN fields before and after the migration to Version 10.1 are not compatible. Also, after you upgrade Capture to Version 10.1, you must start Capture using startmode=cold, regardless of whether the Apply program is upgraded.
• A Version 9 Apply program can work with an Apply control server that is at Db2 Version 10.1 as long as the Apply control tables are not migrated to Version 10.1.
• SQL Replication uses the new IBMSNAP_APPLEVEL table and its ARCH_LEVEL column to keep track of the level of the Apply control tables. For Version 10.1 the ARCH_LEVEL value should be 1001.
• The Capture and Apply migration scripts that are provided with Version 10.1 include SQL statements for altering the data length of synchpoint columns in CD and CCD tables to VARCHAR(16) FOR BIT DATA to handle the longer LSNs. The migration scripts also include statements for changing existing synchpoint values in the control tables, CD tables, and CCD tables so that the synchpoint values work with the longer data lengths.

Other considerations for planning your Version 10.1 migration:

Federated targets

Apply control servers that maintain federated targets do not require any extra steps during migration because all of the Apply control tables are on a Db2 for Linux, UNIX, and Windows database. The Version 10.1 migration does not affect federated targets.

External CCD tables

External CCD tables that are maintained by the Apply program are target CCD tables and can be migrated as part of the Apply control server migration (if the Capture program is upgraded to Version 10.1). External CCD tables that are maintained by the Q Apply program are supported in Version 10.1 Fix Pack 3 and later after they are migrated to use 16-byte LSN columns.

All other CCD tables do not need to be migrated. This includes external CCD tables that are maintained by IMS and federated sources (internal CCD tables). A CCD target table migration includes updates of its nickname and native DDL definitions.

External CCD tables that are also used as a source for subsequent SQL Replication targets cannot be migrated because old and new LSN values cannot be mixed. These tables will need to be fully refreshed.

Staged migration of LSN data

On the Apply side, control tables are migrated first and the version of the Apply control tables is reflected in the ARCH_LEVEL column of the IBMSNAP_APPLEVEL table. Migration of the LSN data can occur later for the appropriate subscription sets. When the Apply control server services multiple Capture servers, LSN data in the Apply control tables might include both 10-byte and 16-byte LSN values until all Capture servers have migrated to a compatibility value of 1001. Because of this, an Apply control server might go through more than one LSN migration step. For a subscription that is being processed by a Capture program that uses 16-byte LSNs, you must set the ARCH_LEVEL to 1001 in the ASN.IBMSNAP_SUBS_SET table.

On the Capture side, control tables are also migrated first. Migration of the LSN data can occur later for the appropriate registrations.

Partitioned databases

Capture from partitioned databases at Version 10.1 or newer is not supported.