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 checkes, 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_seq
This
sequence starts at 1 and increases by 1 with no upper limit.
This 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 cheque 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
For more information about these and other options,
and authorization requirements, see the CREATE SEQUENCE statement.
DB2 Version 9.7 for Linux, UNIX, and Windows