CREATE HISTORY CONFIGURATION

Use the CREATE HISTORY CONFIGURATION command to create a configuration for history logging on a system.

To enable history-data collection, create at least one configuration for the current schema version. If this operation is being carried out for the first time for the current history schema version or if the current history configuration is of type NONE, this CREATE HISTORY CONFIGURATION operation is not logged in the history database.

Syntax

Syntax for creating the history configuration:
CREATE HISTORY CONFIGURATION <config_name> <clause> [ <clause>… ]
Where <clause> represents one of :
HISTTYPE {QUERY | AUDIT | NONE} |
NPS [ LOCALHOST | <hostname> ]   |
DATABASE <dbname> |
USER <username> |
SCHEMA <schemaname> |
PASSWORD <password> |
COLLECT  <item>[,<item>…] |
LOADINTERVAL <number> |
LOADMINTHRESHOLD <number> |
LOADMAXTHRESHOLD <number> |
DISKFULLTHRESHOLD <number> |
STORAGELIMIT <number> |
LOADRETRY <number> |
ENABLEHIST <boolean> |
ENABLESYSTEM <boolean> |
VERSION <version> 
[ KEY key_store . key_alias ]
[ INCLUDING [ ALL | SUCCESS | FAILURE ] ]
Where <item> represents one of:
QUERY
PLAN
TABLE
COLUMN
SERVICE
STATE

Inputs

The CREATE HISTORY CONFIGURATION command has the following inputs:

Table 1. CREATE HISTORY CONFIGURATION inputs
Input Description
<config_name> The name of the configuration to be created. You can create more than one configuration, but each name must be unique. This input option is a delimited identifier. If not delimited, the system converts the name to the default case.
HISTTYPE Whether to collect history data and, if so, the type of the history database:
QUERY
Collect history data in a query database.
AUDIT
Collect history data in an audit database.
NONE
Disable the collection of history data. Specifying this parameter automatically sets the following default values:
  • CONFIG_LEVEL to HIST_LEVEL_NONE
  • CONFIG_TARGETTYPE to HIST_TARGET_LOCAL
  • CONFIG_COLLECTFILTER to COLLECT_ALL
Important: If QUERY or AUDIT is specified, this value must match the database type specified in the nzhistcreatedb command used to create the database; otherwise, the loader process fails.
NPS [LOCALHOST | <hostname> ] Store the history data on the local Netezza Performance Server system. This value is the default and the only allowed value.
DATABASE The name of the history database to which the captured data is to be written. The database must exist and must have been created with the nzhistcreatedb command on the Netezza Performance Server system. There is no default. This input option is a delimited identifier. If not delimited, the system converts the name to the host case.
SCHEMA The schema in the history database where you want to load the history data, If you omit this value, the history data is loaded to the default schema of the history database. You should specify a schema value that matches the owner of the database. This is very important for systems where the enable_user_schema setting is TRUE.
USER The name of the load user, that is, the user account that is used to load history data into the database.
Important: This value must match the load user specified in the nzhistcreatedb command used to create the database; otherwise, the loader process fails.
PASSWORD The password for the database user account. There is no default. This is a single quoted string, and the password is stored as an encrypted string.

If the user password changes, you must update the history configuration with the new password as well, or the loader process fails.
COLLECT When history-data collection is enabled, the system always logs information about login failure, session creation, session termination, and the startup of the log capture (alcapp) process. Use this parameter to specify that additional information is to be collected:
QUERY
Collect query data.
PLAN
Collect query data and plan data from queries.
TABLE
Collect query data and table detail data from queries.
COLUMN
Collect query data, table detail data from queries, and column detail data from queries.
SERVICE
Collect CLI commands.
STATE
Collect state changes.

You can specify multiple values by using comma-separated values. For more information, see the section about history data collection in the IBM® Netezza® System Administrator’s Guide.
LOADINTERVAL The number of minutes to wait before the system checks the staged area for history data to transfer to the loading area. The valid values are 0 (to disable the timer), or 1 - 60 minutes. There is no default value.

This value works with LOADMINTHRESHOLD and LOADMAXTHRESHOLD to configure the loading process. For more information about the settings, see the section about history data collection in the IBM Netezza System Administrator’s Guide.
LOADMINTHRESHOLD The minimum amount of history data in MB to collect before it transfers the staged batch files to the loading area. A value of 0 disables the min threshold check. The maximum value is 102400 MB (100 GB).

This value works with the LOADINTERVAL and LOADMAXTHRESHOLD inputs to configure the loading process timers. For more information about the settings, see the section about history data collection in the IBM Netezza System Administrator’s Guide.
LOADMAXTHRESHOLD The amount of history data in MB to collect before automatically transferring the staged batch files to the loading area. A value of 0 disables the max threshold check. The maximum value is 102400 MB (100 GB).

This value works with the LOADMINTHRESHOLD and LOADINTERVAL inputs to configure the loading process timers. For more information about the settings, see the section about history data collection in the IBM Netezza System Administrator’s Guide.
DISKFULLTHRESHOLD This option is reserved for future use. Any value that you specify is ignored. The default value is 0.
STORAGELIMIT The maximum size of the history data staging area in MB. If the size of the staging area reaches or exceeds this threshold, history data collection stops until disk space can be freed. The STORAGELIMIT value must be greater than LOADMAXTHRESHOLD.

There is no default. Valid values are 0 to any positive integer. If you specify 0, storage limit checking is disabled. The maximum value is 102400 MB (100 GB).
LOADRETRY The number of times that the load operation is retried. The valid values are 0 (no retry), 1, or 2. There is no default.
ENABLEHIST Whether to log information about queries to the history database. A value of TRUE enables history collection for these queries, and FALSE disables the history collection. There is no default. If you specify FALSE, any queries against the history database which have syntax errors are captured.
ENABLESYSTEM Whether to log information about system queries. A system query accesses at least one system table but no user tables. A value of TRUE enables history collection for these queries, and FALSE disables the history collection. There is no default. If you specify FALSE, any queries against system tables which have syntax errors are captured.
VERSION <version> The history schema version of the configuration. By default, this is the history schema version of the current image. For release 4.6, the version number is 1.
Important: The specified version number must match the version number specified in the nzhistcreatedb command used to create the database; otherwise, the loader process fails.
KEY NONE Only applies to HISTTYPE AUDIT. If NONE is specified, then no crypto key is associated with the configuration and no digital signing is done. For more information about crypto keys, see the IBM Netezza Advanced Security Administrator's Guide.
KEY <crypto-key-name> The specified crypto key must be an existing public-private key pair. That crypto key is used to digitally sign the audit history data.
INCLUDING [ALL | SUCCESS | FAILURE ] Specifies to that the history logging should capture the following operations for query_prolog, query_epilog, plan_prolog, and plan_epilog tables:
  • ALL: capture successful and failed operations
  • SUCCESS: capture only the successful queries
  • FAILURE: capture only the failed operations
This setting does not apply to the data captured in the session_prolog, session_epilog and failed authentication.

Outputs

The CREATE HISTORY CONFIGURATION command has the following outputs:

Table 2. CREATE HISTORY CONFIGURATION outputs
Output Description
CREATE HISTORY CONFIGURATION The command was successful.
ERROR: permission denied You do not have the necessary permission to issue this command.
ERROR: database <dbname> not found. The history database was not found on the system.

Privileges

To configure history-data collection you must be the admin user or your account must have the Manage Security privilege.

Usage

Some sample usages of the CREATE HISTORY CONFIGURATION command follow.

The following command creates a history configuration named all_hist which enables the capture of all history information:
MYDB.SCH1(USER)=> CREATE HISTORY CONFIGURATION all_hist HISTTYPE QUERY 
DATABASE histdb USER histusr PASSWORD histusrpw COLLECT PLAN,COLUMN 
LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 VERSION 1;
The following command creates a history configuration named hist_mincollect which collects the basic level of history data (login failure, session creation, and termination, and the startup of the alcapp process):
MYDB.SCH1(USER)=> CREATE HISTORY CONFIGURATION hist_mincollect HISTTYPE 
QUERY DATABASE histdb USER histusr PASSWORD histusrpw COLLECT 
LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 VERSION 1;
The following command creates a history configuration named hist_queryonly which collects query and plan details and the basic level of information:
MYDB.SCH1(USER)=> CREATE HISTORY CONFIGURATION hist_mincollect HISTTYPE 
QUERY DATABASE "query db" USER histusr PASSWORD histusrpw COLLECT 
QUERY,PLAN LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 
VERSION 1;
The following command creates a history configuration named hist_disabled that disables history collection:
MYDB.SCH1(USER)=> CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE 
NONE;