How to create and configure Query History for IBM PureData System for Analytics

Answer

Starting in NPS Release 7.1 and newest releases, we have history database version 3 that contain several of the history tables and views were updated to include fields to record client information:

- User ID: The user ID under which the client is running.
- Application name: The name of the client.
- Workstation name: The host name of the workstation on which the client runs.
- Accounting string: The value of the accounting string from the client information that is specified for the session.

We have two types of History Databases

- query history - A query database collects and stores the data that is most commonly needed to monitor and report on the query activity of a system.

- audit history - An audit database collects the same data as a query database, but stores the data in row-secured tables and digitally signs the data to prevent it from being changed.
Note: If an audit database is used and the history data staging area exceeds its STORAGELIMIT value, the system stops, and the administrator must free up space and restart the system before users can resume activity.

In order to create the query history database, you have to follow the steps below:

1) Create user's
nzsql -c "create user histuser with password 'histuser';"
nzsql -c "create user histdbowner with password 'histdbowner';"

2) Grant privileges
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 histdb -t query -o histdbowner -pw histdbowner -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_V3 -- Name of the history configuration
HISTTYPE QUERY -- Type of the history configuration
NPS LOCALHOST
DATABASE HISTDB -- Database that will be used by the history configuration
USER HISTUSER -- User that will load history data
PASSWORD 'histuser' -- 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 51200 -- 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_V3;"

Note: To activate the new history configuration you need to stop and restart the system, that is, by issuing the nzstop and nzstart commands.

6) How to stop the collection of history configuration:
nzsql -c "set history configuration hist_disabled;"

Note: Then issue the nzstop and nzstart commands to activate that history configuration.

As to best practices for maintaining query history database, here are few scripts that you can use to manage:

I) You will have to then run groom on the database, for example:
/nz/support/contrib/bin/nz_groom histdb
(Note To run above command successfully, you should have latest support tools installed)

II) If you are querying histdb database, you should run statistics on the tables. Easiest way to do this would be using support script:
/nz/support/contrib/bin/nz_genstats -express histdb

More details about manage the collection of history data, history views and tables are available on Netezza_system_admin_guide.pdf on Chapter 13. History data collection on page 13-1