DB2 Version 9.7 for Linux, UNIX, and Windows

FP1: Detach operation for data partitions has been changed

In Version 9.7 Fix Pack 1 and later fix packs, the process to detach a data partition from a partitioned table is a two-phase process.

Details

When you issue the ALTER TABLE statement with the DETACH partition clause, the data partition that you are detaching is converted into a stand-alone table in the following two-phase process:

The ALTER TABLE operation logically detaches the data partition from the partitioned table. The data partition name is changed to a system-generated name of the form SQLyymmddhhmmssxxx so that a subsequent attach can reuse the detached partition name immediately. In SYSCAT.DATAPARTITIONS, the status of the partition is set to L (logically detached) if there are no detached dependent tables or D if there are detached dependent tables.
An asynchronous partition detach task converts the logically detached partition into a stand-alone table.

The target table is unavailable until the asynchronous partition detach task completes the detach. For example, a DROP statement that drops the target table after a detach must wait until the asynchronous partition detach task completes the detach. In Version 9.7 and earlier releases, the target table of an ALTER TABLE statement with the DETACH PARTITION clause became available immediately after the transaction issuing the ALTER TABLE statement committed if there were no detached dependent tables that needed to be incrementally maintained with respect to the detached data partition. If there were detached dependent tables, the target table became available after the SET INTEGRITY statement is run on all detached dependent tables.

User response

Because the data partition name is changed to a system-generated name during the first phase of the detach process, you might need to modify applications that query the catalog views for detached data partitions and use the data partition names.