Srquences in Netezza

A sequence is a named object in a database or schema that supports the get next value method. By using sequences, you can generate unique numbers that can be used as surrogate key values for primary key values, where the identification of rows within a table would involve a large, compound primary key, or for other purposes.

A sequence value is an integer that you can use wherever you would use numeric values. IBM® Netezza® supports user sequences for the four integer types: byteint, smallint, integer, and bigint.

You create a sequence with an initial value, an increment, a minimum, and a maximum value. You also specify whether the sequence cycles, which determines whether the sequence starts over when the endpoint is reached.

Create a sequence

Use the CREATE SEQUENCE statement to create a sequence, which is an object from which users can generate unique numbers.

To create a sequence, use the CREATE SEQUENCE statement and specify the options in any order.

CREATE SEQUENCE <sequence name> [as <data type> <options>]

Where the options are the following:

START WITH <start value>
INCREMENT BY <increment>
no minvalue | minvalue <minimum value>
no maxvalue | maxvalue <maximum value>
cycle | no cycle

The options have the following parameters:

• The default minvalue is no minvalue, which is defined to be 1.
• The default maxvalue is no maxvalue and is the largest value by data type that the sequence can hold.
• The default start value is the minvalue for an increasing sequence, and the maxvalue for a decreasing sequence. The startvalue must be within the range of the minvalue and maxvalue.
• The default increment is 1.
• By default, sequences do not cycle.

When a user generates a sequence number, the system increments the sequence independently of the transaction committing or rolling back. Therefore, a rollback does not return the value to the sequence object. If two users concurrently increment the same sequence, the sequence numbers each user acquires might have gaps because the sequence numbers are being generated by the other user.

Sequences also can have gaps because the IBM® Netezza® caches sequence values on the host and SPUs for efficient operation.

Alter a sequence

You can alter a user sequence by resetting any sequence option, including the name and owner of the sequence. To change the starting value, use the RESTART WITH option.

To alter a sequence, use the ALTER SEQUENCE statement and specify the options in any order.

ALTER SEQUENCE <sequence name> <options>

Where the options are the following:

OWNER to <new owner>
RENAME TO <new sequence name>
RESTART WITH <start value>
INCREMENT BY <increment>
no minvalue | minvalue <minimum value>
no maxvalue | maxvalue <maximum value>
cycle | no cycle

Drop a sequence

Although you can drop a sequence, remember that system locking occurs if you attempt to drop a sequence that is in use by a running query. In addition, if the sequence is referenced by other metadata, subsequent use of that metadata results in an error, for example if the sequence is referenced in a view definition.

To drop a sequence, use the DROP SEQUENCE statement and specify the sequence name.

DROP SEQUENCE <sequence name>