Sequence Description

sequence_description :== SEQUENCE [scheme.]sequence_name [ sequence_parameter … ] 
sequence_parameter :== AS type | START WITH integer_number | RESTART WITH integer_number 
    | INCREMENT BY integer_number 
    | MAXVALUE integer_number | NOMAXVALUE | MINVALUE integer_number | NOMINVALUE 
    | CYCLE | NOCYCLE | CACHE integer_number | NOCACHE | ORDER | NOORDER

The integer_number is a BIGINT type number (64-bit integer - c. +/-9223372036854775000).

You can specify these items when creating or modifying a sequence:

value types (AS);
initial value of the sequence (START WITH);
value, which will continue with sequence generation (RESTART WITH) - only in ALTER SEQUENCE;
sequence step, i.e. the difference between two consecutive values in a sequence (INCREMENT BY);
maximum, that cannot be exceeded when generating a sequence (MAXVALUE);
minimum, that cannot be exceeded when generating a sequence (MINVALUE, only for descending sequences);
a flag, whether the sequence should continue generating values periodically when it reaches its maximum (or minimum) (CYCLE or NOCYCLE, CYCLE is not suitable for generating unique keys);
buffer utilization and number of values in the buffer (CACHE or NOCACHE).

An integer value containing the actual value is always bound to a sequence. The actual value is the value that is returned as the following sequence number.

If the value type of a sequence is specified, it has to be some of the integer types. 602SQL ignores this type and always uses BIGINT. The AS clause is allowed only for the SQL 2003 compatibility.

The default values of nonspecified parameters are START WITH 0 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20. The nonspecified parameters in the ALTER SEQUENCE statement are preserved from the original sequence definition. The value that should be generated as next is also preserved (if it belongs to the interval between the maximum and minimum value).

The sequence step cannot be zero. If the step is negative, the values are descending.

Maximum must be greater than minimum and the sequence initial value must be in between. If the sequence step is positive, the default value for minimum will be 1, the default value for maximum will be the highest possible integer value and the initial value will equal to the minimum. If the sequence step is negative, then the default minimum value will be the lowest possible integer number, the default maximum value will be –1 and the initial value will equal to the maximum.

If the cyclic number generation is enabled using the CYCLE parameter, then the sequence will automatically generate its (set of default) minimum (or maximum for descending sequences) value as the next value when it reaches its maximum (or minimum).

Buffer utilization significantly fastens the value generation. The whole cache is filled when the first need to allocate a value arises. If some cache values are not used, they'll be returned into the sequence when the server is shutting down. If the server is not correctly terminated because of a system failure, the cache values will be lost.

The ORDER and NOORDER parameters are ignored.

The actual value of a sequence is preserved when exporting an application with data. A sequence is reset when exporting application without data (it's set to the initial value).

Usage Example:

CREATE SEQUENCE SEQ10
  START WITH 0
  INCREMENT BY 10
  NOMAXVALUE
  NOMINVALUE
  CACHE 5
  NOCYCLE

Variances from the SQL Standard

602SQL doesn't restrict the sequence values to the type explicitly specified in the AS clause. This is an extension of the SQL 2003 norm.
The CACHE, NOCACHE, ORDER and NORODER clauses are also an extension of the SQL 2003 norm. They allow compatibility with Oracle databases

See

Sequences

CREATE SEQUENCE Statement