DB2 Version 9.7 for Linux, UNIX, and Windows

FP1: Partitioned table data remains available during roll-out operations

In DB2® Version 9.7 Fix Pack 1 and later fix packs, when detaching a data partition of a partitioned table, queries can continue to access the unaffected data partitions of the table during a roll-out operation initiated by the ALTER TABLE...DETACH PARTITION statement.

When detaching a data partition from a partitioned table using the ALTER TABLE statement with the DETACH PARTITION clause, the source partitioned table remains online, and queries running against the table continue to run. The data partition being detached is converted into a stand-alone table in the following two-phase process:

The ALTER TABLE...DETACH PARTITION operation logically detaches the data partition from the partitioned table.
An asynchronous partition detach task converts the logically detached partition into a stand-alone table.

If there are any dependent tables that need to be incrementally maintained with respect to the detached data partition (these dependent tables are referred to as detached dependent tables), the asynchronous partition detach task starts only after the SET INTEGRITY statement is run on all detached dependent tables.

In absence of detached dependents, the asynchronous partition detach task starts after the transaction issuing the ALTER TABLE...DETACH PARTITION statement commits.

The ALTER TABLE...DETACH PARTITION operation performs in the following manner:

The DETACH operation does not wait for dynamic uncommitted read (UR) isolation level queries before it proceeds, nor does it interrupt any currently running dynamic UR queries. This behavior occurs even when the UR query is accessing the partition being detached.
If dynamic non-UR queries (read or write queries) have not locked the partition to be detached, the DETACH operation can complete while dynamic non-UR queries are running against the table.
If dynamic non-UR queries have locked the partition to be detached, the DETACH operation waits for the lock to be released.
Hard invalidation must occur on all static packages that are dependent on the table before the DETACH operation can proceed.
The following restrictions that apply to data definition language (DDL) statements also apply to a DETACH operation because DETACH requires catalogs to be updated:
- New queries cannot be compiled against the table.
- A bind or rebind cannot be performed on queries that run against the table.
To minimize the impact of these restrictions, issue a COMMIT immediately after a DETACH operation.