Sequencer objects are objects that are used to get sequence numbers.
DENSE sequences guarantee that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, then the sequence number allocations are also rolled back. The drawback of dense sequences is that the sequence is locked out from other transactions until the current transaction ends.
Sparse sequences guarantee uniqueness of the returned values, but they are not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, the sequence number is simply lost.
Both dense and sparse sequence numbers start from 1, unless specified otherwise with START WITH constant.
Sequence numbers are 8-byte values. Sequence values can be stored in BIGINT, BINARY, or INT data types.
▪BIGINT is recommended.
▪8-byte BINARY values can store a complete sequence number, but BINARY values are not always as convenient to work with as integer data types.
▪Sequence values stored in INT variables lose information because an 8-byte sequence number will not fit in a 4-byte integer.
When an 8-byte number sequence number is stored as a 4-byte integer (in a stored procedure or in an application program), the highest four bytes are omitted. This will cause loss of data when the sequence number is above 2^31 -1 (=2147483647).
The following example demonstrates this behavior:
CREATE SEQUENCE seq1; -- Set the sequence number to 2^31 - 1, -- then return that value and the "next" value (2^31). "CREATE PROCEDURE set_seq1_to_2G RETURNS (x INT, y INT) BEGIN DECLARE int1 INTEGER; int1 := 2147483647; EXEC SEQUENCE seq1 SET VALUE USING int1; EXEC SEQUENCE seq1 CURRENT INTO x; EXEC SEQUENCE seq1 NEXT INTO y; END";
COMMIT WORK; CALL set_seq1_to_2G();
The return values from the call are:
xy 2147483647 -2147483648
The value for x is correct, but the value for y is a negative number instead of the correct positive number.
The advantage of using a sequencer object instead of a separate table is that the sequencer object is specifically fine-tuned for fast execution and requires less overhead than normal update statements.
Sequence values can be incremented and used within SQL statements. The increment is defined with INCREMENT BY constant.
The following constructs can be also used in SQL:
sequence_name.CURRVAL sequence_name.NEXTVAL
Select access rights are required to retrieve the current sequence value. Update access rights are required to allocate new sequence values. These access rights are granted and revoked in the same way as table access rights.
Sequences in stored procedures
Sequences can also be used inside stored procedures.
Stored procedure statement
Usage
EXEC SEQUENCE sequence_name.CURRENT INTO variable
Retrieves a new sequence value
EXEC SEQUENCE sequence_name.NEXT INTO variable
Retrieves the current sequence value
EXEC SEQUENCE sequence_name SET VALUE USING variable
Sets sequence values
Alternatively, you can use the SET SEQUENCE <name>VALUE <bigint value> statement.
Examples
CREATE DENSE SEQUENCE SEQ1; INSERT INTO ORDER (id) VALUES (SEQ1.NEXTVAL); INSERT INTO ORDER (id) VALUES (SEQ1.NEXTVAL); "CREATE PROCEDURE get_my_seq RETURNS (val INTEGER) BEGIN EXEC SEQUENCE my_sequence.NEXT INTO (val); END";