Skip to content

Archive for

What authentication is my Netezza server using?

To check the type of authentication being used by Netezza database server, just run below query in system database. Output of AUTH_VALUE column will be either LOCAL or LDAP.

select * from _v_authentication

AUTH_OPTION AUTH_VALUE
=========== ==========
AUTHENTICATION METHOD LDAP

OR

select * from _v_authentication_settings where AUTH_OPTION = ‘AUTHENTICATION METHOD’;

AUTH_OPTION AUTH_VALUE
=========== ==========
AUTHENTICATION METHOD LDAP

And to check the complete authentication setting for LDAP/LOCAL, just run below query in system database:

select * from _v_authentication_settings

OR

SYSTEM(ADMIN)=> show authentication;
AUTH_OPTION | AUTH_VALUE
———————–+————
AUTHENTICATION METHOD | LDAP

Can also run below for more details:
SYSTEM(ADMIN)=> show authentication all;
AUTH_OPTION | AUTH_VALUE
————————+—————————————————————————
AUTHENTICATION METHOD | LDAP
AUTHMTHD LDAP ATTRNAME | sAMAccountName
AUTHMTHD LDAP BASE | dc=corp,dc=****,dc=com
AUTHMTHD LDAP BINDDN | ****************
AUTHMTHD LDAP PORT | 389
AUTHMTHD LDAP SCOPE | sub
AUTHMTHD LDAP SERVER | *.com
AUTHMTHD LDAP SSL | off
AUTHMTHD LDAP VERSION | 3

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>;