You can reduce the quantity of SQL issued, as well as a major source of locking contention in your application, by using DB2 system-generated key values.
Traditionally, people have used a next-key table to generate keys. This method is where a table contains one row of one column with a numeric data type. This method typically involves reading the column, incrementing and updating the column, and then using the new value as a key to another table. These next-key tables often result in a significant bottleneck.
Several ways exist to generate key values inside DB2, two of which are identity columns (DB2 V8, DB2 9) and sequence objects (DB2 V8, DB2 9). Identity columns are attached to tables, and sequence objects are independent of tables.
The high performance solution for key generation in DB2 is the sequence object. Confirm that when you use sequence objects or identity columns you use the CACHE and ORDER settings according to your high performance needs. These settings affect the number of values that are cached in advance of a request and whether the order the values are returned is important. For example, the settings for a high level of performance in a data sharing group would be CACHE 50 NO ORDER.
When using sequence objects and identity columns, you can reduce the number of SQL statements your application issues by using a SELECT from a result table (DB2 V8 and DB2 9). In this case, the result table will be the result of an INSERT.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|