ALTER SEQUENCE
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:
- For the SYSSEQOBJECTS catalog table:
- The UPDATE privilege on the table, and
- The system authority *EXECUTE on library QSYS2
- Administrative authority
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
- For the distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
- Administrative authority
For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Sequence, Corresponding System Authorities When Checking Privileges to a Table or View, and Corresponding System Authorities When Checking Privileges to a Distinct Type.
Syntax
>>-ALTER SEQUENCE--sequence-name--------------------------------> .---------------------------------------------. V (1) | >----+-DATA TYPE--data-type----------------+-----+------------->< +-RESTART--+------------------------+-+ | '-WITH--numeric-constant-' | +-INCREMENT BY--numeric-constant------+ +-+-NO MINVALUE----------------+------+ | '-MINVALUE--numeric-constant-' | +-+-NO MAXVALUE----------------+------+ | '-MAXVALUE--numeric-constant-' | +-+-NO CYCLE-+------------------------+ | '-CYCLE----' | +-+-NO CACHE----------------+---------+ | '-CACHE--integer-constant-' | '-+-NO ORDER-+------------------------' '-ORDER----'
- The same clause must not be specified more than once.
data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-' built-in-type |--+-+---SMALLINT---+---------------------------+---------------| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)-----------------. | '-+-+-DECIMAL-+-+--+-----------------------+-' | '-DEC-----' | | .-,0-. | '-+-NUMERIC-+-' '-(--integer--+----+--)-' '-NUM-----'
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