Automatic cleanup of pseudo-empty index pages
Db2 11 automatically cleans up pseudo-deleted index entries and pseudo-empty index pages. This enhancement can reduce the size of some indexes, which can improve SQL performance and reduce the need to run the REORG INDEX utility.
When rows are deleted, index entries are not physically deleted unless the delete operation has exclusive control over the index page set. These index entries are called pseudo-deleted index entries. Subsequent searches continue to access these pseudo-deleted entries, which can gradually degrade performance as more rows are deleted. These pseudo-deleted index entries can also result in timeouts and deadlocks for applications that insert data into tables with unique indexes.
Before Db2 11, Db2 attempted to clean up pseudo-empty index pages as part of SQL DELETE processing. Pseudo-empty index pages are pages that contain only pseudo-deleted index entries. However, if some of the pseudo-deleted entries in the page were not committed during the SQL DELETE processing, cleanup could not be performed. Therefore, some pseudo-empty pages were likely not cleaned up.
In addition to the cleanup that was previously done, Db2 11 also automatically deletes pseudo-empty index pages independently of the SQL DELETE transaction. Db2 also automatically deletes individual pseudo-deleted index entries.
Automated cleanup of pseudo-empty index pages and pseudo-deleted index entries is enabled for all indexes by default when the value of the INDEX_CLEANUP_THREADS subsystem parameter is set to a non-zero value. However, you can specify time windows to enable or disable the index cleanup for the entire subsystem, for indexes in specific databases, or for specific indexes.
When the system is zIIP-enabled, this automated clean-up function runs under enclave service request blocks (SRBs) that are zIIP-enabled. However, the system does not need to be zIIP-enabled to use this new function.
This new function is designed to run with minimal or no disruption to other concurrent Db2 work in the system.