nz_query_history Table

Reading Time: 4 minutes

Netezza query history configuration steps are simple. You can follow below steps to use Netezza query history views to collect Netezza queries historical data in separate history table in optional history database.

Why would we check query history details?

• Identify long running queries so that you can improve the performance of those queries.
• Check Server health. You can identify average time taken from server to execute given queries.
• Identify failed queries.
• Sometimes you may need to check long running queries and offload them to other distributed computing systems such as Hadoop or other high-performance systems. 
• Troubleshooting or capacity planning purposes

How to get Netezza Query History details using nz_query_hostory table?

There are two ways to get query history in Netezza:

• _v_qryhist – You can query this system view to get Netezza query history details. This is a conceptual view on top of virtual table _t_qryhist.
• nz_query_history – You can populate this table using IBM provided nz_query_history shell script.

Now let us check how to get Netezza query history details using
nz_query_history table.

nz_query_history Script

You can schedule a Netezza nz_query_history script to copy the information currently stored in the system view _v_qryhist to a SPU-based table called NZ_QUERY_HISTORY. This script is provided as part of the Netezza Support tools and it can be scheduled using cron or any scheduling tool if you have. DDL and shell scripts are available at /nz/support/bin location.

Create History Database and NZ_QUERY_HISTORY Table

As a best practice, it is always better to create separate database
to store any historical information. You can create separate history database
to maintain Netezza query history.

nzsql -c "CREATE DATABASE HIST_DB"

Next, connect to above created database and create the table using the DDL found in /nz/support/bin. 

nzsql -d QUERY_HISTORY -f /nz/support/bin/nz_query_history.ddl

Above script will create NZ_QUERY_HISTORY table and NZ_QUERY_HISTORY_VIEW view.

For column details, read IBM official document.

Populating NZ_QUERY_HISTORY Table with Query History Data 

Next, execute the /nz/support/bin/nz_query_history shell script to copy the data in the system tables to the newly-created NZ_QUERY_HISTORY table found in your new schema.

/nz/support/bin/nz_query_history HIST_DB NZ_QUERY_HISTORY
[[email protected] bin]$ /nz/support/bin/nz_query_history HIST_DB NZ_QUERY_HISTORY
Load session of table 'NZ_QUERY_HISTORY' completed successfully

ISSUE:

Queryhistory stopped collecting data and all the directories under /nz/data/hist are empty.

CAUSE:

This can happen when the max storage limit is reached.

/nz/kit/log/alcapp->more alcapp.log
2016-01-23 20:32:08.759330 EDT (8231) Error: History Stored Data size: 1000 exceeds STORAGELIMIT (1000).
2016-01-23 20:32:08.765248 EDT (8231) Error: Disabling History Capture.

RESOLUTION:

To fix the issue you have to increase the maximum storage limit from 1000 MB (in this case) to a higher value. Maximum size allowed is 100GB.

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 ‘[email protected]’ auth local;”
nzsql -c “create user histdbowner with password ‘[email protected]’ 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 [email protected] -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 ‘[email protected]’ — 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.