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----'

Notes:

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