Skip to content

Sequence Caching in Netezza – Undocumented Feature

Netezza have an undocumented feature (another undocumented Netezza stuff) that allows user to set the cache size instead of using the default which is around 100k. You should be thinking of using this feature if you heavily use sequences and also you perform a regular NPS restarts (like weekly vacuum, etc). In this scenario the sequence caching will help in reducing the wasted number of sequence keys thus lowering the risk of running out of max values.

Here are some points to be considered before using sequence caching:
1) The functionality has existed in NPS since v4.6.
2) It has not been exposed and there are no plans to expose it to a wider audience. The reason is that if the cache size is used sub-optimally, it would result in performance issues.
3) Use sequence cache size that is large enough that it doesn’t get exhausted frequently, but small enough that there ate no huge gaps.
4) IBM Netezza Support will not be supporting this feature as its not documented. Your lab advocate will be your point of contact for any issues with this functionality.
How to enable Cache size:
Enable cache size for the session in nzsql by issuing below statements.

set enable_sequence_cachesize=true;

If not enabled, you will see the error
ERROR: Cannot set sequence cache size

You can use it in Create or alter sequence command:

CREATE SEQUENCE… [CACHE #]
ALTER SEQUENCE… [CACHE #]

Detailed Example:

TEST.DB(ADMIN)=> show enable_sequence_cachesize;
NOTICE: ENABLE_SEQUENCE_CACHESIZE is off
SHOW VARIABLE
TEST.DB(ADMIN)=> CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1 CACHE 10;
ERROR: Cannot set sequence cache size
TEST.DB(ADMIN)=> set enable_sequence_cachesize=true;
SET VARIABLE
TEST.DB(ADMIN)=> CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE
TEST.DB(ADMIN)=> create table tab(i int);
CREATE TABLE
TEST.DB(ADMIN)=> insert into tab values(10);
INSERT 0 1
TEST.DB(ADMIN)=> insert into tab values(20);
INSERT 0 1
TEST.DB(ADMIN)=> insert into tab values(30);
INSERT 0 1
TEST.DB(ADMIN)=> select i, next value for test_sequence from tab;
I | NEXTVAL
—+———
10 | 1
20| 2
30 | 3
(3 rows)
TEST.DB(ADMIN)=> \q