June 14, 2010

More DB2 10 Index Performance Improvements

While DB2 10 is loaded with performance improvements across the board, for this week I'll focus on the index processing improvements. In general, DB2 10 brings better space management, lock avoidance and improved parallel processing. Some highlights follow. To give you a sense of the progress, I'll start by listing the very good performance features IBM brought to DB2 9:

•    Large index pages
•    Asymmetric index split
•    Data sharing, log latch contention and LRSN spin loop reduction
•    More index look aside
•    APPEND option support
•    RTS LASTUSED support
•    Remove log force write at new page (segmented and UTS) via PK83735
•    RANDOM insert (something I covered here)

DB2 10 Compatibility Mode (CM)
•    Space search improvement
•    Index I/O parallelism
•    Log latch contention reduction and faster commit process
•    Additional index look aside

DB2 10 New Function Mode (NFM)
•    INCLUDE index
•    Member cluster supported in universal table spaces (UTS)
•    Complete LRSN spin avoidance

General Insert Enhancements
•    Log latch reduction in both data and non-data sharing
•    Complete LRSN spin avoidance (NFM)
•    New IFCID 359 to record index split
•    Eliminate mass delete locks from UTS
•    Referential integrity check performance
•    Sequential detection and index look aside for referential integrity (RI)
•    Avoid RI check for each insert of a child under the same parent
•    Performance improvement for sequential inserts into the middle of a cluster index
•    Significant space search improvement in sequential insert
•    Member cluster option now available with UTS (PBG/PBR)