In addition to the regular maintenance that you perform for your databases, running replication requires you to perform the following maintenance:
You should reorganize the CD tables and UOW table about once a week if they are heavily used. For DB2 for OS/390 Version 5 or higher, specify the PREFORMAT keyword. Preformatting the table space speeds up the Capture program's insert processing. If the table space is compressed, you must also specify the KEEPDICTIONARY keyword.
Because subscription predicates are very selective and can filter out a majority of the transaction updates, there is no general rule for how frequently you should reorganize target tables, but it should be at least as frequently as you reorganize the source tables.
At the end of each subscription cycle, the Apply program inserts a row into the Apply trail control table. To prevent this table from growing too large, you need to delete these rows regularly. You can delete these rows whenever you like because although the Apply program writes to this table, it does not read from it. The subscription statistics and error diagnostics written to this table are for your benefit, and they are used by the Replication Monitor. An easy way to manage the growth of this table is to add an SQL statement to the subscription set; for example:
DELETE FROM ASN.IBMSNAP_APPLYTRAIL WHERE LASTRUN < (CURRENT TIMESTAMP - 7 DAYS);
Neither the Capture program nor the Apply program automatically prunes CCD tables, and there is no command for pruning these tables. Condensed CCD tables are updated in place, so do not grow continually. Noncondensed CCD tables contain history, which you likely want to retain.
A condensed, noncomplete, internal CCD table grows, and with enough update activity, can approach the size of a complete CCD table. Because only the most recent changes are retrieved from it, there is no value in letting this table grow. To prune transactions that have already been replicated from this table, add an SQL statement to the internal CCD's subscription; for example:
DELETE FROM my.internal_ccd WHERE IBMSNAP_COMMITSEQ <= (SELECT MIN(SYNCHPOINT) FROM ASN.IBMSNAP_PRUNCNTL);
This statement prunes the table based on the least active subscriptions, not just those subscriptions that refer to the source associated with the internal CCD, so you might want to modify the statement for more aggressive pruning.
The following operational procedures typically require exclusive use of DB2 table spaces or the catalog:
REORG
BIND PACKAGE
BIND PLAN
GRANT
REVOKE
Because these operational procedures do not coexist well with the Capture and Apply programs' issuing dynamic SQL (which implicitly locks catalog tables) or accessing table spaces, you should stop both the Capture and Apply programs when running utilities (and other similar operational procedures) to avoid any possible contention.