Sometimes you may need to verify the queries that are running for a long time on production servers. There are several ways that you can perform this task. For instance, you can use Netezza administrative tool to verify long running queries. In this post, we will check how to get Netezza query history details using nz_query_history table.
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 you need query history details?
As mentioned in the previous section, you need to consider query history to identify the potential long running queries.
- 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. For more details, read Migrating Netezza Data to Hadoop Ecosystem and Sample Approach
- 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.ddlAbove 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
[nz@netezza bin]$ /nz/support/bin/nz_query_history HIST_DB NZ_QUERY_HISTORY
Load session of table 'NZ_QUERY_HISTORY' completed successfullyHope this helps 🙂