ALTER SEQUENCE
This topic describes how to change the start value of a sequence and how to convert the type of a sequence from one type to a different type. A group sequence that contains multiple units cannot be converted to a sequence of another type.
Precautions
Before you convert the type of a sequence from one type to a different type, take note of the following points:
You cannot convert a group sequence that contains multiple units to a sequence of another type or change the values of the UNIT COUNT parameter and INDEX parameter. If you change the sequence type or the value of the UNIT COUNT parameter or INDEX parameter, the sequence cannot work as expected.
Before you change the value of the START WITH parameter, analyze the existing sequence values and the rate at which sequence values are generated to prevent duplicate sequence values from being generated. Proceed with caution when you change the value of the START WITH parameter.
When you execute the
ALTER SEQUENCE CHANGE TOstatement to convert the type of a sequence from one type to a different type, you must configure the START WITH parameter. If the ALTER SEQUENCE statement that is executed to modify a sequence does not include theCHANGE TOclause, the START WITH parameter is optional.
New sequences
Syntax
ALTER SEQUENCE <name> [ CHANGE TO GROUP | TIME ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]
Parameters
| Parameter | Description |
|---|---|
| START WITH | The start value for the new sequence. If you do not configure this parameter, the default start value is used. Default value: 1. |
| INCREMENT BY | The increment between two adjacent sequence values, also called the interval value or step size. If you do not configure this parameter, the default start value is used. Default value: 1. This parameter is supported only when you modify the properties of a new sequence. If you convert a new sequence to a group or time sequence, this parameter is not supported. |
| MAXVALUE | The maximum value for the new sequence. The value must be a positive integer. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type. This parameter is supported only when you modify the properties of a new sequence. If you convert a new sequence to a group or time sequence, this parameter is not supported. |
| CYCLE or NOCYCLE | You can select only one of the parameters. CYCLE: When the maximum value for the new sequence is reached, the start value can be used again in the new sequence. NOCYCLE: When the maximum value for the new sequence is reached, the start value cannot be used again in the new sequence. If more values are assigned, an error is returned. If you do not configure this parameter, the default value of NOCYCLE is used. This parameter is supported only when you modify the properties of a new sequence. If you convert a new sequence to a group or time sequence, this parameter is not supported. |
Note
A new sequence can be converted to a group sequence that contains only one unit.
After a new sequence is converted to a time sequence, the original start value of the sequence becomes invalid.
Group sequences
Syntax
ALTER SEQUENCE <name> [ CHANGE TO NEW | TIME ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]
Parameters
| Parameter | Description |
|---|---|
| START WITH | The start value for the group sequence. If you do not configure this parameter, the default start value is used. Default value: 1. |
| INCREMENT BY | The increment between two adjacent sequence values, also called the interval value or step size. If you do not configure this parameter, the default start value is used. Default value: 1. This parameter is supported only when you convert a group sequence to a new sequence. |
| MAXVALUE | The maximum value for the group sequence. The value must be a positive integer. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type. This parameter is supported only when you convert a group sequence to a new sequence. |
| CYCLE or NOCYCLE | You can select only one of the parameters. This parameter is supported only when you convert a group sequence to a new sequence. CYCLE: When the maximum value for the group sequence is reached, the start value can be used again in the group sequence. NOCYCLE: When the maximum value for the group sequence is reached, the start value cannot be used again in the new sequence. If more values are assigned, an error is returned. If you do not configure this parameter, the default value of NOCYCLE is used. |
Note
When you convert a group sequence to a time sequence, the values of the START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters becomes invalid.
Time sequences
Syntax
ALTER SEQUENCE <name> [ CHANGE TO NEW | GROUP ]
START WITH <numeric value>
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]
Parameters
| Parameter | Description |
|---|---|
| START WITH | The start value for the time sequence. If you do not configure this parameter, the default start value is used. Default value: 1. |
| INCREMENT BY | The increment between two adjacent sequence values, also called the interval value or step size. If you do not configure this parameter, the default start value is used. Default value: 1. This parameter is supported only when you convert a time sequence to a new sequence. |
| MAXVALUE | The maximum value for the time sequence. The value must be a positive integer. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type. This parameter is supported only when you convert a time sequence to a new sequence. |
| CYCLE or NOCYCLE | You can select only one of the parameters. This parameter is supported only when you convert a time sequence to a new sequence. CYCLE: When the maximum value for the time sequence is reached, the start value can be used again in the time sequence. NOCYCLE: When the maximum value for the time sequence is reached, the start value cannot be used again in the time sequence. If more values are assigned, an error is returned. If you do not configure this parameter, the default value of NOCYCLE is used. |
Note When you convert a time sequence to a group sequence, the values of the START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE parameters becomes invalid because only one unit is contained.
Examples
Change the start value of a sequence.
ALTER SEQUENCE seq1 START WITH 1000000;
Convert a sequence to a group sequence.
ALTER SEQUENCE seq2 CHANGE TO GROUP START WITH 2000000;
Convert a sequence to a time sequence.
ALTER SEQUENCE seq3 CHANGE TO TIME;
Convert a sequence to a new sequence and use the default values of the START WITH, MAXVALUE, and CYCLE or NOCYCLE parameters.
ALTER SEQUENCE seq4 CHANGE TO NEW START WITH 100;
Convert a sequence to a new sequence and specify values for the START WITH, MAXVALUE, and CYCLE or NOCYCLE parameters.
ALTER SEQUENCE seq5 CHANGE TO NEW START WITH 200 INCREMENT BY 2 MAXVALUE 300 NOCYCLE;