CREATE SEQUENCE statement

The CREATE SEQUENCE statement defines a sequence at the application server.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the sequence does not exist
  • CREATEIN privilege on the schema, if the schema name of the sequence refers to an existing schema
  • SCHEMAADM authority on the schema, if the schema name of the sequence refers to an existing schema
  • DBADM authority

To replace an existing sequence, the authorization ID of the statement must be the owner of the existing sequence (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACE SEQUENCEsequence-name●AS INTEGERASdata-type●START WITHnumeric-constant●INCREMENT BY 1INCREMENT BYnumeric-constant●NO MINVALUEMINVALUEnumeric-constant●NO MAXVALUEMAXVALUEnumeric-constant●NO CYCLECYCLE●CACHE 20CACHEinteger-constantNO CACHE●NO ORDERORDER●
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name1
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)( integer,0, integer)
Notes:
  • 1 The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type.

Description

OR REPLACE
Specifies to replace the definition for the sequence if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the sequence are not affected. This option is ignored if a definition for the sequence does not exist at the current server. This option can be specified only by the owner of the object.
sequence-name
Names the sequence. The combination of name, and the implicit or explicit schema name must not identify an existing sequence at the current server (SQLSTATE 42710).

The unqualified form of sequence-name is an SQL identifier. The qualified form is a qualifier followed by a period and an SQL identifier. The qualifier is a schema name.

If the sequence name is explicitly qualified with a schema name, the schema name cannot begin with 'SYS' or an error (SQLSTATE 42939) is raised.

AS data-type
Specifies the data type to be used for the sequence value. The data type can be any exact numeric type (SMALLINT, INTEGER, BIGINT or DECIMAL) with a scale of zero, or a user-defined distinct type or reference type for which the source type is an exact numeric type with a scale of zero (SQLSTATE 42815). The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2). The default is INTEGER.
START WITH numeric-constant
Specifies the first value for the sequence. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA). The default is MINVALUE for ascending sequences and MAXVALUE for descending sequences.

This value is not necessarily the value that a sequence would cycle to after reaching the maximum or minimum value of the sequence. The START WITH clause can be used to start a sequence outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.

INCREMENT BY numeric-constant
Specifies the interval between consecutive values of the sequence. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815). The value must not exceed the value of a large integer constant (SQLSTATE 42820) and must not contain nonzero digits to the right of the decimal point (SQLSTATE 428FA).

If this value is negative, this is a descending sequence. If this value is 0 or positive, this is an ascending sequence. The default is 1.

MINVALUE or NO MINVALUE
Specifies the minimum value at which a descending sequence either cycles or stops generating values, or an ascending sequence cycles to after reaching the maximum value.
MINVALUE numeric-constant
Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).
NO MINVALUE
For an ascending sequence, the value is the START WITH value, or 1 if START WITH is not specified. For a descending sequence, the value is the minimum value of the data type associated with the sequence. This is the default.
MAXVALUE or NO MAXVALUE
Specifies the maximum value at which an ascending sequence either cycles or stops generating values, or a descending sequence cycles to after reaching the minimum value.
MAXVALUE numeric-constant
Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that could be assigned to a column of the data type associated with the sequence (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).
NO MAXVALUE
For an ascending sequence, the value is the maximum value of the data type associated with the sequence. For a descending sequence, the value is the START WITH value, or -1 if START WITH is not specified.
CYCLE or NO CYCLE
Specifies whether the sequence should continue to generate values after reaching either its maximum or minimum value. The boundary of the sequence can be reached either with the next value landing exactly on the boundary condition, or by overshooting it.
CYCLE
Specifies that values continue to be generated for this sequence after the maximum or minimum value has been reached. If this option is used, after an ascending sequence reaches its maximum value it generates its minimum value; after a descending sequence reaches its minimum value it generates its maximum value. The maximum and minimum values for the sequence determine the range that is used for cycling.

When CYCLE is in effect, then duplicate values can be generated for the sequence.

NO CYCLE
Specifies that values will not be generated for the sequence once the maximum or minimum value for the sequence has been reached. This is the default.
CACHE or NO CACHE
Specifies whether to keep some preallocated values in memory for faster access. This is a performance and tuning option.
CACHE integer-constant
Specifies the maximum number of sequence values that are preallocated and kept in memory. Preallocating 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 sequence values that have not been used in committed statements are lost (that is, they will never be used). The value specified for the CACHE option is the maximum number of sequence values that could be lost in case of system failure.

The minimum value is 2 (SQLSTATE 42815). The default value is CACHE 20.

Use the CACHE and NO ORDER options to allow multiple caches of sequence values simultaneously. In a multi-partition or Db2® pureScale® environment, multiple members can cache them.

In a Db2 pureScale environment, if both CACHE and ORDER are specified, the specification of ORDER overrides the specification of CACHE and instead NO CACHE will be in effect.

NO CACHE
Specifies that values of the sequence are not to be preallocated. It ensures that there is not a loss of values in the case of a system failure, shutdown or database deactivation. When this option is specified, the values of the sequence are not stored in the cache. In this case, every request for a new value for the sequence results in synchronous I/O to the log.
NO ORDER or ORDER
Specifies whether the sequence numbers must be generated in order of request.
ORDER
Specifies that the sequence numbers are generated in order of request.
NO ORDER
Specifies that the sequence numbers do not need to be generated in order of request. This is the default.

Notes

  • It is possible to define a constant sequence, that is, one that would always return a constant value. This could be done by specifying an INCREMENT value of zero and a START WITH value that does not exceed MAXVALUE, or by specifying the same value for START WITH, MINVALUE and MAXVALUE. For a constant sequence, each time NEXT VALUE is invoked for the sequence, the same value is returned. A constant sequence can be used as a numeric global variable. ALTER SEQUENCE can be used to adjust the values that will be generated for a constant sequence.
  • A sequence can be cycled manually by using the ALTER SEQUENCE statement. If NO CYCLE is implicitly or explicitly specified, the sequence can be restarted or extended using the ALTER SEQUENCE statement to cause values to continue to be generated once the maximum or minimum value for the sequence has been reached.
  • A sequence can be explicitly defined to cycle by specifying the CYCLE keyword. Use the CYCLE option when defining a sequence to indicate that the generated values should cycle once the boundary is reached. When a sequence is defined to automatically cycle (that is, CYCLE was explicitly specified), the maximum or minimum value generated for a sequence might not be the actual MAXVALUE or MINVALUE specified, if the increment is a value other than 1 or -1. For example, the sequence defined with START WITH=1, INCREMENT=2, MAXVALUE=10 will generate a maximum value of 9, and will not generate the value 10. When defining a sequence with CYCLE, carefully consider the impact of the values for MINVALUE, MAXVALUE and START WITH.
  • Caching sequence numbers implies that a range of sequence numbers can be kept in memory for fast access. When an application accesses a sequence that can allocate the next sequence number from the cache, the sequence number allocation can happen quickly. However, if an application accesses a sequence that cannot allocate the next sequence number from the cache, the sequence number allocation may require having to wait for I/O operations to persistent storage. The choice of the value for CACHE should be done keeping in mind the performance and application requirements tradeoffs.
  • Gaps in a sequence: Consecutive values in a sequence differ by the constant INCREMENT BY value specified for the sequence. However, gaps can occur in the values that are assigned to a sequence object by Db2.
    The following situations are some examples of how gaps can be introduced in the sequence values:
    • A transaction has advanced the sequence and then rolls back.
    • The SQL statement leading to the generation of the next value fails after the value was generated.
    • The NEXT VALUE expression is used in the SELECT statement of a cursor in a DRDA environment where the client uses block-fetch and not all retrieved rows are fetched by the application.
    • The sequence is altered and then the alteration is rolled back.
    • The sequence (or an identity column table) is dropped and then the drop is rolled back.
    • The SYSIBM.SYSSEQ table space is stopped or closed for any reason (including when DSMAX is reached).
    • The Db2 subsystem is stopped or goes down.

    Values of such gaps are not available for the current cycle, unless the sequence is altered and restarted in a specific way to make them available.

    A sequence is incremented independently of a transaction. Thus, a given transaction increments the sequence two times might see a gap in the two numbers that it receives if other transactions concurrently increment the same sequence. Most applications can tolerate these instances as these are not really gaps.

  • The definer of a sequences is granted ALTER and USAGE privileges with the grant option. The owner of the sequence can drop the sequence.
  • Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2 and with other database products. These alternatives are non-standard and should not be used.
    • A comma can be used to separate multiple sequence options
    • NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can be specified in place of NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and NO ORDER, respectively
  • Considerations for a multi-partition or Db2 pureScale environment:
    • If the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously. This can happen at each member in a multi-partition or Db2 pureScale environment. The requests for next value assignments from different members might not result in the assignment of values in strict numeric order. Assume, for example, in a multi-partition or Db2 pureScale environment, that members DB1A and DB1B are using the same sequence, and DB1A gets the cache values 1 to 20 and DB1B gets the cache values 21 to 40. In this scenario, if DB1A requested the next value first, then DB1B requested, and then DB1A requested again, the actual order of values assigned would be 1,21,2. Therefore, to guarantee that sequence numbers are generated in strict numeric order among multiple members using the same sequence concurrently, specify the ORDER option.
    • In a Db2 pureScale environment, using the ORDER or NO CACHE option ensures that the values assigned to a sequence which is shared by applications across multiple members are in strict numeric order. If ORDER is specified, then NO CACHE is implied even if CACHE n is specified.

Example

Create a sequence called ORG_SEQ that starts at 1, increments by 1, does not cycle, and caches 24 values at a time: 
   CREATE SEQUENCE ORG_SEQ
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO CYCLE
     CACHE 24