Designing sequences

When designing sequences you must consider the differences between identity columns and sequences, and which is more appropriate for your environment. If you decide to use sequences, you must be familiar with the available options and parameters.

About this task

Before designing sequences, see Sequences compared to identity columns.
In addition to being simple to set up and create, the sequence has a variety of additional options that allows you more flexibility in generating the values:
  • Choose from a variety of data types (SMALLINT, INTEGER, BIGINT, DECIMAL)
  • Change starting values (START WITH)
  • Change the sequence increment, including specifying increasing or decreasing values (INCREMENT BY)
  • Set minimum and maximum values where the sequence numbering starts and stops (MINVALUE/MAXVALUE)
  • Allow wrapping of values so that sequences can start over again, or disallow cycling (CYCLE/NO CYCLE)
  • Allow caching of sequence values to improve performance, or disallow caching(CACHE/NO CACHE)

Even after the sequence has been generated, many of these values can be altered. For instance, you might want to set a different starting value depending on the day of the week. Another practical example of using sequences is for the generation and processing of bank checks. The sequence of bank check numbers is extremely important, and there are serious consequences if a batch of sequence numbers is lost or corrupted.

For improved performance, you should also be aware of and make use of the CACHE option. This option tells the database manager how many sequence values should be generated by the system before going back to the catalog to generate another set of sequences. The default CACHE value is 20, if not specified. Using the default as an example, the database manager automatically generates 20 sequential values in memory (1, 2, ...., 20) when the first sequence value is requested. Whenever a new sequence number is required, this memory cache of values is used to return the next value. Once this cache of values is used up, the database manager will generate the next twenty values (21, 22, ...., 40).

By implementing caching of sequence numbers, the database manager does not have to continually go to the catalog tables to get the next value. This reduces the extra processing associated with retrieving sequence numbers, but it also leads to possible gaps in the sequences if a system failure occurs, or if the system is shut down. For instance, if you decide to set the sequence cache to 100, the database manager will cache 100 values of these numbers and also set the system catalog to show that the next sequence of values should begin at 201. In the event that the database is shut down, the next set of sequence numbers will begin at 201. The numbers that were generated from 101 to 200 will be lost from the set of sequences if they were not used. 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 sequence values.

Gaps in a sequence

When Db2 generates a value for a sequence, that value can be said to be "consumed", regardless of whether that value is used by the application. If a sequence value is consumed but not used by the application, gaps can be created. See CREATE SEQUENCE for examples of how gaps can be introduced in the sequence values.
Note: The number and type of situations where gaps are generated can change and, as a result, cannot be listed here. You should expect some occurrences of gaps in a sequence.

For more information about all available options and associated values, see the CREATE SEQUENCE statement.