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.
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.
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.