The ALTER SEQUENCE statement can be used to change the current value of an existing sequence. The sequence can be in the current schema or a schema can be specified.
When altering a sequence of a multi-tenant table as a regular tenant, the FOR TENANT phrase is optional to modify its CURRVAL, and a regular tenant does not have the privileges to modify the global attributes of a sequence.
When altering the sequence of a multi-tenant table as a DBA or a super-tenant, the FOR TENANT phrase is mandatory for a DBA or a super-tenant to identify the tenant in the multi-tenant database that must be altered. A DBA or a super-tenant can alter all the sequence attributes of a multi-tenant table.
Syntax
ALTER SEQUENCE [schema_name]sequence_name[SET
{CURRVAL value[ FOR TENANT tenant_name]| START WITH value| INCREMENT BY value| MAXVALUE value | NOMAXVALUE | MINVALUE value|NOMINVALUE | CYCLE | NOCYCLE }];
schema_name
Specifies the name of the schema name that contains the sequence. If this is not specified, OpenEdge SQL alters the sequence.
sequence_name
Specifies the sequence to be altered.
CURRVAL
Returns the current value of the sequence.
FOR TENANT tenant_name
Specifies the name of the tenant corresponding to the CURRVAL.
INCREMENT BY
Specifies the interval between sequence numbers. The value can be a positive or negative integer (INTEGER data type for 32-bit sequences, BIGINT datatype for 64-bit sequences), but it cannot be 0. The value range for a 32-bit sequence is -2,147,483,648 to 2,147,483,647. The value range for a 64-bit sequence is -9223372036854775808 to 9223372036854775807. The value is positive for an ascending sequence and negative for a descending sequence. The default value is 1.
START WITH
Specifies the first number in a sequence generated. The value must be greater than or equal to the MINVALUE for an ascending sequence. The value must be less than or equal to the MAXVALUE for a descending sequence. The default value for an ascending sequence is MINVALUE and MAXVALUE for a descending sequence.
MAXVALUE
Specifies the maximum value that the sequence can generate. The default value is -1 for both 32-bit and 64-bit descending sequences. The default value is 2,147,483,647 for a 32-bit ascending sequence and 9223372036854775807 for a 64-bit ascending sequence.
NOMAXVALUE
Specifies -1 as the MAXVALUE for a 32-bit descending sequence and 2,147,483,647 as the MAXVALUE for a 32-bit ascending sequence. Specifies -1 as the MAXVALUE for a 64-bit descending sequence and 9223372036854775807 as the MAXVALUE for a 64-bit ascending sequence.
MINVALUE
Specifies the minimum value that the sequence can generate. The default value is 0 for an ascending sequence. The default value is -2,147,483,6480 for a 32-bit descending sequence and -9223372036854775808 for a 64-bit sequence.
NOMINVALUE
Specifies 0 as the MINVALUE for an ascending sequence. The MINVALUE is -2,147,483,648 for a 32-bit descending sequence and -9223372036854775808 for a 64-bit sequence.
CYCLE
Indicates that the sequence will continue to generate values after attaining the MAXVALUE (for an ascending sequence)or MINVALUE (for a descending sequence).
NOCYCLE
Indicates that the sequence cannot generate more values after attaining the MAXVALUE (for an ascending sequence) or MINVALUE (for a descending sequence). The SQL sequence generator uses NOCYCLE as the default if CYCLE is not specified.
Notes
It is possible to set only one attribute of a sequence at a time.
Attributes START WITH, INCREMENT BY, MAXVALUE, MINVALUE and CURRVAL can take either an INTEGER or BIGINT argument, depending on whether the sequence is a 32-bit or a 64-bit sequence.
Examples
The following example modifies a sequence by specifying a maximum value:
ALTER SEQUENCE pub.customer
SET MAXVALUE 9000000000;
The following example when executed by a DBA or a super-tenant, modifies a sequence of a multi-tenant table by specifying a maximum value:
ALTER SEQUENCE pub.cust_num_seq SET MAXVALUE 99999;
The following example when executed by a DBA or a super-tenant, modifies a sequence by specifying a CURRVAL for the tenant AsiaPecificCust of a multi-tenant table:
ALTER SEQUENCE pub.cust_num_seq SET CURRVAL 521 FOR TENANT mtAsiaPecificCust;
It is possible to set only one attribute of a sequence at a time.
