DB2 Sequences

A database object independent of any one table that automatically generates unique key values based on initial user specifications.

A sequence is a database object that allows automatic generation of values. Unlike identity columns, this object does not depend on any tablethe same sequence object can be used across the database.

To create a sequence, use the CREATE SEQUENCE statement as demonstrated here.

CREATE SEQUENCE myseq AS INTEGER
       START WITH 1 INCREMENT BY 1
       NO MAXVALUE
       NO CYCLE
       CACHE 5

This statement creates the sequence myseq, which is of type INTEGER. The sequence starts with a value of 1 and then increases by 1 each time it's invoked for the next value.

The NO MAXVALUE clause indicates there is no explicit maximum value in which the sequence will stop; therefore, it will be bound by the limit of the data type, in this case, INTEGER.

The NO CYCLE clause indicates the sequence will not start over from the beginning once the limit is reached.

CACHE 5 indicates five sequence numbers will be cached in memory, and the sixth number in the sequence would be stored in a catalog table. 

Sequence numbers are cached in memory for performance reasons; otherwise, DB2 needs to access the catalog tables constantly to retrieve the next value in line. What would happen if your computer crashed and the following numbers were in the cache: 5, 6, 7, 8, and 9? These numbers would be lost, and the next time DB2 needed to retrieve a number, it would obtain the number from the catalog tables. In this example, 10 is the next number to be generated. 

If you are using the sequence number to generate unique identifiers, which must be in sequence with no gaps allowed, this would not work for you. The solution would be to use the NO CACHE clause to guarantee sequentially generated numbers with no gaps, but you will pay a performance cost.

Statement	Explanation
ALTER SEQUENCE	Alters the characteristics of a sequence, like the increment value
DROP SEQUENCE	Drops the sequence
NEXTVAL FOR sequence_name or NEXT VALUE FOR sequence_name	Retrieves the next value generated in the sequence
PREVVAL FOR sequence_name or PREVIOUS VALUE FOR sequence_name	Retrieves the previous value generated in the sequence

Created with Artisteer