November 17, 2009

Turn the Page on Sequential Insert

IBM continues to innovate to improve DB2 performance and availability. An example of this is the new RANDOM option, which I discussed last week. Found on the CREATE/ALTER INDEX statements, RANDOM stores an ever increasing sequential key index into random locations to help avoid the creation of a hotspot during insert processing on the last index page.

RANDOM is included with the current DB2 9, which brings several other new innovations in the area of index management. One such innovation is the new index asymmetric page splitting feature that will help you reduce wasted space as well as improve database performance.

When an index leaf page is full and you perform an insert, DB2 creates a new page and moves 50 percent of the index keys to this new page. This becomes a problem with ever increasing index key values, because 50 percent of the page left blank goes unused. Asymmetric page split processing is designed to address this issue. Now, when the page is full and DB2 detects sequential insert with ever increasing index keys, instead of conducting the 50:50 split, DB2 creates a new page and continues inserting new rows on this new page. This improves space utilization, reduces CPU consumption to rebalance the index keys and reduces contention while waiting on rebalancing during frequent page splits.

Make sure you are current on maintenance. The latest APAR PK91830 will fix problems related to asymmetric page split processing.