Netezza Sequence - Creation and retrieval - learn Reading Time: 5 minutes Netezza Sequnces

A Netezza sequence can provide unique values when the getnext value method. You can use the sequence to generate unique numbers that can be used as surrogate key values for primary key columns.

Property	Description	Additional Information
Physical Datatype	Specifies the data type	Default data type is bigint.
Owner	Specifies the owner of the sequence
Schema	Specifies the schema to which the sequence belongs
Starting Value	Specifies the starting value of either:	Default value is:
	an ascending sequence; at a value greater than its minimum	Ascending sequence: Minimum value of the sequence
	a descending sequence; at a value less than its maximum	Descending sequence: Maximum value of the sequence
Increment By	Specifies the value by which the sequence should increment.	The value; always an integer; can be positive or negative. However, it cannot be 0.
Specify Minimum Value	Specifies the minimum value of the sequence
Specify Maximum Value	Specifies the maximum value of the sequence
Cycle Values	Specifies whether the sequence continues to generate values after it reaches its maximum or minimum value	The default value is False.

we will cover these topics: hide 1) Netezza Sequence Overview 2) Netezza Sequence and Privileges 3) Known problem with Netezza sequences 4) Syntax: 5) Sample Sequence 6) Sample to get next value in Sequence 7) Alter Sequence 8) Where options can be: 9) Restriction on use of sequence 10) Dropping a Sequence 11) What to read next? 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 a Sequence

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

Retrieve the next value from the sequence

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. 301st “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.