The
ALTER SEQUENCE statement can be used to change a sequence.
A sequence can be changed in the following ways:
- Restarting the sequence
- Changing the increment between future sequence values
- Setting or eliminating the minimum or maximum values
- Changing the number of cached sequence numbers
- Changing the attribute that determines whether the sequence can
cycle or not
- Changing whether sequence numbers must be generated in order of
request
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:
- ALTER privilege on the sequence to be altered
- ALTERIN privilege on the schema implicitly or explicitly specified
- DBADM authority
Syntax
>>-ALTER SEQUENCE--sequence-name-------------------------------->
.-----------------------------------------------.
V (1) |
>----------+-RESTART--+------------------------+-+-+-----------><
| '-WITH--numeric-constant-' |
+-INCREMENT BY--numeric-constant------+
+-+-MINVALUE--numeric-constant-+------+
| '-NO MINVALUE----------------' |
+-+-MAXVALUE--numeric-constant-+------+
| '-NO MAXVALUE----------------' |
+-+-CYCLE----+------------------------+
| '-NO CYCLE-' |
+-+-CACHE--integer-constant-+---------+
| '-NO CACHE----------------' |
'-+-ORDER----+------------------------'
'-NO ORDER-'
Notes:
- The same clause must not be specified more than once.
Description
- sequence-name
- Identifies the sequence that is to be changed. The name, including
the implicit or explicit schema qualifier, must uniquely identify
an existing sequence at the current server. If no sequence by this
name exists in the explicitly or implicitly specified schema, an error
(SQLSTATE 42704) is returned. sequence-name must
not be a sequence generated by the system for an identity column (SQLSTATE
428FB).
- RESTART
- Restarts the sequence. If numeric-constant is
not specified, the sequence is restarted at the value specified implicitly
or explicitly as the starting value on the CREATE SEQUENCE statement
that originally created the sequence.
- WITH numeric-constant
- Restarts the sequence with the specified 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).
- 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,
then this is a descending sequence. If this value is 0 or positive,
this is an ascending sequence after the ALTER statement.
- 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 original starting
value. For a descending sequence, the value is the minimum value of
the data type associated with the sequence.
- 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 original starting value.
- 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 the value.
- 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; or 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.
- 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).
- 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.
- ORDER or NO 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.
Notes
- Only future sequence numbers are affected by the ALTER SEQUENCE
statement.
- The data type of a sequence cannot be changed. Instead, drop and
re-create the sequence specifying the required data type for the new
sequence.
- All cached values are lost when a sequence is altered.
- After restarting a sequence or changing to CYCLE, it is possible
for sequence numbers to be duplicate values of ones generated by the
sequence previously.
- 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
Example
A
possible reason for specifying RESTART without a numeric value would
be to reset the sequence to the START WITH value. In this example,
the goal is to generate the numbers from 1 up to the number of rows
in the table and then inserting the numbers into a column added to
the table using temporary tables. Another use would be to get results
back where all the resulting rows are numbered:
ALTER SEQUENCE ORG_SEQ RESTART
SELECT NEXT VALUE FOR ORG_SEQ, ORG.* FROM ORG