Skip to content

How to install Netezza Queryhistory version 3?

Just follow the below steps to install Queryhistory version 3. NOTE: Here we will create Query history database names QHIST3.

1) Create user’s specific for Queryhistory version 3.

nzsql -c “create user histuser with password ‘P@ssword’ auth local;”
nzsql -c “create user histdbowner with password ‘P@ssw0rd’ auth local;”
2) Grant privileges to newly created users.

nzsql -c “grant create database to histdbowner;”
nzsql -c “grant list on user to histdbowner;”
3) Create the history database ( online help is available “nzhistcreatedb -h” )

nzhistcreatedb -d QHIST3 -t query -o histdbowner -pw P@ssw0rd -u histuser -v 3
4) Create the history configuration:

For each history database, create at least one history configuration that specifies the parameter HISTTYPE NONE. Setting this configuration to be the active configuration disables the collection of history data.

nzsql -c “CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE NONE;”

nzsql -d SYSTEM
CREATE HISTORY CONFIGURATION HISTORY_ENABLE — Name of the history configuration
HISTTYPE QUERY — Type of the history configuration
NPS LOCALHOST
DATABASE QHIST3 — Database that will be used by the history configuration
USER histuser — User that will load history data
PASSWORD ‘P@ssword’ — Password of the user, if this password change the history configuration must be updated in order to have the latest password to be able to load the data on the History Database.
COLLECT QUERY , PLAN , TABLE , COLUMN , SERVICE , STATE — Information that will be collected
LOADINTERVAL 5 — How long it will wait to load the data on the database
LOADMINTHRESHOLD 4 — The minimun amount in MB to collect before transfer staged batch files to the loading area.
LOADMAXTHRESHOLD 20 — In MB size
STORAGELIMIT 100 — The maximum size of the history data staging area in MB.
LOADRETRY 1 — The number of times that the load operation is retried.
ENABLEHIST TRUE
ENABLESYSTEM TRUE
VERSION 3 — Available on Release 7.1 and on-wards
;
5) To start the collection of history data

nzsql -c “set history configuration HISTORY_ENABLE;”

6) To activate the new history configuration you need to stop and restart the system, that is, by issuing the nzstop and nzstart.