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.
The privilege set that is defined below must include at least one of the following:
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.
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}
] [,...]
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:
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.
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.
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.
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.
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.
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.
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:
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.
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.
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:
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.
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.
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.
Specifies whether or not to keep some preallocated values in memory for faster access. This is a performance and tuning option.
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.
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.
Specifies whether the sequence numbers must be generated in order of request. The default is NO 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.
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.
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.
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.