Asynchronous partition detach for data partitioned tables

For Db2® Version 9.7 Fix Pack 1 and later releases, the asynchronous partition detach task completes the detach of a data partition from a partitioned table that was initiated by an ALTER TABLE...DETACH operation. The task is an asynchronous background process (ABP) that is initiated after the partition becomes a logically detached partition.

The asynchronous partition detach task accelerates the process of detaching a data partition from a partitioned table. If the partitioned table has dependent materialized query tables (MQTs), the task is not initiated until after a SET INTEGRITY statement is executed on the MQTs.

By completing the detach of the data partition asynchronously, queries accessing the partitioned table that started prior to issuing ALTER TABLE...DETACH PARTITION statement continue while the partition is immediately detached.

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

The asynchronous partition detach task performs the following operations:
  • Performs hard invalidation on cached statements on which the ALTER TABLE...DETACH operation previously performed soft invalidation.
  • Updates catalog entries for source partitioned table and target stand-alone table and makes the target table available.
  • For multidimensional clustering (MDC) tables with nonpartitioned block indexes and no other partitioned indexes, creates an index object for target table. The block indexes are created upon first access to the target table after the asynchronous partition detach task commits.
  • Creates the system path index on the target table for table containing XML columns.
  • Updates the minimum recovery time (MRT) of the table space containing the detached partition.
  • Creates asynchronous index cleanup AIC tasks for nonpartitioned indexes. The AIC task performs index cleanup after asynchronous partition detach completes.
  • Releases the data partition ID if nonpartitioned indexes do not exist on the table.

Asynchronous partition detach task impact on performance

An asynchronous partition detach task incurs minimal performance impact. The task waits for all access to the detached partition to complete by performing a hard invalidation on cached statements on which the ALTER TABLE...DETACH operation previously performed soft invalidation. Then the task acquires the required locks on the table and on the partition and continues the process to make the detached partition a stand-alone table.

Monitoring the asynchronous partition detach task

The distribution daemon and asynchronous partition detach task agents are internal system applications that appear in LIST APPLICATIONS command output with the application names db2taskd and db2apd, respectively. To prevent accidental disruption, system applications cannot be forced. The distribution daemon remains online as long as the database is active. The tasks remain active until detach completes. If the database is deactivated while detach is in progress, the asynchronous partition detach task resumes when the database is reactivated.

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
The following sample output for the LIST UTILITIES SHOW DETAIL command shows asynchronous partition detach task activity in the WSDB database:
ID                   = 1
Type                 = ASYNCHRONOUS PARTITION DETACH
Database Name        = WSDB
Partition Number     = 0
Description          = Finalize the detach for partition '4' of table 'USER1.ORDERS'.
Start Time           = 07/15/2009 14:52:14.476131
State                = Executing
Invocation Type      = Automatic
Progress Monitoring: 
    Description      = Waiting for old access to the partitioned table to complete.
    Start Time       = 07/15/2009 14:52:51.268119
In the output of the LIST UTILITIES command, the main description for the asynchronous partition detach task identifies the data partition being detached and the target table created by the detach operation. The progress monitoring description provides information about the current state of the asynchronous partition detach task.
Note: The asynchronous partition detach task is an asynchronous process. To know when the target table of a detach operation is available, a stored procedure can be created that queries the STATUS column of the SYSCAT.DATAPARTITIONS catalog view and returns when the detach operation completes. If non-partitioned indexes exist on the partitioned table, the target table of the detach operation becomes available when the STATUS column is changed to 'I', which indicates that asynchronous index cleanup is being performed on the non-partitioned index(es). Rows with a STATUS value of 'I' are removed when all index records referring to the detached partition have been deleted. If there are no non-partitioned indexes, then the asynchronous index cleanup phase is skipped and the row specified by the original DATAPARTITIONID is removed from SYSCAT.DATAPARTITIONS. This data partition name is changed to a system-generated name using the following form: 'SQLyymmddhhmmssxxx', which is done during the DETACH operation.

Asynchronous partition detach processing in a partitioned database environment

One asynchronous partition detach task is created for each DETACH operation independent of the number of database partitions in a partitioned database environment. The task is created on the catalog database partition and distributes work to the remaining database partitions, as needed.

Error handling for the asynchronous partition detach task

The asynchronous partition detach task is transaction based. All the changes made by a task will be rolled back internally if it fails. Any errors during asynchronous partition detach processing are logged in a db2diag log file. A failed task is retried later by the system.