Reduction in need for explicit REORG
DB2® 10 provides several performance enhancements that reduce the need to reorganize indexes frequently, resulting in a reduction in CPU time and synchronous I/O waits.
Compared to DB2 9, in DB2 10 you might see increased activity in deferred writes because more buffers are in use by buffer pools to improve performance. For example, activity might increase for index root pages that are pinned in a buffer pool when the index page set or index partition is opened. Activity also might increase for space map pages to avoid an exhaustive scan of the buffer pool when going in and out of group buffer pool dependency (GBP-dependency). In these cases, you might want to increase the corresponding buffer pool size or increase the deferred write thresholds.
List prefetch of index leaf pages
Previously when an index was disorganized, performance could be affected, because more gaps existed between index leaf pages. However, this release of DB2 for z/OS® provides the functionality to perform a list prefetch of index leaf pages based on non-leaf page information. This enhancement can greatly reduce synchronous I/O waits for long-running queries that are accessing disorganized indexes. This enhancement also helps utilities that need to access index leaf pages to extract the index key values by reducing the number of synchronous I/O waits and elapsed time. These utilities include REORG INDEX, CHECK INDEX and RUNSTATS.
Sequential detection and index lookaside for referential constraints
When you insert data into a dependent table, DB2 accesses the parent key to check for referential constraints. New functionality enables sequential detection and index lookaside for INSERT statements that are subject to referential constraints. This enhancement reduces the CPU time of insert workloads that involve referential constraints.
IFCID support for index page split
DB2 10 introduces support for IFCID 359. IFCID 359 records an index page split. This IFCID makes monitoring index page splits easier, which can impact application performance. This IFCID stores the following information:
- Database ID
- Page set ID
- Partition number of the splitting index
- Splitting page number
- Start timestamp of the split
- End timestamp of the split