nz_query_history Table - learn learn 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.

we will cover these topics: hide 1) Why would we check query history details? 2) How to get Netezza Query History details using nz_query_hostory table? 3) nz_query_history Script 4) Create History Database and NZ_QUERY_HISTORY Table 5) Populating NZ_QUERY_HISTORY Table with Query History Data 5.1) How to install Netezza Queryhistory version 3? 6) What to read next?

Why would we check query history details?

How to get Netezza Query History details using nz_query_hostory table?

There are two ways to get query history in Netezza:

Now let us check how to get Netezza query history details usingnz_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 databaseto store any historical information. You can create separate history databaseto 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.

Netezza – Full Course

Free Netezza Course Netezza Twinfin Architecture Snowflake course

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.log2016-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 SYSTEMCREATE HISTORY CONFIGURATION HISTORY_ENABLE — Name of the history configurationHISTTYPE QUERY — Type of the history configurationNPS LOCALHOSTDATABASE QHIST3 — Database that will be used by the history configurationUSER histuser — User that will load history dataPASSWORD ‘[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 collectedLOADINTERVAL 5 — How long it will wait to load the data on the databaseLOADMINTHRESHOLD 4 — The minimun amount in MB to collect before transfer staged batch files to the loading area.LOADMAXTHRESHOLD 20 — In MB sizeSTORAGELIMIT 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 TRUEENABLESYSTEM TRUEVERSION 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.



Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

Connect with me:

My Resources:

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite