DB2 Version 9.7 for Linux, UNIX, and Windows

Data partition detach phases

With DB2® Version 9.7 Fix Pack 1 and later releases, detaching a data partition from a data partitioned table consists of two phases. The first phase logically detaches the partition from the table, the second phase converts the data partition into a stand-alone table.

The detach process is initiated when an ALTER TABLE...DETACH PARTITION statement is issued:

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 the 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 dependent tables, the asynchronous partition detach task starts after the transaction issuing the ALTER TABLE...DETACH PARTITION statement commits.

DETACH operation

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.

During the DETACH operation, the data partition name is changed to a system-generated name of the form SQLyymmddhhmmssxxx , and in SYSCAT.DATAPARTITIONS, the status of the partition is set to 'L' if there are no detached dependent tables, or 'D' if there are detached dependent tables.

During the DETACH operation, an entry is created in SYSCAT.TABLES for the target table. If there are detached dependent tables, the table TYPE is set to 'L'. After SET INTEGRITY is run on all detached dependent tables, the TYPE is set to 'T', however, the target table continues to be unavailable. The asynchronous partition detach task completes the detach and makes the target table available.

Soft invalidation of dynamic SQL during the DETACH operation allows dynamic SQL queries that started prior to the ALTER TABLE...DETACH PARTITION statement to continue running concurrently with the DETACH operation. The ALTER TABLE...DETACH PARTITION statement acquires a IX lock on the partitioned table and an X lock on the data partition being detached.

Asynchronous partition detach task

After the DETACH operation commits and any detached dependent tables have been refreshed, the asynchronous partition detach task converts the logically detached partition into the stand-alone table.

The asynchronous partition detach task waits for the completion of all access on the partitioned table that started prior to phase 1 of the detach operation. If the partitioned table has nonpartitioned indexes, the asynchronous partition detach task creates the asynchronous index cleanup task for deferred indexed cleanup. After the access completes, the asynchronous partition detach task completes phase 2 of the detached operation, by converting the logically detached partition into a stand-alone table.

The LIST UTILITIES command can be used to monitor the process of the asynchronous partition detach task. The LIST UTILITIES command indicates whether the asynchronous partition detach task is in one of the following states:

Waiting for old access to the partitioned table to complete
Finalizing the detach operation and making the target table available