CREATE SEQUENCE

CREATE SEQUENCE — define a new sequence generator

Syntax

CREATE SEQUENCE [ schema.]seqname [ sequence_option [,...] ]

where sequence_option can be one of the following:

START WITH value
QUANTUM SIZE size

Description

CREATE SEQUENCE creates a new sequence number generator. The sequence database object is owned by the user issuing the CREATE SEQUENCE command. The user can then issue the GRANT command to allow other users SELECT access to the sequence. See the GRANT command and also, Example 3: Successfully delegate an INSERT permission on a table with a sequence to a non-admin user.

Note that the user needs to GRANT SELECT rights to a table's sequence(s) in order for a non-admin user to be able to insert into that table.

The sequence number generator assigns integer values, by default, starting with the value one (1). The START WITH sequence_option can be used to provide a different starting integer value for the sequence. This starting integer value is referred to as the floor value for the sequence. A sequence will always generate a unique integer value that is greater than the floor value for the sequence. However, these integer values are not necessarily generated in sequential, ascending order. See below how sequences are managed by Transaction Engine(s).

The sequence floor value is incremented each time a unique integer value is generated by the sequence. This can occur in one of two ways:

How sequence number generators are managed by one or more transaction engines (TE)

The sequence number generator will reserve a range of integer values for each TE that requests integer values from a sequence. This range of integer values is reserved, by default, in blocks of one hundred (100) values. The QUANTUM SIZE sequence_option can be used to change this block size. Once all of the integer values that have been reserved for that TE are assigned by the sequence, the next range of integer values is reserved for that TE. For example, suppose TE1 and TE2 both use the same sequence. The sequence reserves integer values 1-100 for TE1 and reserves integer values 101-200 for TE2. After the sequence assigns all of the integer values reserved for TE1, the next block that the sequence number generator will reserve for TE1 might contain integer values 201-300. If a client connects to the database via TE1 and selects the NEXT VALUE of the sequence, the integer value 1 is assigned. If another connection to the database is made via TE2 and the NEXT VALUE of the sequence is selected, the integer value 101 would be assigned, and so forth. As each TE returns unique integer values, the floor value for that sequence for that TE is incremented.

The sequence number generator maintains an integer value that is greater than any integer value that has been reserved for all TEs. This integer value is referred to as the ceiling value for the sequence. The ceiling value for a sequence is always incremented by one thousand (1000). This incremental size cannot be changed. Therefore, the initial ceiling value of a new sequence, by default, is 1000. Once a value that is equal to or greater than 1000 has been reserved for a TE, the ceiling value for the sequence is incremented to 2000. The sequence ceiling value will never decrease. Altering a sequence can only change the starting value of a sequence to a value that is greater than the sequence ceiling value (see ALTER SEQUENCE).

Parameters

Examples