The ALTER SEQUENCE statement can be used to change a sequence.
The ALTER SEQUENCE statement can be used to change a sequence
in any of these 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 interactively. It is an executable
statement that can be dynamically prepared.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following:
- For the sequence identified in the statement:
- The system authority *EXECUTE on the library containing the sequence
- The ALTER privilege for the sequence
- Administrative authority
The privileges held by the authorization ID of the statement
must include at least one of the following:
- The following system authorities:
- *USE to the Change Data Area (CHGDTAARA) command
- *USE to the Retrieve Data Area (RTVDTAARA) command
- Administrative authority
The privileges held by the authorization ID of the statement
must include at least one of the following:
Description
- sequence-name
- Identifies the sequence to be altered. The name must identify
a sequence that already exists at the current server.
- DATA TYPE data-type
- Specifies the new data type to be used for the sequence value.
The data type can be any exact numeric type (SMALLINT, INTEGER, BIGINT,
DECIMAL, or NUMERIC) with a scale of zero, or a user-defined distinct
type for which the source type is an exact numeric type with a scale
of zero.
Each of the existing START WITH, INCREMENT BY, MINVALUE,
and MAXVALUE attributes that are not changed by the ALTER SEQUENCE
statement must contain a value that could be assigned to a column
of the data type associated with the new data type.
- built-in-type
- Specifies
the new built-in data type used as the basis for the internal representation
of the sequence. If the data type is DECIMAL or NUMERIC, the precision
must be less than or equal to 63 and the scale must be 0. See CREATE TABLE for a more complete description of
each built-in data type.
For portability of applications across
platforms, use DECIMAL instead of a NUMERIC data type.
- distinct-type-name
- Specifies
that the new data type of the sequence is a distinct type (a user-defined
data type). If the source type is DECIMAL or NUMERIC, the precision
of the sequence is the precision of the source type of the distinct
type. The precision of the source type must be less than or equal
to 63 and the scale must be 0. If a distinct type name is specified
without a schema name, the distinct type name is resolved by searching
the schemas on the SQL path.
- 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, without nonzero digits
to the right of the decimal point.
- 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, and does
not exceed the value of a large integer constant, without nonzero
digits existing to the right of the decimal point.
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.
- NO MINVALUE or 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.
- 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 (and precision, if DECIMAL or NUMERIC) associated with
the sequence.
- MINVALUE numeric-constant
- Specifies the numeric constant that is the minimum value that
is generated for this 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 and without non-zero digits to the right
of the decimal point. The value must be less than or equal to the
maximum value.
- NO MAXVALUE or 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.
- NO MAXVALUE
- For an ascending sequence, the value is the maximum value of the
data type (and precision, if DECIMAL or NUMERIC) associated with the
sequence. For a descending sequence, the value is the original starting
value.
- MAXVALUE numeric-constant
- Specifies the numeric constant that is the maximum value that
is generated for this 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 and without non-zero digits to the right
of the decimal point. The value must be greater than or equal to the
minimum value.
- CYCLE or NO CYCLE
- Specifies whether this sequence should continue to generate values
after reaching either the maximum or minimum value of 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.
- 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 the maximum value of
the sequence, it generates its minimum value. After a descending sequence
reaches its minimum value of the sequence, 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, duplicate
values can be generated for a sequence by the database manager.
- CACHE or NO CACHE
- Specifies whether to keep some preallocated values in memory.
Preallocating and storing values in the cache improves the performance
of the NEXT VALUE sequence expression.
- 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 when values are generated for the sequence.
In certain situations, such as system failure, all cached sequence
values that have not been used in committed statements are lost, and
thus, will never be used. The value specified for the CACHE option
is the maximum number of sequence values that could be lost in these
situations.
The minimum value is 2.
- NO CACHE
- Specifies that values of the sequence are not to be preallocated.
It ensures that there is not a loss of values in situations, such
as system failure. 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.
- NO ORDER or ORDER
- Specifies whether the sequence numbers must be generated in order
of request.
- NO ORDER
- Specifies that the sequence numbers do not need to be generated
in order of request.
- ORDER
- Specifies that the sequence numbers are generated in order of
request. If ORDER is specified, the performance of the NEXT VALUE
sequence expression will be worse than if NO ORDER is specified.
Notes
Altering
a sequence:
- Only future sequence numbers are affected by the ALTER SEQUENCE
statement.
- All the cached values are lost when a sequence is altered.
- After restarting a sequence or changing it to cycle, it is possible
that a generated value will duplicate a value previously generated
for that sequence.
Syntax alternatives: The following keywords are synonyms
supported for compatibility to prior releases of other DB2® products. These keywords are non-standard
and should not be used:
- The keywords NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER
can be used as synonyms for NO MINVALUE, NO MAXVALUE, NO CYCLE, NO
CACHE, and NO ORDER.
Examples
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 a table and then inserting
the numbers into a column added to the table using temporary tables.
ALTER SEQUENCE ORG_SEQ RESTART
DECLARE GLOBAL TEMPORARY TABLE TEMP_ORG AS
(SELECT NEXT VALUE FOR ORG_SEQ, ORG.*
FROM ORG) WITH DATA
INSERT INTO TEMP_ORG
SELECT NEXT VALUE FOR ORG_SEQ, ORG.*
FROM ORG
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