DB2 Version 9.7 for Linux, UNIX, and Windows

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 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
      MAXVALUE 200
      CACHE 25
For more information about these and other options, and authorization requirements, see the CREATE SEQUENCE statement.