DB2 Replication Pruning

When you are pruning your replication tables using the asncmd prune command, the process uses the database logs. If the data in your change data tables and/or UOW table is large, logs will get filled up. This will cause incomplete pruning of the tables in addition to taking up the log space from other applications resulting in those applications to fail.

There are a few different ways to get out of the situation (where pruning requires more log space than is available):

A) Temporarily increase the amount of log space available.

B) Check the DATA_CAPTURE set for the CD table in SYSCAT.TABLES. If
it is Y, change it to N, that way the entries logged in the transaction log as a result of DELETE issued by PRUNE will be much smaller and might be contained in the present size of the log file available.

C) Quiesce the source tables. Allow Capture to catch up with the log
so that all data is captured. Stop Capture. Allow Apply to catch up and apply all data (subs_set synchpoint = register table synchpoint where global_record ='Y'). Now all data is subject to pruning. Drop and recreate the cd and uow tables. This causes zero logging and zero guesswork.

D) Work around this problem by pruning changes manually:
- Start Capture with the NOPRUNE parm so that pruning is NOT done
automatically.
- Start Apply so that the captured changes are applied to the targets.
Changes cannot be pruned until they have been applied.
- Stop Capture so that you can manually delete rows from the CD tables
- For each CD table, issue the following:

DELETE FROM <cd_table> CD
WHERE CD.IBMSNAP_UOWID IN
(SELECT UOW.IBMSNAP_UOWID FROM ASN.IBMSNAP_UOW UOW
WHERE UOW.IBMSNAP_COMMITSEQ <=
(SELECT MIN(PC.SYNCHPOINT) FROM ASN.IBMSNAP_PRUNCNTL PC))

- Then, for the UOW table, issue:

DELETE FROM ASN.IBMSNAP_UOW UOW
WHERE UOW.IBMSNAP_COMMITSEQ <=
(SELECT MIN(PC.SYNCHPOINT) FROM ASN.IBMSNAP_PRUNCNTL PC)

- Restart Capture


E) Option D may still need a lot of log space, so you can break the
manual deletes down into smaller bits :

- Stop Capture and Apply. Keep Capture down until you are finished with this manual effort. Capture will just suffer continual contention while you are pruning these large numbers of rows, and keeping capture and apply down will keep all of the pruncntl values static while you perform this task.
- Determine the min synchpoint for a CD table based on the pruncntl table.

SELECT HEX(MIN(A.SYNCHPOINT)) FROM ASN.IBMSNAP_PRUNCNTL A,
ASN.IBMSNAP_REGISTER B
WHERE (A.SOURCE_TABLE = B.SOURCE _TABLE
AND A.SOURCE_OWNER = B.SOURCE_OWNER
AND A.SOURCE_VIEW_QUAL = B.SOURCE_VIEW_QUAL
AND B.PHYS_CHANGE_OWNER = <YOUR CD OWNER>
AND B.PHYS_CHANGE_TABLE = <YOUR CD TABLE>)

-This is the highest value that can be safely pruned from this cd table.
Eventually all the rows in the CD table with a commitseq less than or
equal to that value can be pruned, but the commitseq value actually comes from a join with the UOW table. Now find the minimum commitseq value in the uow table. Choose interim values of commitseq in between the two values just selected and use as prunpoint in the following SQL:

DELETE FROM <CD TABLE> A WHERE A.IBMSNAP_UOWID IN (SELECT DISTINCT
B.IBMSNAP_UOWID FROM ASN.IBMSNAP_UOW B WHERE B.IBMSNAP_COMMITSEQ <=
prunpoint)

- Keep on issuing these deletes, with commits, until you have pruned all of the values through to the highest value determined at the beginning.
- The uow table can be pruned safely only after all of the cd tables have been pruned. The safe value for the uow table pruning is the
min(synchpoint) from the pruncntl table (select * where not null or
zeroes).

DELETE FROM ASN.IBMSNAP_UOW WHERE IBMSNAP_COMMITSEQ <= min synchpoint