A sequence is a database object that allows the automatic
generation of values, such as cheque numbers. Sequences are ideally suited
to the task of generating unique key values. Applications can use sequences
to avoid possible concurrency and performance problems resulting from column
values used to track numbers. The advantage that sequences have over numbers
created outside the database is that the database server keeps track of the
numbers generated. A crash and restart will not cause duplicate numbers from
being generated.
The sequence numbers generated have the following properties:
- Values can be any exact numeric data type with a scale of zero. Such data
types include: SMALLINT, BIGINT, INTEGER, and DECIMAL.
- Consecutive values can differ by any specified integer increment. The
default increment value is 1.
- Counter value is recoverable. The counter value is reconstructed from
logs when recovery is required.
- Values can be cached to improve performance. Pre-allocating and storing
values in the cache reduces synchronous I/O to the log when values are generated
for the sequence. In the event of a system failure, all cached values that
have not been used are considered lost. The value specified for CACHE is the
maximum number of sequence values that could be lost.
There are two expressions that can used with sequences:
- NEXT VALUE expression: returns the next value for the specified
sequence. A new sequence number is generated when a NEXT VALUE expression
specifies the name of the sequence. However, if there are multiple instances
of a NEXT VALUE expression specifying the same sequence name within a query,
the counter for the sequence is incremented only once for each row of the
result, and all instances of NEXT VALUE return the same value for each row
of the result.
- PREVIOUS VALUE expression: returns the most recently generated
value for the specified sequence for a previous statement within the current
application process. That is, for any given connection, the PREVIOUS VALUE
remains constant even if another connection invokes NEXT VALUE.
For complete details and examples of these expressions, see Sequence reference.
DB2 Version 9.7 for Linux, UNIX, and Windows