Relief for sequential key insert

This release of DB2® for z/OS® includes support for asymmetric splitting of index pages and increased index page sizes (larger than 4 KB).

In prior releases, an index page splits so that approximately half of the index keys on the splitting page remain on one page, and the remaining index keys move to a new page. This 50:50 split can cause frequent page splits on an index that has sequential insert patterns. As a result, half of the splitting pages are empty. Non-partitioned indexes that are updated by LOAD utility jobs that run in parallel against multiple partitions are especially susceptible to these problems because sequential inserts into multiple ranges in the non-partitioned index can occur.

Allowing index pages to split asymmetrically can improve space utilization and reduce contention that results from frequent page splits in an index with sequential insert patterns in the middle of the index. An index page size that is greater than 4 KB can also relieve contention by accommodating more index keys per page, which reduces the frequency of page splits in indexes. You can use the INDEXBP option on both CREATE DATABASE and ALTER DATABASE statements to specify 4-KB, 8-KB, 16-KB, or 32-KB index buffer pools, and the BUFFERPOOL keyword on the CREATE INDEX statement to specify 8-KB, 16-KB, and 32-KB buffer pools.