How to collect Netezza history

Collecting your Netezza query history is a mandatory step before optimization. Read this article to know how to collect Netezza history easily.

First of all, log into your NPS with the nz user. If you open an nzsql session, I suppose you login as ADMIN.

Create hist user and query database

Open an nzsql session an launch the following

CREATE USER hist WITH PASSWORD '***' AUTH LOCAL;
GRANT CREATE DATABASE TO hist;

Log out, and back to the shell prompt to create a query database.

nzhistcreatedb --db query --db-type query --owner hist --pw '***' -v 1

Note that you should set the password according to your policies.

History configurations

Get an nzsql prompt again and create two history configurations:

• all_hist
• no_hist

CREATE HISTORY CONFIGURATION all_hist
HISTTYPE QUERY
DATABASE query
USER hist
PASSWORD '***'
COLLECT PLAN,COLUMN
LOADINTERVAL 5
LOADMINTHRESHOLD 4
LOADMAXTHRESHOLD 20
STORAGELIMIT 25
LOADRETRY 1
VERSION 1
;
CREATE HISTORY CONFIGURATION no_hist
HISTTYPE NONE
;

Set history configuration

To activate an history configuration you need an NPS restart.
Attention: do it when there is no ETL running
Set history configuration

SET HISTORY CONFIGURATION all_hist;

Back to the shell prompt and restart the NPS

nzstop
nzstart

Create a group that can read the query database

For sure you need to grant permission to see the history to some users. I use a set of utils I created: checkout my nz-utils.

\c query
CALL util..grant_readonly('query_hist_readonly');

Now you can add any user to *query_hist_readonly* group, for instance

ALTER GROUP query_hist_readonly ADD USER pippo;
ALTER GROUP query_hist_readonly ADD USER pluto;

List databases that collect history

To know which database are currenty collecting history, run this query

SELECT datname FROM _T_DATABASE WHERE dbcollecthistory IS TRUE;