So if you’ve used Netezza sequences you may have noticed they are all over the place, and far from sequential. This is just the nature of a share nothing massively parallel architecture. But if you want sequential sequences that automatically keep in sync with your table you can leverage the ability of Netezza to do table counts REALLY fast, and the row_number logic covered in ROWNUM alternative post. Here is an example
ROW_NUMBER() OVER ( ORDER BY A.CUSTOMER_SOME_NATURAL_KEY) + CUST_MAXSKEY.MAXSKEY AS CUSTOMER_SKEY,
WHERE …) A
(SELECT NVL(MAX(CUSTOMER_SKEY),0) MAXSKEY FROM CUSTOMER ) CUST_MAXSKEY
The ordering clause is only needed to give you some control over what order the sequences are generated. You just cross product to a SQL getting the max value, then used the row_number method and add that to that max value. I’ve used this on tables with millions rows. Larger tables should probably stick to the sequence object. The great thing about this method is you don’t have to worry about ever getting your sequence out of sync with the table.