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 update CD table
structures when Capture is upgraded to Version 10.1 and the Apply
control tables when Apply is upgraded to Version 10.1. If both Capture
and Apply are at Version 10.1, you also must update the values in
the control tables and your CD tables and target CCD tables 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.
- 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 cannot
be migrated in Version 10.1 at this time.
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.
Considerations for DB2 pureScale feature with multiple
members
If you use the DB2 pureScale feature with multiple
members as a source database, you must upgrade the Apply program to
Version 10.1 and migrate the Apply control tables to the Version 10.1
level (ARCH_LEVEL 1001). Upgrading Apply and migrating the control
tables enables Apply to work with the 16-byte LSNs that a Version
10.1 Capture program uses in a multi-member system. The old, 10-byte
LSN columns cannot hold the information that is needed for multiple
members.
A DB2 10.1 for Linux, UNIX, and Windows source
with multiple members cannot be a replication source for targets on DB2 for z/OS® or IBM® i.
Migrating from a single-member source to multiple
members
When a source server is moved from DB2 10.1 for Linux, UNIX, and Windows with a single pureScale member to a multi-member configuration,
existing LSN data in the following locations must be migrated by concatenating
six hexadecimal zeros before each LSN value:
- Capture control tables
- CD tables
- Apply control tables
- Target CCD tables
The LSN data migration is required to ensure the accuracy
of ORDER BY queries because strings are sorted from left to right.
The Capture migration script, SQLLIB/samples/repl/mig10/sql/asncapluwv10.sql,
includes statements for migrating existing data in the Capture control
tables and CD tables. The Apply migration script, SQLLIB/samples/repl/mig10/sql/asnappluwv10.sql,
includes statements for migrating existing data in the Apply control
tables and CCD tables.
These migrations are required before
you start capturing from a multi-member pureScale instance.
SQL Replication