Query history in a persistent table

Answer

Query history information is stored in a system view, but only the last few thousand interactions are stored. To persist this information, the following steps must be taken.

This document details the steps a DBA must take to capture query history information.

Persisting query history information

A historical record of what queries ran and when can be very useful information for troubleshooting or capacity planning purposes. You can schedule a Netezza script to copy the information currently stored in the system view _v_qryhist to a SPU-based table -- typically called NZ_QUERY_HISTORY. This script is provided as part of the Netezza Support tools and it can be scheduled using cron.

Creating NZ_QUERY_HISTORY

Before you can store the data into this table, you must create the table in the database in which you want to store the data. It is strongly recommended that you create a new schema for this purpose or use your existing integrated query history capture database.:

nzsql -c "CREATE DATABASE QUERY_HISTORY"

Next, create the table using the DDL found in /nz/support/bin.

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

This will create a table in the QUERY_HISTORY database called NZ_QUERY_HISTORY and a view called NZ_QUERY_HISTORY_VIEW.

Populating with query history data

Next, run the /nz/support/bin/nz_query_history script to copy the data in the system tables to the newly-created NZ_QUERY_HISTORY table found in your new schema. This script takes two optional arguments:

• Database (if the value is not the same as environment variable NZ_DATABASE)
• Table (if the value is not the same as environment variable NZ_QUERY_HISTORY)

To run this script and explicitly use the newly-created QUERY_HISTORY database:

/nz/support/bin/nz_query_history QUERY_HISTORY

This will produce very little output; a successful run will only produce the message:

Load session of table 'NZ_QUERY_HISTORY' completed successfully

Scheduling the query history load

The next step is to schedule this script to run with a frequency that ensures that all of the query history data is captured. The script manages what data needs to be migrated; no special steps must be taken to avoid duplicate data.

Some customers run the script every 15 minutes while others run it once or twice per day. Running the script every hour would not be unreasonable. The in-memory view we are writing from has a default size of 2000 entries, and when it reaches this limit as a new row enters the table, the oldest row is dropped. We therefore want to write this data before it rolls off. It is recommended that you use cron to schedule the script. As the nz user, run the following command:

crontab -e

This command opens the nz user's cron table in the vi editor. Add the following line to run the script every hour at 15 minutes past the hour:

15   * * * * if [ -d /nz/lost+found ]; then ( . /export/home/nz/.bash_profile ; /nz/support/bin/nz_query_history QUERY_HISTORY) ; fi > /dev/null 2>&1

To run the script every 15 minutes, add the following line instead:

*/15  * * * * if [ -d /nz/lost+found ]; then ( . /export/home/nz/.bash_profile ; /nz/support/bin/nz_query_history QUERY_HISTORY) ; fi > /dev/null 2>&1

To run this once per day at 2300 hours, add the following line instead:

00 23 * * * if [ -d /nz/lost+found ]; then ( . /export/home/nz/.bash_profile ; /nz/support/bin/nz_query_history QUERY_HISTORY) ; fi > /dev/null 2>&1

However you schedule it, remember to duplicate cron entries to the passive host.