Syntax

CREATE SEQUENCE <name> [options] ;

options : option [options]

option :
START WITH <positive-initeger> |
INCREMENT BY <positive-integer> |
MAXVALUE <positive-integer> |
MINVALUE <positive-integer> |
NO CACHE |
CACHE <positive-integer> |
[NO] CYCLE |
LARGEINT | INT UNSIGNED | SMALLINT UNSIGNED

A sequence is a database object registered in system metadata. It generates a sequence of unique values that satisfy the options specified. Values are guaranteed to be unique even when the same sequence is accessed from multiple concurrent sessions. The sequence value can be obtained with a SELECT statement using the seqnum function

SELECT SEQNUM(<sequence-name>) FROM (VALUES(1)) X(a) ;
INSERT INTO <table-name> VALUES (SEQNUM(<sequence-name>)) ;  ---- Table <table-name> has one column of appropriate type
SELECT SEQNUM(<sequence-name>) FROM <table-name> ; ---- as many sequence values produces as rows in table <table-name> 

The default type of a sequence is LARGEINT. The maximum value of a sequence by default is the maximum value of its type. The minimum value by default is 1. Start value and increment also default to 1. The cache option allows sequence values to generated as a contiguous set and used from a session without accessing metadata for each subsequent value. By default cache size is set at 25. By default a sequence object does not cycle, though the CYCLE option can be used to go back to start value once MAXVALUE has been reached.

SHOWDDL SEQUENCE <sequence-name> ;

can be used to see the CREATE SEQUENCE definition of a sequence object.

ALTER SEQUENCE <sequence-name> options ;

can be used to change various properties of a sequence. START WITH value cannot be altered. The keyword reset can be used instead of options list in the ALTER SEQUENCE statement, to reset the sequence to its start value.

A sequence object can also be dropped with

DROP SEQUENCE <sequence-name> ;

IDENTITY COLUMN

A column in a CREATE TABLE statement can be declared as an IDENTITY column. The system will then create a SEQUENCE object and use it to generate unique values to populate unique this column during INSERT statements. IDENTITY columns can be declared as 

CREATE TABLE <table-name> ( <column-name> <type> GENERATED [ ALWAYS | BY DEFAULT ] AS IDENTITY, ...);

<type> must be LARGEINT, INT UNSIGNED or SMALLINT UNSIGNED. Either ALWAYS or BY DEFAULT must be specified. If ALWAYS is specified, an INSERT statement will not be able to specify values for this column. The system alone is responsible for generating values for this column. If BY DEFAULT is specified, then the system is responsible for generating values if the user does not provide a value in the INSERT statement.