Create a sequence

Use the CREATE SEQUENCE statement to create a sequence, which is an object from which users can generate unique numbers.

To create a sequence, use the CREATE SEQUENCE statement and specify the options in any order.
CREATE SEQUENCE <sequence name> [as <data type> <options>]
Where the options are the following:
START WITH <start value>
INCREMENT BY <increment>
no minvalue | minvalue <minimum value>
no maxvalue | maxvalue <maximum value>
cycle | no cycle
The options have the following parameters:
  • The default minvalue is no minvalue, which is defined to be 1.
  • The default maxvalue is no maxvalue and is the largest value by data type that the sequence can hold.
  • The default start value is the minvalue for an increasing sequence, and the maxvalue for a decreasing sequence. The startvalue must be within the range of the minvalue and maxvalue.
  • The default increment is 1.
  • By default, sequences do not cycle.

When a user generates a sequence number, the system increments the sequence independently of the transaction committing or rolling back. Therefore, a rollback does not return the value to the sequence object. If two users concurrently increment the same sequence, the sequence numbers each user acquires might have gaps because the sequence numbers are being generated by the other user.

Sequences also can have gaps because sequence values on the host and SPUs are identified for efficient operation.