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:

Table 1. ALTER SEQUENCE 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:

Table 2. ALTER SEQUENCE 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;