A sequence object is a user-defined object that generates a sequence of numeric values according to the specifications in which it was created. It provides an incremental counter generated by DB2 and is similar to an identity column. An identity column can be thought of as a special kind of sequence object; however, a sequence column is separate from the table.
Sequence object values can be used in applications for a variety of reasons and have several benefits:
The sequence name is made up of two parts: the 128-byte schema name and the 128-byte identifier. A sequence object is created by using a new CREATE SEQUENCE statement; all attributes are completely user defined or defaults. The values in the sequence object can be of any exact numeric data type. The starting value is defined with a START WITH value and advances with INCREMENT BY (ascending or descending). The values can be cached and are generated in the order of request. Figure 12-1 shows the creation and simple use of a sequence object.
Figure 12-1. Sequence object creation and use
The sequence object, relatively similar to the workaround many people were using for identity columns, is much more efficient and has some nice usage benefits, such as the ability to do NEXT VALUE FOR and PREVIOUS VALUE FOR. NEXTVAL FOR generates and returns the next value for the sequence object; PREVVAL FOR generates and returns the previous value for the sequence object. These statements can be used with the following:
Following are examples of usage for these statements (assume that ACCT_SEQ is START WITH 10 INCREMENT BY 10):
SELECT NEXTVAL FOR ACCT_SEQ Returns 10 SELECT NEXTVAL FOR ACCT_SEQ Returns 20 COMMIT SELECT PREVVAL FOR ACCT_SEQ Returns 20 *** UPDATE ACCOUNTS SET ACCT_NO = NEXTVAL FOR ACCT_SEQ Returns 30 *** INSERT INTO ACCOUNTS (ACCT_NO) VALUES( SELECT NEXTVAL FROM ACCT_SEQ) Returns 40
As you can see, using sequence objects instead of identity columns has many benefits. Table 12-2 compares the two.