Section 7.16. Sequences

7.16. Sequences

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.

For the sequence value, you can use any exact numeric data type with a scale of zero, including SMALLINT, INTEGER, BIGINT, and DECIMAL. In addition, any user-defined distinct type based on these data types can hold sequence values.

NOTE

The options supported for sequence objects are the same as the ones for identity columns.

Table 7.4 shows other statements you can use with sequences.

Table 7.4. Other Statements Used with Sequences

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