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:

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:

 

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.

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:

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.



Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

Connect with me:

My Resources:

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite