Migration of all Databases with all users and grant & privileges of IBM Netezza from one to Another server

 #############################################################################

Migration of all Databases with all users and grant & privileges of IBM Netezza from one  to Another server

##############################################################################

Please Use Below steps to migrate from one server to another server.

1. Take DDL of All Users.

nz_ddl_user > nz_ddl_user.sql

2. Take DDL of All Groups one by one Important 

nz_ddl_group GroupName >  nz_ddl_group_GroupName .sql

3. Take DDL of Grant given to groups one by one Important 

nz_ddl_grant_group DBname > nz_ddl_grant_group_DBname .sql

4. Take DDL of Grants to given to all user.

nz_ddl_grant_user > nz_ddl_grant_user.sql

5. Move all SQL file to other server and import it.

From OS run it.

nzsql < nz_ddl_user.sql

connect to each database and run

nzsql -d dbname -f  nz_ddl_group_GroupName .sql

connect to each database and run

nzsql -d dbname -f nz_ddl_grant_group_DBname .sql

nzsql <  nz_ddl_grant_user.sql

6. Now Migrate All Databases using nz_migrate, below are the few examples.

nz_migrate -shost Source-Host -thost Target-Host -sdb NZDB -tdb NZDB -suser admin -spassword Source_passwd  -tuser admin -tpassword targetpasswd -format binary  -genStats Express  -CreateTargetTable YES -CreateTargetDatabase yes -threads 10 -Status

Note :- If you want to rename database during nz_migrate

nz_migrate -shost Source-Host -thost Target-Host -sdb POMSDB-tdb N_POMSDB -suser admin -spassword Source_passwd  -tuser admin -tpassword targetpasswd -format binary  -genStats Express  -CreateTargetTable YES -CreateTargetDatabase yes -threads 10 -Status

Generate DDL of Users, Groups, Tables, Views & Functions and Procedure in IBM Netezza

################################################################################

  Generate DDL of Users, Groups, Tables, Views & Functions and Procedure in IBM Netezza

################################################################################


Here is the OS  command to Generate DDL of Users, Groups, Tables, Views & Functions and Procedure in IBM Netezza.


DDL for all user run this from OS.

nz_ddl_user > nz_ddl_user.sql  ## output will be in file nz_ddl_user.sql

DDL for single user  MJAISWAL run  below command to generate.

nz_ddl_user MJAISWAL > nz_ddl_user_MJAISWAL.sql 

## output will be in file nz_ddl_user_MJAISWAL.sql

DDL all groups 

nz_ddl_group > nz_ddl_group .sql

DDL for all grant given to groups.

nz_ddl_grant_group > nz_ddl_grant_group.sql

To Migrate all MetaData of IBM Netezza from to another server, Please use below steps.


Move all Genrated SQL  file to other server and import it in new server.


nzsql < nz_ddl_user.sql 

nzsql <  nz_ddl_group .sql

nzsql < nz_ddl_grant_group.sql



There are below command to generate DDL for all other purpose .

nz_ddl_user 
nz_ddl_group 
nz_ddl_grant_group 
nz_ddl_grant_user   
nz_ddl_mview  
nz_ddl_schema   
nz_ddl_view
nz_ddl_ext_table   
nz_ddl_group    
nz_ddl_table       
nz_ddl_function  
nz_ddl_owner 
nz_ddl_database  
nz_ddl_grant_group 
nz_ddl_procedure  
nz_ddl_synonym 

Fail-over and Switch-over from primary to standby on IBM Netezza System

###########################################################################
Fail-over and Switch-over  from primary to standby on IBM Netezza System 
############################################################################

1. Login to  active Node.

Please run the following command as root on the active host to monitor resources status.    
    Make sure all disk and other hardware is ok,
                                                                  
crm_resource -U -r nps    
crm_mon                                            
                                                                       
2. Then run the following to migrate nps on the standby node host to swith all NPS services to standby host.

Before doing this ask all user to use VIP address to connect Netezza system, Otherwise they would be disconnect.

              

$/nzlocal/scripts/heartbeat_admin.sh --migrate

3. Wait for few minute and open another session( new active server)  and run below command several time to check status.

nzstate 
nzstats
crm_mon
crm_resource -U -r nps 


Output of nzstate should be online.


Check log file 
tail -f  /nz/kit/log/sysmgr/sysmgr.log

LDAP setup in in IBM Netezza System MAKO

These are the steps for other LDAP setup in netezza 

Install the correct rpm based on your rhel version 6.5 with root user openldap-clients-x.x.xx-xx.elx.x86_64.rpm  


First make sure ldapsearch works from the nz host, Example as I did in edw-ntz-0a1-lq

[root@XXXX ~]# ldapsearch -h 100.172.83.15 -b "DC=domestic,DC=gutu,DC=com" "(&(memberOf=CN=GCEDW-NETEZZA-GRP,OU=Netezza,OU=Security Groups - Global,OU=Corporate,DC=domestic,DC=guitarcenter,DC=com))" -D "CN=svcNTZ-LDAPS-prod,OU=LDAPRefAccounts,OU=Service Accounts,OU=Special,DC=domestic,DC=gutu,DC=com" -w 'password' |grep -i samaccountname

If that is not working there could be a network issue that needs to be investigated, is not worth to configure ldap if we cannot reach the ldap server from the netezza hosts or contact with your window admin for LDAP or active directory.

Once the ldapsearch is confirmed to work:

su - nz
nzsql as admin
Run following command to setup ldap, I'm assuming the LDAP server and settings are the same for all NZ machines

SYSTEM.ADMIN(ADMIN)=>SET AUTHENTICATION LDAP BASE

'DC=domestic,DC=gutu,DC=com' SERVER '100.172.83.15'

BINDDN 'CN=svcNTZ-LDAPS-prod,OU=LDAPRefAccounts,OU=Service Accounts,OU=Special,DC=domestic,DC=guitarcenter,DC=com' PORT '389'

BINDPW 'password' SCOPE SUB ATTRNAME 'sAMAccountName' NAMECASE lowercase SSL 'off';

SYSTEM.ADMIN(ADMIN)=>\q

Restart NPS to apply changes

$nzstop
$nzstart &

Once NPS is back online check the configuration files
As root
vi /etc/pam_ldap.conf

# comment out any uri line pointing to 127.0.0.1
#uri ldap://127.0.0.1/
####Make sure
scope SUB

IMP :- Compare files with this expected output from the configured NZ box

[root@edw-ntz-0a1-lq ~]# egrep -v '^(#|$)' /etc/pam_ldap.conf
host 10.192.83.13
base DC=domestic,DC=gutu,DC=com
binddn CN=svcNTZ-LDAPS-prod,OU=LDAPRefAccounts,OU=Service Accounts,OU=Special,DC=domestic,DC=gutu,DC=com
bindpw password
port 389
scope SUB
pam_login_attribute sAMAccountName
ssl off
ssl no
tls_cacertdir /etc/openldap/cacerts
pam_password md5

[root@edw-ntz-0a1-lq ~]# egrep -v '^(#|$)' /nz/data/config/pam_ldap.conf
host 10.192.83.13
base DC=domestic,DC=gutu,DC=com
binddn CN=svcNTZ-LDAPS-prod,OU=LDAPRefAccounts,OU=Service Accounts,OU=Special,DC=domestic,DC=gutu,DC=com
bindpw Password
port 389
scope SUB
pam_login_attribute sAMAccountName
ssl off
ssl no
tls_cacertdir /etc/openldap/cacerts
pam_password md5


If there is any difference betewen /nz/data/config/pam_ldap.conf and /etc/pam_ldap.conf just cp /etc/pam_ldap.conf /nz/data/config/pam_ldap.conf to make sure the files are consistent

Once the files are in place we just test

To connect to DB you must use always pw from ldap

unset NZ_PASSWORD
nzsql -d DGCRODS -u manish.jaiswal 

User in NZ can be without a password of different than ldapd but it has to match the username

If you need to troubleshoot add debug to /etc/pam.d/netezza_nps
#%PAM-1.0
auth      required     pam_ldap.so debug
account   required     pam_ldap.so
password  required     pam_ldap.so


TO Disable  LDAP login  for complete server on IBM netezza

Nzsql> SET AUTHENTICATION local;

 TO Disable  LDAP login for a single users

NZSQL> Alter user MJAISWAL auth local;

If you want to revert again to LDAP logiin

NZSQL> Alter user MJAISWAL AUTH DEFAULT;  -- ( IF LDAP is setup for full netezza server)

Setup time out for few user and number of session per users and group in IBM Netezza

############################################################################Setup time out  for few  user nad number of session per users  and group in IBM Netezza###########################################################################

For User 

SYSTEM(ADM_USR)=> \h alter user
Command:     ALTER USER
Description: Alter properties of a user
Syntax:
ALTER USER username
    [WITH]
     [ PASSWORD [ 'string' | NULL ] ]
     [ IN GROUP group  [, ... ] ]
     [ VALID UNTIL 'date' ]
     [ ROWSETLIMIT integer ]
     [ SESSIONTIMEOUT integer ]
     [ QUERYTIMEOUT integer ]
     [ DEFPRIORITY [ CRITICAL | HIGH | NORMAL | LOW | NONE ] ]
     [ MAXPRIORITY [ CRITICAL | HIGH | NORMAL | LOW | NONE ] ]
     [ IN RESOURCEGROUP resourcegroupname ]
     [ RESET ACCOUNT ]
     [ OWNER TO user_name ]
     [ RENAME TO new_user_name ]
     [ SECURITY LABEL '[level] : [category,]... : [cohort,]... ' ]
     [ AUDIT CATEGORY [ NONE | 'category,...' ] ]
     [ COLLECT HISTORY [ ON | OFF | DEFAULT ] ]
     [ CONCURRENT SESSIONS integer ]
     [ ACCESS TIME [ ALL | DEFAULT | ( access_time,... ) ]
     [ AUTH [ LOCAL | DEFAULT ] ]
     [ EXPIRE PASSWORD ]
access_time:
    DAY [ ALL | day,... ]
            [ START time-literal END time-literal,]... ]


EXAMPLE:-

ALTER USER MJAISWAL WITH SESSIONTIMEOUT 60 QUERYTIMEOUT 120 IN RESOURCEGROUP ACCOUNT CONCURRENT SESSION 5 AUTH LOCAL;


For Groups

SYSTEM(ADM_USR)=> \h alter group
Command:     ALTER GROUP
Description: Alter properties of a group
Syntax:
ALTER GROUP name [WITH]
     [ ROWSETLIMIT integer ]
     [ SESSIONTIMEOUT integer ]
     [ QUERYTIMEOUT integer ]
     [ DEFPRIORITY [ CRITICAL | HIGH | NORMAL | LOW | NONE ] ]
     [ MAXPRIORITY [ CRITICAL | HIGH | NORMAL | LOW | NONE ] ]
     [ RESOURCE MINIMUM resourcePercent ]
     [ RESOURCE MAXIMUM resourcePercent ]
     [ JOB MAXIMUM  integer ]
     [ SYSID gid ]
     [ DROP USER username [, ... ] ]
     [ [ADD] USER username [, ... ] ]
     [ OWNER TO user_name ]
     [ RENAME TO new_grp_name ]
     [ COLLECT HISTORY [ ON | OFF | DEFAULT ] ]
     [ CONCURRENT SESSIONS integer ]
     [ ACCESS TIME [ ALL | day,... ]
            [ [START time-literal END time-literal,]... ] ]

SYSTEM(ADM_USR)=> \q

EXAMPLE:-

ALTER GROUP  ACCOUNT  SESSIONTIMEOUT 60 QUERYTIMEOUT 120  CONCURRENT SESSION 5

RESOURCE MINIMUM 10 RESOURCE MAXIMUM 30;
SYSTEM.ADMIN(ADMIN)=> ALTER GROUP Business_Users JOB MAXIMUM 100;
ALTER GROUP

SYSTEM.ADMIN(ADMIN)=> ALTER GROUP EDW_Users  CONCURRENT SESSIONS 100;

 This doc can be used to setup resource utilization 

Check Resource utilization on IBM Netezza system

#################################################################################
Check Resource utilization on IBM Netezza system
#################################################################################


[nz@XXXXXXX]$ nzsqa schedqueues -sys
SCHED TIME:  2016-03-08 11:46:27.801 PST

SCHEDULER POLICY:
    Gatekeeper     Disabled  max slots = 2048
    GRA             Enabled  max slots =   48    SQB slots =  10
    Snippet         Enabled  max slots =   40    SQB slots =   6
    SQB (< 2s)      Enabled
    SN Limiting     Enabled   baseline =    8
    GRALoad         Enabled
    Null I/O -> Ceilings:on Available:on Expected:on SNLimit:on
##### Defined utilization  will be here 
KNOWN GROUPS:
    GROUP --OID=    4901  RSG Min  20%  Max 100%  JobMax  0 (off)     "PUBLIC"
    GROUP --OID=    4900  RSG Min 100%  Max 100%  JobMax  0 (off)     "ADMIN"
    GROUP --OID=170526524  RSG Min  10%  Max 100%  JobMax  0 (off)     "ADM_GRP"
    GROUP --OID=144910001  RSG Min  50%  Max 100%  JobMax  0 (off)     "RSG_PERFTEST"

GATEKEEPER:
    Gatekeeper 0 [normal], up to 0 sec, queued 0, running 0 (max 2048)

GRA SCHED:
##### Current utilization will be here 
SNIPPET SCHED:     Avail   Avail
                   Short   Long  InUse    Max  Delta Reserved
    Host Memory    16384  15872      0  16384      0    512
    Blade Memory   18499  16499      0  18499      0   2000
    Channels         100    100      0    100
    Distributes       50     49      0     50      0      1
    Spu2Hosts        100    100      0    100      0      0
    Broadcasts       100    100      0    100      0      0

###########checck Running process utilization 
[nz@XXXX]$ nz_responders

Cur Time  Plan #  Snippet   Time S/P  State  Busy Dataslices ...       SQL                                      Username/Database
======== ======= ========= ========= ======= ==== ==================== ======================================== ====================
11:46:45
11:46:55
11:47:05
11:47:15

Debug Hung Backup and Restore in IBM Netezza/ Hung NZBACKUP and NZRESTORE IBM Netezza

########################################################################
Here is the step by step of handling hung session of backup and restore in IBM  netezza .
Hung /stuck NZBACKUP and NZRESTORE
###############################################################################

1. Check status of backup

[nz@sherlock-host-1 ~]$nzrestore -history -db PMFIDW |grep "2016-03-08"
PMFIDW     PMFIDW    20160206210001 32    INCR:REST COMPLETED Yes       2016-03-08 05:36:41 restoresvr.28184.2016-03-08.log
2. checked still restore is running 
[nz@sherlock-host-1 ~]$ ps -ef|grep nzrestore
nz       27981 31708  0 10:54 pts/1    00:00:00 grep nzrestore
nz       28173 28170  0 05:36 ?        00:00:00 /nz/kit/bin/nzrestore -db pmfidw -sourcedb "PMFIDW" -connector netbackup -connectorArgs DATASTORE_SERVER=edp-nbu-101-ap -npshost 10.192.204.35 -increment REST -lockdb T

3. Check If there is any session of bnr in Type and i found it.
[nz@sherlock-host-1 ~]$ nzsession

ID      Type      User        Start Time              PID   Database  State  Priority Name Client IP      Client PID Command
------- --------- ----------- ----------------------- ----- --------- ------ ------------- -------------- ---------- ------------------------
5545200 sql-oledb MFIDSS      08-Mar-16, 02:21:04 PST 32265 PMFIDW    active normal         10.192.136.43       2552 select * from f_order_li
5545372 sql-oledb MFIDSS      08-Mar-16, 02:26:00 PST 32666 PMFIDW    active normal         10.192.136.43       2552 select * from f_order_li
5545440 sql-oledb MFIDSS      08-Mar-16, 02:28:35 PST 21821 PMFIDW    active normal         10.192.136.43       2552 select * from f_order_li
5545457 sql-oledb MFIDSS      08-Mar-16, 02:29:25 PST 24165 PMFIDW    active normal         10.192.136.43       2552 select * from f_order_li
5552655 bnr       ADM_USR     08-Mar-16, 05:36:02 PST 28184 PMFIDW    active normal         10.192.150.20          0

4. check log file for session and process id of restore.

[nz@sherlock-host-1 ~]$ vi /nz/kit/log/restoresvr/restoresvr.28184.2016-03-08.log
[nz@sherlock-host-1 postgres]$ cat /nz/kit/log/restoresvr/restoresvr.28184.2016-03-08.log|grep "Postgres client pid"
2016-03-08 05:36:41.368352 PST Info: Postgres client pid: 32398, session 5552669

5. checked log file restore is still not committed.

[nz@sherlock-host-1 ~]$ tail -f /nz/kit/log/backupsvr/restoresvr.28184.2016-03-08.log
tail: cannot open `/nz/kit/log/backupsvr/restoresvr.28184.2016-03-08.log' for reading: No such file or directory
tail: no files remaining
[nz@sherlock-host-1 ~]$ tail -f /nz/kit/log/restoresvr/restoresvr.28184.2016-03-08.log
2016-03-08 05:42:59.634337 PST Info: Restoring ATL_ATG_PROMOTION
2016-03-08 05:43:00.265610 PST Info: Restoring ATL_ATG_PROMOTION_REJECTED
2016-03-08 05:43:00.731310 PST Info: Restoring L_PROMOTION
2016-03-08 05:43:01.149427 PST Info: Restoring F_ORDER_LIFECYCLE_EXP_DT
2016-03-08 05:43:01.266068 PST Info: Restoring ATF_OMS_ORDER_PRICE_DTL
2016-03-08 05:43:03.160407 PST Info: Restoring F_ORDER_PAYMENT
2016-03-08 05:43:05.662640 PST Info: Restoring F_ORDER_MISC_DISCOUNT_DTL
2016-03-08 05:43:06.262526 PST Info: Restoring F_OMS_ORDER_DTL
2016-03-08 05:43:12.022260 PST Info: Restoring ATF_OMS_ORDER_RMK
2016-03-08 05:43:14.350368 PST Info: Restoring views, users, groups, permissions
#################waiting for last line of success############################

6. check session of session id 5552669(restore session but not found)
[nz@sherlock-host-1 ~]$ nzsession|grep 5552669
##################### no result###############


7. check processes for process id of restore ( found its waiting on create view)

[nz@sherlock-host-1 ~]$ ps -efw --forest | grep ^nz | grep 32398
nz       32701 31708  0 11:13 pts/1    00:00:00  |                       \_ grep 32398
nz       32398 29810  0 05:36 ?        00:00:04  |   \_ postgres: ADMIN PMFIDW 127.0.0.1 CREATE VIEWwaiting
nz       32413 30302  0 05:36 ?        00:00:15  |   \_ dbos event C1047,U1277861,S5552669,[32398]

8. check pg.log for process id 32398

[nz@sherlock-host-1 ~]$ cd /nz/kit/log/postgres/
[nz@sherlock-host-1 postgres]$ vi pg.log
[nz@sherlock-host-1 postgres]$ grep "\[32398\]" pg.log > /tmp/32398.txt
[nz@sherlock-host-1 postgres]$ vi /tmp/32398.txt
##########checked log of this proc id.( its was at creating of views)########

9. check if there is any locks and found locks on the same views and belonging tables.

[nz@sherlock-host-1 postgres]$ nz_show_locks

User objects that currently have lock(s) associated with them which was used in view creations

 Database Name |        Object Name        | Object Type | Object ID
---------------+---------------------------+-------------+-----------
 PMFIDW        | F_ORDER_LIFECYCLE         | (table)     | 235686790
 PMFIDW        | F_ORDER_LIFECYCLE_ARCHIVE | (table)     | 235687516
 PMFIDW        | F_ORDER_LIFECYCLE_HT      | (view)      | 235700073
(3 rows)


For specific details, invoke
     nz_show_locks <database> <object>  [-schema <schema>]

10. check the session belong to those tables and views holding locks. and found four first session.

[nz@sherlock-host-1 postgres]$ nzsession

ID      Type      User        Start Time              PID   Database State  Priority Name Client IP      Client PID Command
------- --------- ----------- ----------------------- ----- -------- ------ ------------- -------------- ---------- ------------------------
5545200 sql-oledb MFIDSS      08-Mar-16, 02:21:04 PST 32265 PMFIDW   active normal         10.192.136.43       2552 select * from f_order_li
5545372 sql-oledb MFIDSS      08-Mar-16, 02:26:00 PST 32666 PMFIDW   active normal         10.192.136.43       2552 select * from f_order_li
5545440 sql-oledb MFIDSS      08-Mar-16, 02:28:35 PST 21821 PMFIDW   active normal         10.192.136.43       2552 select * from f_order_li
5545457 sql-oledb MFIDSS      08-Mar-16, 02:29:25 PST 24165 PMFIDW   active normal         10.192.136.43       2552 select * from f_order_li
5552655 bnr       ADM_USR     08-Mar-16, 05:36:02 PST 28184 PMFIDW   active normal         10.192.150.20          0
5563898 sql-odbc  BHAYDEN     08-Mar-16, 09:16:25 PST 28550 GCRTST   idle   normal          10.160.76.12       6636 select * from ( SELECT S
5564657 sql-odbc  QMACDWDS    08-Mar-16, 09:41:03 PST  6738 QMACDW   idle   normal        10.192.148.127       8124 select distinct DAX_GLAC

11. Kill those session of select statement.( after confirmation of users or App Team)

[nz@sherlock-host-1 postgres]$ nzsession abort -id 5545200
Are you sure you want to abort the session (y|n)? [n] y
You have new mail in /var/spool/mail/nz
[nz@sherlock-host-1 postgres]$ nzsession abort -id 5545372
Are you sure you want to abort the session (y|n)? [n] y
[nz@sherlock-host-1 postgres]$ nzsession abort -id 5545440
Are you sure you want to abort the session (y|n)? [n] y
[nz@sherlock-host-1 postgres]$ nzsession abort -id 5545457
Are you sure you want to abort the session (y|n)? [n] y

11. check session of them again and found no session.

[nz@sherlock-host-1 postgres]$ nzsession

ID      Type     User        Start Time              PID   Database State  Priority Name Client IP      Client PID Command
------- -------- ----------- ----------------------- ----- -------- ------ ------------- -------------- ---------- ------------------------
5563898 sql-odbc BHAYDEN     08-Mar-16, 09:16:25 PST 28550 GCRTST   idle   normal          10.160.76.12       6636 select * from PMFIDW..R_
5564657 sql-odbc QMACDWDS    08-Mar-16, 09:41:03 PST  6738 QMACDW   idle   normal        10.192.148.127       8124 select distinct DAX_GLAC
5564809 sql-odbc BWYATT      08-Mar-16, 09:46:34 PST  5458 GCRTST   idle   normal          10.160.72.94       5656 SELECT DATE_TIME_SCRAPED
5564844 sql-odbc AARAKELYAN  08-Mar-16, 09:48:17 PST 11903 GCRTST   idle   normal          10.160.76.27       2892 select identifier_case,
5564854 sql-odbc AARAKELYAN  08-Mar-16, 09:48:26 PST 11990 GCRTST   idle   normal          10.160.76.27       2892 Select SKU from threesix
5565295 sql-odbc QMACDWDS    08-Mar-16, 09:59:51 PST  5262 QMACDW   idle   normal         10.164.100.82       8124 SELECT * FROM ATL_ORGANI

12. check log file hopefully restore get done.

[nz@sherlock-host-1 postgres]$ vi /nz/kit/log/restoresvr/restoresvr.28184.2016-03-08.log


#### Its Completed after waiting for last Six houres :)  Thanks God.   !!!
#### This Document was prepared with Parul Bhatt (IBM)