Need a Db2 Identity or Sequence?

Previously, I wrote about speeding up the performance of values generated by an identity column using the cache setting. One reader suggested that I follow up that entry with a comparison with the other key generator provided by Db2, a sequence object. So here we go…

The biggest differentiator between these two key generators is that a sequence is not associated with any table. A sequence is a standalone object whose generated values can be used by any table.  This also means that the next key value needs to be explicitly generated – in comparison, an identity column results in Db2 automatically generating the next key value whenever a new row gets inserted. The following code snippet shows this explicit key generation associated with a sequence in action.

CREATE SEQUENCE orderSeq START WITH 1 INCREMENT BY 1;

VALUES (NEXT VALUE FOR orderSeq) INTO :hvOrdID;

INSERT INTO ordMaster (ordId, ordDate, OrdCust, ordAmount)
   VALUES (:hvOrdID, CURRENT DATE, 'C1013', 1013.97);

INSERT INTO ordDetail (ordNum, ItemNum, ItemQty)
   VALUES (:hvOrdID, 'T0612', 67);

INSERT INTO ordDetail (ordNum, ItemNum, ItemQty)
   VALUES (:hvOrdID, 'J1103', 95);

 

The NEXT VALUE expression is used to explicitly generate a key value from the orderSeq sequence object and is stored in a local host variable. This expression could be placed on the VALUES clause of an INSERT statement and eliminate the host variable assignment. However, the code in this example demonstrates one of the advantages of a sequence object – the generated value can be easily shared across multiple tables. In this case, the generated sequence value is used for the ordID colum in the ordMaster table and the ordNum foreign key column in the ordDetail table which is used to link the order details to the associated order in the ordMaster table.

 

This ability to share the generated key value across multiple tables was key for a client that engaged our IBM Expert Labs team to modernize their database and application with SQL. Their initial database design used an identity column to generate the key values for their account number column. A review of their application design, however, showed that their account creation process required the new account number to be known before inserting an account into their SQL table. Thus, they switched from a generated identity column to a sequence object.

 

A sequence also provides the flexibility of being able to generate an alphanumeric key value. The requirement to explicitly request a key provides an opportunity to convert the generated numeric key value into a character string as shown in the following example.

 

SET :hv1 = 'N' CONCAT CAST( NEXT VALUE FOR someSeq AS CHAR(4) );

 On the performance front, my tests showed that generated identity columns have a performance advantage over sequence objects. With the default cache settings, identity columns generated values about 25% faster. When the cache setting for both solutions was increased to 200, the sequence object was only 15% slower. Given that a single key value generated by a sequence object can be used on multiple insert operations, it’s difficult to put too much weight on the differences in performance.

 

Hopefully, this comparison has given you some things to consider when choosing between these two key generators provided by Db2.  One option is not better than the other, your requirements will dictate which option you should choose.