How to enable Query history on Netezza systems

Below are the sample steps that you can follow up to create and enable the history database:

1) Add users and grant appropriate access to them:

system(admin)=> create user owner with password 'ownerpw‘;
system(admin)=> grant create database to owner;
system(admin)=> create user audituser with password ‘audituserpw‘;
system(admin)=> grant list on audituser to owner;

2) Create history database using  “nzhistcreatedb” command:

$ nzhistcreatedb -d qhist -t q -u audituser -o owner -p 'ownerpw' -v 1

3) Maintenance of history database using  “nzhistcleanupdb” c- ommand:

$ nzhistcleanupdb -d qhist -u owner -pw 'ownerpw' –t ‘2011-10-31’

4) Create history configuration:

$ nzsql -c "CREATE HISTORY CONFIGURATION QUERY_HISTORY HISTTYPE QUERY NPS LOCALHOST DATABASE QHIST USER audituser PASSWORD 'audituserpw' COLLECT QUERY,PLAN,TABLE,COLUMN LOADINTERVAL 0 LOADMINTHRESHOLD 0 LOADMAXTHRESHOLD 1 STORAGELIMIT 10 LOADRETRY 1 ENABLEHIST TRUE ENABLESYSTEM TRUE VERSION 1;” VERSION 1;"

5) Enable history collection:

$ nzsql –c “SET HISTORY CONFIGURATION QUERY_HISTORY;”

6) Restart NPS using command:

$ nzstop;nzstart