Netezza Sequence – Creation and retrieval

Netezza Sequence Overview

A sequence value is an integer that you can use wherever you would use numeric values. Netezza supports user sequences for the four integer types: byteint, smallint, integer, and bigint. You can even create a sequence with an initial value, an increment, a minimum and a maximum value. You can even specify what sequence should do when end point is reached.

When you generate the sequence number, the system increments the sequence independently of the transaction commit or rollback. Therefore, rollback doesn’t return the value back to sequence

Netezza Sequence and Privileges

The privileges to create, alter, drop, select, and update sequences are as follows:

• The admin user has all privileges on all user sequences. There is no need to grant any privileges to the admin user.
• The owner of the database has all privileges on all user sequences in that database.

There is no need to grant any privileges to the owner.

Known problem with Netezza sequences

Sequence always provide a unique value; be advised that, you are not guaranteed that sequence numbers will be predictable, monotonically increasing values. Unexpected sequence numbers occur as a result of the Netezza topology.

You cannot access the sequence from other databases. i.e. Sequence doesn’t provide cross-database access.

Syntax:

To create sequences use CREATE SEQUENCE statement along with options in any order.

CREATE SEQUENCE <sequence name> [options]

You can use following options while creating sequences:

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

 Meanings and values of options:

• The default minvalue is no minvalue, which is 1.
• The default maxvalue is no maxvalue and is the largest value by particular 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 has to be within the range of the minvalue and maxvalue.
• The default increment is 1.
• By default, sequences do not cycle

Sample Sequence

CREATE SEQUENCE IDNOseq as integer
START WITH 1 increment by 1
minvalue 1 maxvalue 100000000 no cycle

select NEXT VALUE FOR IDNOseq

Getting Values from Sequences

After you have established a Netezza sequence, you can use the NEXT VALUE FOR and the NEXT <integer expression> VALUES FOR statement to retrieve sequence values.

• The NEXT VALUE FOR statement returns the next available value.
• The NEXT <integer expression> VALUES FOR statement returns the first of a set of contiguous values for the sequence.

You can include next value for sequence_name in the SELECT clause to get the value out of sequence created.

For instance, “SELECT next value for sequence1” will return the sequence value from sequence. 301^st “next value for” will be an error.

Sample to get next value in Sequence

select case 
          when id is NULL then cast((next value for id_seq) as character varying(10)) 
          else id 
       end id
from table1;

Alter Sequence

You can alter a user sequence by resetting any sequence options, including the name and owner of the sequence.

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

ALTER SEQUENCE <sequence name> [options]

Where options can be:

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

For instance, resets increment value, use alter statement as below;

 ALTER SEQUENCE sequence1 INCREMENT BY 2;

If you alter a sequence while a sequence is in use by a running query, the system waits for the running query’s transaction to complete before altering the sequence.

Restriction on use of sequence

You cannot use NEXT VALUE in the following statements:

• CASE expressions
• WHERE clauses
• ORDER BY clauses
• aggregate functions
• window functions
• grouped queries
• SELECT distinct

Dropping a Sequence

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

DROP SEQUENCE <sequence name>;

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.