Skip to content

Archive for

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

Netezza – Max object id

Maximum number of objectid’s accepted by Netezza is around 2.14 Billiion in V7 (around 1 Billion in V6). If your system start using OBJID greater than 1 Billion (in V7) then you will never be able to downgrade to NPS v6. Point to note here is that when ever you create any new object the object id increased and can never be reused even if you drop any objects. So object id allocation is always increasing.
Also note that objid count will increase by 10 when ever you create a table because internally 10 oids were reserved by NPS for table creation (look little strange but its true).

How to find the max object id (objid) for your system?
Run below query:
select now(), max(objid) from _t_object;

NOW MAX
— —
2013-01-17 12:49:33 180782574

You may se that the MAX value may some time give value lower than what you got some time back or before NPS restart. This is because the above query will give you the max object id for any object that still exists. If any object was created after this and then dropped, that id value will not be shown.

So, to avoid above issue, always create one temporary table and immediately after that run this query and then delete the temp table create. This will give you a most accurate objid that is being used by NPS.