Sequential Insert Hotspot Relief

A common problem database administrators (DBAs) try to avoid when designing an application is a primary key or index that is for ever increasing like a sequence number or timestamp. DBAs go out of their way to avoid keys like this because of locking contention issues. During the insert process, DB2 acquires a lock on the index page. If multiple transactions with a sequential key are inserting at the same time, you'll more than likely experience a slowdown--and in the worst case, you'll get deadlock or timeout failures. This seems to be even more of a problem when online transactions are processing in a data sharing environment.

DB2 9 for z/OS offers relief in this area by allowing the insert of the sequential column through a new RANDOM option on both the CREATE INDEX and ALTER INDEX statements. When RANDOM is used on the column in an index, DB2 stores the key values in random locations within the index tree. This reduces the chance of ever-increasing sequential inserts being on the same page, which decreases the chance of having lock contention.

Randomly ordered indexes can be used with equality lookups on a specific column. In addition, randomly ordered key columns can be used in non-matching index scans, while index-only access can be used in random key columns.

Here's an example of a CREATE INDEX using the RANDOM clause:

CREATE INDEX creator.table_name_idx1
       ON creator.table_name        
       (COL1                       ASC    
       ,COL2                       RANDOM)   

RANDOM cannot be specified on a key column that is varying in length with the NOT PADDED option. If you've taken advantage of RANDOM key columns in your application, please take a moment and tell us of your experience in Comments.

Comments

[Troy Coleman]

My friend Dan Luksetich pointed out to me that he has had some high transaction inserts 13,000 per second in a two way data sharing without a problem. I also noticed Willie's blog on this topic: http://it.toolbox.com/blogs/db2zos/db2-9-random-index-sounds-very-32659

So unless you are experiencing real pain with insert contention you may want to take more time testing this option out before you jump in and use it.

If you have used this feature to solve a real pain please post a note and let us know what you experienced.