Sequences

A sequence is a database object that allows the automatic generation of values, such as check 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 maximum number of sequence values that can be lost is calculated as follows:
  • If ORDER is specified, the maximum is the value specified for the CACHE option.
  • In a multi-partition or Db2® pureScale® environment, the maximum is the value specified for the CACHE option times the number of members that generate new identity values.

For complete details and examples of these expressions, see Sequence reference.