When you migrate an SQL Replication environment to Version 10.1, you need to run a a few additional migration steps to handle special cases that could cause data to be pruned prematurely and not replicated to the target.
With the changes in log sequence numbers in DB2 for Linux, UNIX, and Windows Version 10.1, the value in the SYNCHPOINT column in the IBMSNAP_PRUNE_SET table can be much lower in V10.1 than it was in V9.7.
For example, a SYNCHPOINT value that is very high in Version 9.7 such as 514cc771000000010000 can be lower in Version 10.1, such as 000000000ce6d01a9ae4.
If you migrate the control tables and allow applications to begin updating your source tables before the Apply program starts, data could be inserted into the CD tables and pruned before Apply can fetch the data and apply it to targets.
Resolving the problem
To avoid this potential problem, follow these steps in addition to the documented steps for migrating to Version 10.1:
1. Make sure the Capture and Apply programs are stopped.
2. After migrating the Capture control tables to Version 10.1, run the following SQL statement on the Capture control server:
UPDATE schema.IBMSNAP_PRUNE_SET SET SYNCHPOINT=x'00000000000000000000000000000000' where apply_qual=apply_qualifier and set_name=set_name and target_server=target_server
3. Start Capture with startmode=cold.
4. On the Apply control server, run the following SQL statement to update the SYNCHPOINT and SYNCHTIME columns in the ASN.IBMSNAP_SUBS_SET table:
UPDATE ASN.IBMSNAP_SUBS_SET SET SYNCHPOINT=NULL, SYNCHTIME=NULL where apply_qual=apply_qualifier and set_name=set_name and whos_on_first= whos_on_first
5. Optional: 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 for each subscription set with the following SQL statement:
UPDATE ASN.IBMSNAP_SUBS_MEMBR SET LOADX_TYPE=6 WHERE apply_qual=apply_qualifier and SET_NAME=set_name
6. Start the Apply program with loadxit=y.