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.

SQL

Netezza query history setup

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:

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-util.

\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 currently collecting history, run this query

SELECT datname FROM _T_DATABASE WHERE dbcollecthistory IS TRUE
;

Some useful queries

Now that your query history collection is up and running, you can get many useful informations.

Who/what connects to your NPS

SELECT
  clienthost,
  clientip,
  sessionusername,
  CASE
    WHEN clienttype = 0  THEN 'None'
    WHEN clienttype = 1  THEN 'LibPq client'
    WHEN clienttype = 2  THEN 'ODBC client'
    WHEN clienttype = 3  THEN 'JDBC client'
    WHEN clienttype = 4  THEN 'nzload / nzunload'
    WHEN clienttype = 5  THEN 'Client of the client manager'
    WHEN clienttype = 6  THEN 'nzbackup / nzrestore'
    WHEN clienttype = 7  THEN 'nzreclaim'
    WHEN clienttype = 8  THEN 'Unused'
    WHEN clienttype = 9  THEN 'Internal Netezza tool'
    WHEN clienttype = 10 THEN 'OLE DB client'
  END AS clienttype,
  COUNT(1) AS num
FROM query.hist."$hist_session_prolog_1"
GROUP BY clienthost,clientip,sessionusername,clienttype
;