DB2 Version 9.7 for Linux, UNIX, and Windows

Asynchronous index cleanup for MDC tables

You can enhance the performance of a rollout deletion-an efficient method for deleting qualifying blocks of data from multidimensional clustering (MDC) tables-by using asynchronous index cleanup (AIC). AIC is the deferred cleanup of indexes following operations that invalidate index entries.

Indexes are cleaned up synchronously during a standard rollout deletion. When a table contains many record ID (RID) indexes, a significant amount of time is spent removing the index keys that reference the table rows that are being deleted. You can speed up the rollout by specifying that these indexes are to be cleaned up after the deletion operation commits.

To take advantage of AIC for MDC tables, you must explicitly enable the deferred index cleanup rollout mechanism. There are two methods of specifying a deferred rollout: setting the DB2_MDC_ROLLOUT registry variable to DEFER or issuing the SET CURRENT MDC ROLLOUT MODE statement. During a deferred index cleanup rollout operation, blocks are marked as rolled out without an update to the RID indexes until after the transaction commits. Block identifier (BID) indexes are cleaned up during the delete operation because they do not require row-level processing.

AIC rollout is invoked when a rollout deletion commits or, if the database was shut down, when the table is first accessed following database restart. While AIC is in progress, queries against the indexes are successful, including those that access the index that is being cleaned up.

There is one coordinating cleaner per MDC table. Index cleanup for multiple rollouts is consolidated within the cleaner, which spawns a cleanup agent for each RID index. Cleanup agents update the RID indexes in parallel. Cleaners are also integrated with the utility throttling facility. By default, each cleaner has a utility impact priority of 50 (acceptable values are between 1 and 100, with 0 indicating no throttling). You can change this priority by using the SET UTIL_IMPACT_PRIORITY command or the db2UtilityControl API.

Note: In DB2® Version 9.7 and later releases, deferred cleanup rollout is not supported on a data partitioned MDC table with partitioned RID indexes. Only the NONE and IMMEDIATE modes are supported. The cleanup rollout type will be IMMEDIATE if the DB2_MDC_ROLLOUT registry variable is set to DEFER, or if the CURRENT MDC ROLLOUT MODE special register is set to DEFERRED to override the DB2_MDC_ROLLOUT setting.

If only nonpartitioned RID indexes exist on the MDC table, deferred index cleanup rollout is supported. The MDC block indexes can be partitioned or nonpartitioned.

Monitoring the progress of deferred index cleanup rollout operation

Because the rolled-out blocks on an MDC table are not reusable until after the cleanup is complete, it is useful to monitor the progress of a deferred index cleanup rollout operation. Use the LIST UTILITIES command to display a utility monitor entry for each index being cleaned up. You can also retrieve the total number of MDC table blocks in the database that are pending asynchronous cleanup following a rollout deletion (BLOCKS_PENDING_CLEANUP) by using the SYSPROC.ADMIN_GET_TAB_INFO_V95 table function or the GET SNAPSHOT command.

In the following sample output for the LIST UTILITIES SHOW DETAIL command, progress is indicated by the number of pages in each index that have been cleaned up. Each phase represents one RID index.

ID                               = 2
Type                             = MDC ROLLOUT INDEX CLEANUP
Database Name                    = WSDB
Partition Number                 = 0
Description                      = TABLE.<schema_name>.<table_name>
Start Time                       = 06/12/2006 08:56:33.390158
State                            = Executing
Invocation Type                  = Automatic
Throttling:
   Priority                      = 50
Progress Monitoring:
   Estimated Percentage Complete = 83
   Phase Number                  = 1
      Description                = <schema_name>.<index_name>
      Total Work                 = 13 pages
      Completed Work             = 13 pages
      Start Time                 = 06/12/2006 08:56:33.391566
   Phase Number                  = 2
      Description                = <schema_name>.<index_name>
      Total Work                 = 13 pages
      Completed Work             = 13 pages
      Start Time                 = 06/12/2006 08:56:33.391577
   Phase Number                  = 3
      Description                = <schema_name>.<index_name>
      Total Work                 = 9 pages
      Completed Work             = 3 pages
      Start Time                 = 06/12/2006 08:56:33.391587