Sequence – Issue with Netezza restart?
In Netezza, sequence can be declared as 8-, 16-, 32-, or 64-bit integers and the cache size is a function of the number of distinct values in one complete cycle of the sequence (so it depends on the declared minvalue, maxvalue, and increment of the sequence).
Netezza caches sequence numbers at each SPU and whenever Netezza state changes from online (i.e. changed from Online to anyother state) all the cached sequesnces were lost and next time when Netezza comes online, it will cache from the next available non-cached value. Same issue will be faced when any SPU goes offline. In this scenario only the cached values in that SPU is lost.
If you are using 64 bit integer then approx cached value per data slice (and not per SPU) is 100K and if you are on TF120 which has 920 data slices, which mean around 9200K values are cached each time. And if your system have too many restarts than you may run out of sequence numbers/hit max value very fast.
How to manage/avoid losing sequence numbers?
To avoid losing too many sequence number you can setup a process (to run when Netezza comes online) to alter sequence number to start from current max value used in a table.
Just pull the max value from table which is using the sequence and then alter your sequence to start at that value.
select max(Sequence_Num) from TableName;
alter sequence Sequence_Name restart with <Sequence_Num + 1>;