Previous Topic Next topic Print topic


CREATE SEQUENCE

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

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

  • The CREATEIN privilege on the schema
  • SYSADM or SYSCTRL authority

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process.

If the data type of the sequence is a distinct type, the privilege set must include the USAGE privilege on the distinct type.

Syntax

CREATE SEQUENCE sequence-name [AS {INTEGER | data-type} |
     START WITH numeric-constant |
     {INCREMENT BY 1 | INCREMENT BY numeric-constant} |
     {MINVALUE numeric-constant | NO MINVALUE} |
     {MAXVALUE numeric-constant | NO MAXVALUE} |
     {CYCLE | NO CYCLE} |
     {CACHE 20 | CACHE integer-constant | NO CACHE} |
     {ORDER | NO ORDER}
     ] [,...]
Note: The same clause must not be specified more than once. Separator commas may or may not be specified between sequence attributes when a sequence is defined.

Description

SEQUENCE sequence-name

Names the sequence. The combination of name and the implicit or explicit name must not identify an existing sequence at the current server, including the internal names generated by DB2 for identity names.

The unqualified form of sequence-name is an SQL identifier. The unqualified name is implicitly qualified with a schema name according to the following rules:

  • If the CREATE SEQUENCE statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
  • If the CREATE SEQUENCE statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

The contents of the SQL PATH are not used to determine the implicit qualifier of a sequence name.

The qualified form of sequence-name is a schema followed by a period and an SQL identifier. The schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.

The owner of the sequence is determined by the following conditions. If the CREATE SEQUENCE statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package. If the CREATE SEQUENCE statement is dynamically prepared, the owner is the SQL authorization ID contained in the CURRENT SQLID special register. The owner has the ALTER and USAGE privileges on the new sequence with the GRANT option. These privileges can be granted by the owner and cannot be revoked from the owner.

AS data-type

Specifies the data type to be used for the sequence value. The data type can be any exact numeric data type (SMALLINT, INTEGER, or DECIMAL with a scale of zero), or a user-defined distinct type for which the source type is an exact numeric data type with a scale of zero. The default is INTEGER.

START WITH numeric-constant

Specifies the first value for the sequence. The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point.

If the START WITH key word is not explicitly specified with a value, the default is the 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. The value can be any positive or negative value (including 0) that could be assigned to a column of the data type that is associated with the sequence without any non-zero digits existing to the right of the decimal point. The default is 1.

If INCREMENT BY is positive, the sequence ascends. If INCREMENT BY is negative, the sequence descends. If INCREMENT is 0, the sequence is treated as an ascending sequence.

The absolute value of INCREMENT BY can be greater than the difference between MAXVALUE and MINVALUE.

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. The default is NO MINVALUE.

MINVALUE numeric-constant

Specifies the minimum end of the range of values for the sequence. The last value that is generated for a cycle of a descending sequence will be equal to or greater than this value. MINVALUE is the value to which an ascending sequence cycles to after reaching the maximum value.

The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.

NO MINVALUE

Specifies that the minimum end point of the range of values for the sequence has not been specified explicitly. In such a case, the default value for MINVALUE becomes one of the following:

  • 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 that is associated with the sequence.

MAXVALUE or NO MAXVALUE

Specifies the minimum value at which an ascending sequence either cycles or stops generating values or an descending sequence cycles to after reaching the minimum value. The default is NO MAXVALUE.

MAXVALUE numeric-constant

Specifies the maximum end of the range of values for the sequence. The last value that is generated for a cycle of an ascending sequence will be less than or equal to this value. MAXVALUE is the value to which a descending sequence cycles to after reaching the minimum value.

The value can be any positive or negative value that could be assigned to the a column of the data type that is associated with the sequence without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.

NO MAXVALUE

Specifies the maximum end point of the range of values for the sequence has not been specified explicitly. In such a case, the default value for MAXVALUE becomes one of the following:

  • For an ascending sequence, the value is the maximum value of the data type that is 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 or not 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. The default is NO CYCLE.

CYCLE

Specifies that the sequence continue to generate values after either 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 defined by the MINVALUE and MAXVALUE options determine the range that is used for cycling.

When CYCLE is in effect, duplicate values can be generated by the sequence. When a sequence is defined with CYCLE, any application conversion tools for converting applications from other vendor platforms to DB2 should also explicitly specify MINVALUE, MAXVALUE, and START WITH values.

NO CYCLE

Specifies that the sequence cannot generate more values once the maximum or minimum value for the sequence has been reached. The NO CYCLE option (the default) can be altered to CYCLE at any time during the life of the sequence.

When the next value is being generated for a sequence if the maximum value (for an ascending sequence) or the minimum value (for a descending sequence) of the logical range of the sequence is exceeded and the NO CYCLE option is in effect, an error occurs.

CACHE or NO CACHE

Specifies whether or not 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 values of the sequence that XDB can preallocate and keep in memory. Preallocating values in the cache reduces synchronous I/O when values are generated for the sequence. The actual number of values that XDB caches is always the lesser of the number in effect for the CACHE option and the number of remaining values within the logical range. Thus, the CACHE value is essentially an upper limit for the size of the cache.

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. The default is CACHE 20.

In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple XDB members to cache sequence values simultaneously.

NO CACHE

Specifies that values of the sequence are not to be preallocated. This option ensures that there is not a loss of values in the case of a system failure. When NO CACHE 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.

ORDER or NO ORDER

Specifies whether the sequence numbers must be generated in order of request. The default is NO ORDER.

ORDER

Specifies that the sequence numbers are generated in order of request. Specifying ORDER may disable the caching of values. There is no guarantee that values are assigned in order across the entire server unless NO CACHE is also specified. ORDER applies only to a single-application process.

NO ORDER

Specifies that the sequence numbers do not need to be generated in order of request.

In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for next value assignments from different members may not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the same sequence, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested for next value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that sequence numbers are generated in strict numeric order among multiple members using the same sequence concurrently, specify the ORDER option.

Note:
  • MINVALUE must not be greater than MAXVALUE. Although MINVALUE is typically less than MAXVALUE, MINVALUE can equal MAXVALUE. If START WITH were the same value as MINVALUE and MAXVALUE, the sequence would be constant. The request for the next value in a constant sequence appears to have no effect because all of the values that are generated by the sequence are in fact the same value.
  • When you define a sequence, you can choose to have it cycle automatically or not when the maximum or minimum value for the sequence has been reached.
    • Implicitly or explicitly defining a sequence with NO CYCLE causes the sequence to not cycle automatically after the boundary is reached. However, you can use the ALTER SEQUENCE statement to cycle the sequence manually. ALTER SEQUENCE allows you to restart or extend the sequence, which causes sequence values to continue to be generated.
    • Explicitly defining a sequence with CYCLE causes the sequence to cycle automatically after the boundary is reached. Sequence values continue to be generated after the sequence cycles.

      When a sequence is defined to cycle automatically, the maximum or minimum value that is generated for a sequence may not be the actual MAXVALUE or MINVALUE value that is 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 a sequence is defined with CYCLE, any application conversion tools (for converting applications from other vendor platforms to DB2) should also explicitly specify MINVALUE, MAXVALUE, and START WITH.

  • You can define a sequence such that it always returns the same (or a constant) value. To create a constant sequence, use either of these techniques when defining the sequence:
    • Specify an INCREMENT value of zero and a START WITH value that does not exceed MAXVALUE.
    • Specify the same value for START WITH, MINVALUE, and MAXVALUE, and specify CYCLE. A constant sequence can be used as a numeric global variable. You can use ALTER SEQUENCE to adjust the values that are generated for a constant sequence.
  • After XDB generates a value for a sequence, that value can be said to be "consumed" regardless of whether or not that value is utilized by the application or not. The value is not reused within the current cycle. A consumed value may not be utilized when the statement that caused the value to be generated fails for some reason or is rolled back after the value was generated. Generated but unused values may constitute gaps in a sequence.
  • Consecutive values in a sequence differ by the constant INCREMENT BY value specified for the sequence. However, gaps can be introduced in a sequence in situations like the following ones:
    • 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 is dropped and then the dropping is rolled back.

      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 may 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.

  • It is possible the duplicate values can be generated for a sequence. Duplicate values are most likely to occur when a sequence is defined with the CYCLE option, is defined as a constant sequence, or is altered. For example, the following situations could cause duplicate sequence values:
    • A sequence is defined with the attributes START WITH=2, INCREMENT BY 2, MINVALUE=2, MAXVALUE=10, and CYCLE.
    • The ALTER SEQUENCE statement is used to restart the sequence with a value that has already been generated.
    • The ALTER SEQUENCE statement is used to reverse the ascending direction of a sequence by changing the INCREMENT BY value from a positive to a negative.
  • A sequence can be referenced using a sequence reference. A sequence reference can appear in most places that an expression can appear. A sequence reference can specify whether the value to be returned is a newly generated value or the previously generated value. A NEXT VALUE sequence expression is used to generate a new value. A PREVIOUS VALUE sequence expression is used to obtain the last assigned value of a sequence.
  • To provide compatibility with previous releases, XDB supports the following keywords:
    • NOMINVALUE (single key word) as a synonym for NO MINVALUE
    • NOMAXVALUE (single key word) as a synonym for NO MAXVALUE
    • NOCYCLE (single key word) as a synonym for NO CYCLE
    • NOCACHE (single key word) as a synonym for NO CACHE
    • NOORDER (single key word) as a synonym for NO ORDER
Previous Topic Next topic Print topic