Replication of DB2 partitioned tables: Version 9.7 Fix Pack 2 and later (Linux, UNIX, Windows)

Q Replication supports DB2® tables that are partitioned by range (using the PARTITION BY clause of the CREATE TABLE statement). These tables are sometimes known as range-partitioned tables.

Version and fix pack requirements exist for the Q Capture program if a source table is partitioned by range. This topic covers these requirements and support for replication of partitioned tables when your replication programs are at Version 9.7 Fix Pack 2 or later. If only target tables (no source tables) are partitioned by range, then Q Replication has no version or fix pack requirements specific to these tables.

To capture changed data for range-partitioned tables, your Q Capture program must be at Version 9.7 or later. It can capture changes from range-partitioned tables on DB2 V9.1, V9.5, or V9.7. However, restrictions exist for range-partitioned tables earlier than V9.7. The restrictions are also discussed in this topic.

Important: If your replication programs are at Version 9.7 Fix Pack 2 or later and you plan to replicate range-partitioned tables, you must run the Version 9.7 Fix Pack 2 migration script, asnqcapluwv97fp2.sql. The script adds a new control table, IBMQREP_PART_HIST, to help the replication programs handle data partition changes such as add, attach, or detach. The script is located in the sqllib/samples/repl/mig97/q/ directory. The Q Capture program does not use the IBMQREP_PART_HIST table for partitioned source tables on DB2 Version 9.5 or Version 9.1.

Partitioned tables use a data organization scheme in which table data is divided across multiple storage objects, called data partitions or ranges, according to values in one or more table partitioning key columns of the table.

Replication and publishing treat all data partitions of a source table as a single table. For example, when you create a Q subscription or publication that specifies a partitioned table, you specify the entire table rather than one or more data partitions of the table. All row operations for the table, regardless of the data partition at which they occur, are replicated or published.

You can perform several alterations on a partitioned table, including adding a data partition, attaching a data partition, or detaching a data partition. These ALTER operations on the source table are not replicated to the target. You must alter the target table independently of the source table if you want to maintain an identical partitioning scheme.

Replication and publishing treat these ALTER operations differently:

Note for Version 9.5 and earlier: The Q Capture program does not recognize the addition, attachment, or detachment of a data partition until the program is reinitialized or stopped and restarted.

ADD

Adds a new, empty data partition to the source table. If you require the new data partition at the target, you must manually add it. Q Capture program behavior and the procedure that you need to follow depend on the release of your DB2:

Version 9.7 or higher

Add the data partition at the target before adding it at the source. Q Capture automatically begins replicating changes to the data partition.

Version 9.5 or 9.1

Follow these steps:

Quiesce any applications that are updating the source table.
Stop the Q subscription and wait until it becomes inactive.
Add the partition.
Make sure that the Q subscription does not specify a load of the target table. If necessary, use the ASNCLP program or replication center to change the load option.
Start the Q subscription.
Resume applications.

ATTACH

Creates a new data partition at the source by using an existing table. The ATTACH operation is not replicated and the data in the new data partition is not replicated to the target. If you require the new data partition at the target you must manually add it. If you require the attached data at the target, you must manually load the data into the target before you attach the data partition at the target.

DETACH

Turns an existing data partition into a separate table. The DETACH operation is not replicated. The data that is deleted from the source table by the DETACH operation is not deleted from the target table. If you need to change the target data partition into a separate table, you need to do so manually.

Note: DB2 logs updates that cause rows to move across data partitions as delete-insert pairs. The Q subscription or publication option to suppress delete operations from the source table (SUPPRESS_DELETES=Y) can cause a single UPDATE operation at the source to appear as two rows at the target. It is recommended that you avoid using the suppress delete option with partitioned source tables.