Creating sequences
To create sequences, use the CREATE SEQUENCE statement. Unlike an identity column attribute, a sequence is not tied to a particular table column nor is it bound to a unique table column and only accessible through that table column.
About this task
There are several restrictions on where NEXT VALUE or PREVIOUS VALUE expressions can be used. A
sequence can be created, or altered, so that it generates values in one of these ways:
- Increment or decrement monotonically (changing by a constant amount) without bound
- Increment or decrement monotonically to a user-defined limit and stop
- Increment or decrement monotonically to a user-defined limit and cycle back to the beginning and start again
Note: Use caution when recovering databases that use sequences: For sequence values that
are used outside the database, for example sequence numbers used for bank checks, if the database is
recovered to a point in time before the database failure, then this could cause the generation of
duplicate values for some sequences. To avoid possible duplicate values, databases that use sequence
values outside the database should not be recovered to a prior point in time.
To create a sequence called
order_seq using defaults for all the options, issue
the following statement in an application program or through the use of dynamic SQL
statements: CREATE SEQUENCE order_seqThis example could represent processing for a batch of bank checks starting from 101 to 200. The
first order would have been from 1 to 100. The sequence starts at 101 and increase by 1 with an
upper limit of 200. NOCYCLE is specified so that duplicate check numbers are not produced. The
number associated with the CACHE parameter specifies the maximum number of sequence values that the
database manager preallocates and keeps in
memory.
CREATE SEQUENCE order_seq
START WITH 101
INCREMENT BY 1
MAXVALUE 200
NOCYCLE
CACHE 25