How can I find out when GENERATE STATISTICS was last run?

In order to use this solution, you must be actively collecting query history.

1) Find the name of the current query history database:

nzsql -x -c 'show history configuration' | grep CONFIG_DBNAME
CONFIG_DBNAME | HISTDB 

In the above example it is HISTDB. If this does not return anything, then you do not have query history enabled.

2) Create a file with the following sql statement:

select a.username as USERID , b.dbname as DATABASE ,
substring(querytext,1,60) as SQL , submittime as SUBMITTIME,
finishtime-submittime as RUNTIME
from "$hist_query_prolog_1" a, "$hist_session_prolog_1" b, "$hist_query_epilog_1" c
where a.npsid = b.npsid and a.npsinstanceid = b.npsinstanceid and a.sessionid = b.sessionid
and a.npsid = c.npsid and a.npsinstanceid = c.npsinstanceid
and a.opid = c.opid and a.sessionid = c.sessionid
and upper(querytext) like '%GENERATE STATISTICS%'
and submittime > '2012-12-01' --- say when you want to start searching from id 2012-12-01
and upper(dbname) = 'TESTDB' --- specify the database name in uppercase

3) Execute the sql file against the query history database:

nzsql -db HISTDB -f <sql file> 

Example:

$ nzsql histdb -f hist_query.sql
USERID | DATABASE | SQL | SUBMITTIME | RUNTIME
------+--------+-----------------------------------+----------------------------+-----------------
ADMIN | TESTDB | GENERATE STATISTICS ON "CUSTOMER" | 2014-10-24 00:06:34.440963 | 00:00:01.210749

Note that the time will be specified in GMT and not local time.

How does Privilege Precedence work on IBM PureData Systems for Analytics?

When customer grant a global privilege, as we will show on this example using nzbackup, the user will be able to backup all the databases from the NPS system.

As example we will create user bkpusr1 and add this user to the group bkp and later grant global backup privilege to bkp group:

SYSTEM.ADMIN(ADMIN)=> create userbkpusr1 with password 'password';
CREATE USER
SYSTEM.ADMIN(ADMIN)=> create groupbkp add user bkpusr1;
CREATE GROUP
SYSTEM.ADMIN(ADMIN)=> grant backup to group bkp;
GRANT 

Verify the privilege for group bkp:

SYSTEM.ADMIN(ADMIN)=> \dpG bkp
                                             Group object permissions for group 'BKP'
Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
GLOBAL        | GLOBAL      | GLOBAL      |                                     |                         X
(1 rows) 

After that we are able to backup all the databases with user id bkpusr1

nzbackup -db TESTDB -u bkpusr1  -pw password -dir  /nzscratch/bkp_dir/
Backup of database TESTDB to backupset 20141115222940 completed successfully. 

The problem will occur when we grant any one of the privileges below directly to the database.

SYSTEM.ADMIN(ADMIN)=> grant create sequence, create synonym, create table, create external table, create temp table, create AGGREGATE, create view, create materialized view, create procedure, create library, restore, unfence, vacuum IN "TESTDB".ALL to group bkp; 

Verify the actual privileges from group bkp, you will see that GLOBAL still there, but also you have granted direct on database TESTDB, in this case you will loose the backup on TESTDB as show example below:

SYSTEM.ADMIN(ADMIN)=> \dpG bkp
                                             Group object permissions for group 'BKP'
Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
TESTDB        | GLOBAL      | GLOBAL      |                                     |         X X X X X X X     X X       X X X X
GLOBAL        | GLOBAL      | GLOBAL      |                                     |                         X
(2 rows) 

In this case the global privilege of backup will failed for database TESTDB.

nzbackup -db TESTDB -u bkpusr1 -pw password -dir  /nzscratch/bkp_dir/
Error: login failed - you must have 'BACKUP' privileges to perform this action. 

In order to be able to use GLOBAL backup privilege we will have to revoke all the privileges that we grant direct to TESTDB database.

SYSTEM.ADMIN(ADMIN)=> revoke create sequence, create synonym, create table, create external table, create temp table, create AGGREGATE, create view, create materialized view, create procedure, create library, restore, unfence, vacuum IN "TESTDB".ALL from group bkp ; 

After that we will be able to run backups again.

Have in mind that regarding to Privilege Precedence: IBM® Netezza® uses the following order of precedence for permissions:
• Privileges granted on a particular object within a particular database and a particular schema, for systems that support multiple schemas
• Privileges granted on an object class within a particular database and a particular schema, for systems that support multiple schemas
• Privileges granted on a particular object within all schemas of a particular database
• Privileges granted on an object class within all schemas of a particular database
• Privileges granted on an object within the system database
• Privileges granted on an object class within the system database

FINDING THE PLAN FILE FROM ZIP FILE IN NETEZZA

In Short Explantion!

cd /nz/kit/log/planshist/current/

based on that TIMESTAMP you will be able to locate which tar file will contain the plan id that you are interested in collect.

syntax: tar -tvf zipfilename planid

tar -tvf 20151107103841.tgz 1335415

go to the directory location,you want keep the extracted plan file.

cd /nz/home/export/ABBAS

tar -xvzf /nz/kit/log/planshist/current/20160123103448.tgz 2341510

[nz@netezza201402-01 ABBAS]$ cd 2341510

[nz@netezza201402-01 2341510]$ ls -ltr

total 32

-rw-r--r-- 1 nz nz 26173 Jan 23 11:19 h2341510_500.cpp

-rw-r--r-- 1 nz nz  3050 Jan 23 11:19 2341510.pln

[nz@netezza201402-01 2341510]$ nz_plan  2341510.pln

***************************************************************

Let's use the example below:

I am interested on planid 4011 that it was already archived:

From dbos.log I will know when the plan id finish and based on that TIMESTAMP you will be able to locate which tar file will contain the plan id that you are interested in collect.

grep "planid 4011 " /nz/kit/log/dbos/dbos.2015-05-05.log
2015-05-05 06:50:07.803613 EDT Info: plan queued: planid 4011 tx 0x482fa cli 131 uid 4960 sid 26061 pid [20595] (run 0/0)
2015-05-05 06:50:07.807195 EDT Info: plan prep  : planid 4011 tx 0x482fa cli 131 uid 4960 sid 26061 pid [20595] (run 0/0)
2015-05-05 06:50:07.811578 EDT Info: plan in GRA: planid 4011 tx 0x482fa cli 131 uid 4960 sid 26061 pid [20595] (run 0/1)
2015-05-05 06:50:07.811643 EDT Info: plan start : planid 4011 tx 0x482fa cli 131 uid 4960 sid 26061 pid [20595] job new (run 0/1)
2015-05-05 06:50:07.860964 EDT Info: plan done  : planid 4011 tx 0x482fa cli 131 uid 4960 sid 26061 pid [20595] dur 0.073 queue 0.018 run 0.041 @ 92%  delay 0.000 res 0.015 (run 2/2)

ls -ltrh /nz/kit/log/planshist/current/
total 52M
-rw------- 1 nz nz  11M May  4 22:28 20150504142511.tgz
-rw------- 1 nz nz  11M May  5 06:47 20150504222822.tgz
-rw------- 1 nz nz  11M May  5 14:59 20150505064911.tgz
-rw------- 1 nz nz  11M May  5 23:13 20150505145906.tgz
-rw------- 1 nz nz 9.7M May  6 06:59 20150505231321.tgz


# As uzer nz go to archived plan files
cd /nz/kit/log/planshist/current/

# I will use the command to verify if my planid 4011 is on the archived tar file

# Below is the example in case the plan file is not available on that tar file

tar -tvf 20150504142511.tgz 4011
tar: 4011: Not found in archive
tar: Error exit delayed from previous errors

# Here is the tar file that contain our plan id.
tar -tvf 20150505064911.tgz 4011
-rw-r--r-- nz/nz          7020 2015-05-05 06:50:07 4011/s4011_1.cpp
-rw-r--r-- nz/nz          7150 2015-05-05 06:50:07 4011/h4011_501.cpp
-rw-r--r-- nz/nz          2594 2015-05-05 06:50:07 4011/4011.pln

# Soon you confirm that the plan id is located on the tar file you have to use the command below to extract only the plan file that you are interested:

tar -xvzf 20150505064911.tgz 4011
4011/s4011_1.cpp
4011/h4011_501.cpp
4011/4011.pln

# After that you will see that it will create the folder 4011 and it will contain the plan id that you looking for:

ls -ltrh /nz/kit/log/planshist/current/
total 52M
-rw------- 1 nz nz  11M May  4 22:28 20150504142511.tgz
-rw------- 1 nz nz  11M May  5 06:47 20150504222822.tgz
-rw------- 1 nz nz  11M May  5 14:59 20150505064911.tgz
-rw------- 1 nz nz  11M May  5 23:13 20150505145906.tgz
-rw------- 1 nz nz 9.7M May  6 06:59 20150505231321.tgz
drwxrwxr-x 2 nz nz     4096 May  6 07:10 4011

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

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:RESTCOMPLETED 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 -npshost10.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 themwhich 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

Welcome to Netezza!

Welcome to Netezza!