A sequence is an object for creating an incremental number series. Sequences can generate sequential values within any integer range with either positive or negative increments. The database holds the sequence definition and keeps track of the next available value.
To create a multi-tenant sequence, use the MULTI_TENANT attribute after defining all the other sequence attributes in the CREATE SEQUENCE syntax.
Syntax
CREATE SEQUENCE [schema_name] sequence_name [INCREMENT BY value],
[START WITH value],
[MAXVALUE value| NOMAXVALUE],
[MINVALUE value| NOMINVALUE],
[CYCLE | NOCYCLE] [MULTI_TENANT];
Parameters
schema_name
Specifies the schema to contain the sequence. If schema_name is not specified, the sequence generator creates the sequence in the current schema. OpenEdge supports only the PUBLIC (PUB) schema.
sequence_name
Specifies the name of the sequence to be created.
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 the 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 for a descending sequence it is MAXVALUE.
MAXVALUE
Specifies the maximum value to be generated for the sequence. The default value is -1 for a 32-bit and a 64-bit descending sequence. The default value is 2,147,483,647 for a 32-bit ascending sequence and 9223372036854775807 for a 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,648 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 a value after reaching the MAXVALUE (for an ascending sequence)or MINVALUE (for a descending sequence).
NOCYCLE
Indicates that the sequence cannot generate a value after reaching 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.
MULTI_TENANT
Specifies the sequence defined as a multi-tenant sequence.
Examples
Generating unique customer numbers
The following example shows how a sequence generates unique customer numbers when a new customer is inserted into the pub.customer table:
CREATE SEQUENCE pub.customer_sequence
START WITH 100,
INCREMENT BY 1,
NOCYCLE;
Defining a multi-tenant sequence
The following example shows how to define a multi-tenant sequence:
CREATE SEQUENCE pub.mtcustomer_sequence
START WITH 100,
INCREMENT BY 1,
NOCYCLE
MULTI_TENANT
;
