Replication of DB2 partitioned tables: Version 9.7 Fix Pack 1 or earlier (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 1 or earlier. 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.

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:

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:

1. Quiesce any applications that are updating the source table.
2. Stop the Q subscription and wait until it becomes inactive.
3. Add the partition.
4. 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.
5. Start the Q subscription.
6. 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.

Note: If the Q Capture program is stopped when a data partition is attached, rows that are inserted into, updated, or deleted from the table before it is attached as a partition are replicated. If Q Capture is running when the data partition is attached, these rows are not replicated.

To ensure consistent behavior, before you attach a table as a new data partition, set the DATA CAPTURE CHANGES clause for the table to OFF if you need to make any changes to the table. For example, the following statements create a table, insert values into the table, and then attach the table as a data partition to an existing partitioned table:

 db2 create table temp1 like t1;
 --  NOTE: data capture changes is off by default
 db2 insert into temp1 values (44,44);
 -- NOTE: Turn on data capture changes after insert/update/deletes
 -- and before attach partition
 db2 alter table temp1 data capture changes;
 db2 alter table t1 attach partition part4 starting from 41
 ending at 50 from temp1;
 db2 set integrity for t1 allow write access immediate checked;

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.