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:
- 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
- DBADM authority
To replace an existing sequence, the
authorization ID of the statement must be the owner of the existing
sequence (SQLSTATE 42501).
Syntax
>>-CREATE--+------------+--SEQUENCE--sequence-name--●----------->
'-OR REPLACE-'
.-AS INTEGER--------.
>--+-------------------+--●--+------------------------------+--->
'-AS--| data-type |-' '-START WITH--numeric-constant-'
.-INCREMENT BY 1-----------------.
>--●--+--------------------------------+--●--------------------->
'-INCREMENT BY--numeric-constant-'
.-NO MINVALUE----------------.
>--+----------------------------+--●---------------------------->
'-MINVALUE--numeric-constant-'
.-NO MAXVALUE----------------. .-NO CYCLE-.
>--+----------------------------+--●--+----------+--●----------->
'-MAXVALUE--numeric-constant-' '-CYCLE----'
.-CACHE 20----------------. .-NO ORDER-.
>--+-------------------------+--●--+----------+--●-------------><
+-CACHE--integer-constant-+ '-ORDER----'
'-NO CACHE----------------'
data-type
|--+-| built-in-type |--+---------------------------------------|
'-distinct-type-name-'
built-in-type
|--+-+-SMALLINT----+------------------------------+-------------|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
'-+-+-DECIMAL-+-+--+-------------------------+-'
| '-DEC-----' | | .-,0-------. |
'-+-NUMERIC-+-' '-(integer-+----------+-)-'
'-NUM-----' '-,integer-'
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 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.
- 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.
- 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
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
Examples
Example 1: 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