DB2 10.5 for Linux, UNIX, and Windows

Asynchronous index cleanup

Asynchronous index cleanup (AIC) is the deferred cleanup of indexes following operations that invalidate index entries. Depending on the type of index, the entries can be record identifiers (RIDs) or block identifiers (BIDs). Invalid index entries are removed by index cleaners, which operate asynchronously in the background.

AIC accelerates the process of detaching a data partition from a partitioned table, and is initiated if the partitioned table contains one or more nonpartitioned indexes. In this case, AIC removes all nonpartitioned index entries that refer to the detached data partition, and any pseudo-deleted entries. After all of the indexes have been cleaned, the identifier that is associated with the detached data partition is removed from the system catalog. In DB2® Version 9.7 Fix Pack 1 and later releases, AIC is initiated by an asynchronous partition detach task.

Prior to DB2 Version 9.7 Fix Pack 1, if the partitioned table has dependent materialized query tables (MQTs), AIC is not initiated until after a SET INTEGRITY statement is executed.

Normal table access is maintained while AIC is in progress. Queries accessing the indexes ignore any invalid entries that have not yet been cleaned.

In most cases, one cleaner is started for each nonpartitioned index that is associated with the partitioned table. An internal task distribution daemon is responsible for distributing the AIC tasks to the appropriate table partitions and assigning database agents. The distribution daemon and cleaner agents are internal system applications that appear in LIST APPLICATIONS command output with the application names db2taskd and db2aic, respectively. To prevent accidental disruption, system applications cannot be forced. The distribution daemon remains online as long as the database is active. The cleaners remain active until cleaning has been completed. If the database is deactivated while cleaning is in progress, AIC resumes when you reactivate the database.

AIC impact on performance

AIC incurs minimal performance impact.

An instantaneous row lock test is required to determine whether a pseudo-deleted entry has been committed. However, because the lock is never acquired, concurrency is unaffected.

Each cleaner acquires a minimal table space lock (IX) and a table lock (IS). These locks are released if a cleaner determines that other applications are waiting for locks. If this occurs, the cleaner suspends processing for 5 minutes.

Cleaners are integrated with the utility throttling facility. By default, each cleaner has a utility impact priority of 50. You can change the priority by using the SET UTIL_IMPACT_PRIORITY command or the db2UtilityControl API.

Monitoring AIC

You can monitor AIC with the LIST UTILITIES command. Each index cleaner appears as a separate utility in the output. The following is an example of output from the LIST UTILITIES SHOW DETAIL command:

ID                               = 2
Type                             = ASYNCHRONOUS INDEX CLEANUP
Database Name                    = WSDB
Partition Number                 = 0
Description                      = Table: USER1.SALES, Index: USER1.I2
Start Time                       = 12/15/2005 11:15:01.967939
State                            = Executing
Invocation Type                  = Automatic
Throttling:
   Priority                      = 50
Progress Monitoring:
      Total Work                 = 5 pages
      Completed Work             = 0 pages
      Start Time                 = 12/15/2005 11:15:01.979033

ID                               = 1
Type                             = ASYNCHRONOUS INDEX CLEANUP
Database Name                    = WSDB
Partition Number                 = 0
Description                      = Table: USER1.SALES, Index: USER1.I1
Start Time                       = 12/15/2005 11:15:01.978554
State                            = Executing
Invocation Type                  = Automatic
Throttling:
   Priority                      = 50
Progress Monitoring:
      Total Work                 = 5 pages
      Completed Work             = 0 pages
      Start Time                 = 12/15/2005 11:15:01.980524

In this case, there are two cleaners operating on the USERS1.SALES table. One cleaner is processing index I1, and the other is processing index I2. The progress monitoring section shows the estimated total number of index pages that need cleaning and the current number of clean index pages.

The State field indicates the current state of a cleaner. The normal state is Executing, but the cleaner might be in Waiting state if it is waiting to be assigned to an available database agent or if the cleaner is temporarily suspended because of lock contention.

Note that different tasks on different database partitions can have the same utility ID, because each database partition assigns IDs to tasks that are running on that database partition only.