ALTER SEQUENCE
Use the ALTER SEQUENCE command to reset a user sequence option, including the name and owner of the sequence. This statement affects only future sequence numbers.
Note: If you change the increment, raise the minvalue for
ascending sequences, or lower the maxvalue for descending sequences,
the system clears the cache, which results in nonsequential sequence
numbers. Therefore, if you want to retain the original starting value,
also specify the RESTART WITH parameter.
Syntax
Syntax for altering a sequence
ALTER SEQUENCE <seq_name> <clause> [<clause>…]
Where
<clause>
represents
one of:INCREMENT BY <value> |
NO CYCLE | CYCLE |
NO MAXVALUE | MAXVALUE <value> |
NO MINVALUE | MINVALUE <value> |
OWNER TO <value> |
RENAME TO <value> |
RESTART WITH <value>
Inputs
The ALTER SEQUENCE
command takes the following inputs:
Input | Description |
---|---|
<seq_name> | The name of the sequence. |
INCREMENT BY | Set a new increment value. |
NO CYCLE | Do not cycle the sequence. |
CYCLE | Cycle the sequence. |
NO MAXVALUE | Do not limit the maximum value. |
MAXVALUE | Set the maximum value. |
NO MINVALUE | Do not limit the minimum value. |
MINVALUE | Set the minimum value. |
OWNER TO | Change the owner of the sequence. |
RENAME TO | Rename the sequence. |
RESTART WITH | Specify the new starting sequence number. |
Outputs
The ALTER SEQUENCE
command produces the following outputs:
Output | Description |
---|---|
ALTER SEQUENCE | The command is successful. |
ERROR: ALTER on system sequence not allowed. | You attempted to alter a system sequence. |
Privileges
You must be the admin user, the sequence owner, the owner of the database or schema where the sequence is defined, or your account must have the Alter privilege for thesequence or for the Sequence object class.
Usage
The following examples
illustrate how:
- Change the maximum value:
MYDB.SCH1(USER)=> ALTER SEQUENCE seq1 MAXVALUE 1000;