| 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: No waits for incrementing values Standalone sequential-number-generating object not tied to a table Ascending or descending number generation Useful for application porting from the other database management systems Helpful in generating keys that can be used to coordinate keys across multiple tables (RI or application related)
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: SELECT and SELECT INTO An INSERT statement within the SELECT clause of fullselect An UPDATE statement within the SET clause (searched or positioned) SET host variable VALUES or VALUES INTO CREATE PROCEDURE, FUNCTION, TRIGGER
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. Table 12-2. Sequence Objects versus Identity Columns[*]Sequence Object | Identity Columns with Version 8 Features |
|---|
Standalone sequence objects created at user request | Internal sequence objects generated/maintained and populated by DB2 | Can supply values for one or more table columns | Can have only one per table | Used for whatever purpose users choose | Associated with a particular table | CYCLEing will wrap around and repeat with no uniqueness consideration | CYCLEing may have a problem if a unique index is on the identity column and duplicates are created | When used to populate a table, can later be updated | Cannot be updated if GENERATED ALWAYS | Supports NEXT VALUE FOR expression and PREVIOUS VALUE FOR expression | Must use ID_VAL_LOCAL and returns only most recent values in that user's commit scope | ALTER, DROP, COMMENT, GRANT/REVOKE | ALTER TABLE only; if adding an identity column to a populated table, it will be put in REORG-pending status; cannot remove from a table |
[*] If future designs would benefit more from sequence objects than from identity columns, careful consideration should be made when choosing to use identity columns. If they are defined on populated tables and you want to remove them, the table must be dropped and recreated. This could be a big problem for large tables in a high-availability environment.
| 