ALTER HISTORY CONFIGURATION

Use the ALTER HISTORY CONFIGURATION command to modify the configuration of query or audit history logging. This command runs on the source Netezza Performance Server system. You cannot alter the current history configuration.

Synopsis

Syntax for altering a history configuration:
ALTER HISTORY CONFIGURATION <config-name> <hist-clause>...
<hist-clause> ::=
   | HISTTYPE {QUERY | AUDIT | NONE}
   | NPS { LOCALHOST | <hostname> }
   | DATABASE <dbname>
   | SCHEMA <schemaname>
   | USER <username>
   | PASSWORD <writer-password>
   | COLLECT  <history-item> ,...
   | LOADINTERVAL {number }
   | LOADMINTHRESHOLD {number}
   | LOADMAXTHRESHOLD {number}
   | DISKFULLTHRESHOLD {number}
   | STORAGELIMIT {number}
   | LOADRETRY {number}
   | ENABLEHIST {boolean}
   | ENABLESYSTEM {boolean}
   | VERSION <version>
   | KEY { NONE | <cryto-key-name> 
   | INCLUDING [ONLY] { SUCCESS | FAILURE | ALL }

Inputs

The ALTER HISTORY CONFIGURATION command has the following inputs:
Table 1. ALTER HISTORY CONFIGURATION inputs
Input Description
config_name The name of the configuration to alter. The configuration must exist on the Netezza Performance Server system. You cannot alter the current configuration or can you change the name of the configuration. (To change a configuration name, you must drop the configuration and create a new one.) This input option is a delimited identifier. If not delimited, the system converts the name to the host case.
HISTTYPE The type of the database to create, which can be QUERY, AUDIT, or NONE. Specify NONE to disable history collection. If you do not specify this input option, the current configuration value is retained.
LOCALHOST Use this Netezza Performance Server system as the target system for query or audit history logging. If not specified, the current value is retained.
DATABASE <dbname> Specifies the database to use for query or audit history logging. The database must be created with the CREATE HISTORY DATABASE command on the target Netezza Performance Server system. If not specified, the current value is retained. This input is a delimited identifier. If not delimited, the name is converted to host case.
SCHEMA <schemaname> Specifies 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<username> The user name to use when logging in to the Netezza Performance Server system to write the query or audit history log. If not specified, the current value is retained. This input is a delimited identifier. If not delimited, the name is converted to host case.
PASSWORD <password> The password to use when logging in to the Netezza Performance Server system to write the query or audit history log. If not specified, the current value is retained. This input is a single quoted string.
COLLECT Specifies the history data to be collected. The system always collects login failure, session creation, session deletion, and the startup of the log capture (alcapp) process. You can specify more information to collect by using this clause:
  • QUERY to collect the query data
  • PLAN to collect plan data from queries. If you specify PLAN, you automatically collect QUERY as well.
  • TABLE to collect table detail data from queries. If you specify TABLE, you automatically collect QUERY as well.
  • COLUMN to collect column detail data from queries. If you specify COLUMN, you automatically collect QUERY and TABLE as well.
  • SERVICE to collect CLI commands
  • STATE to collect system state changes

You can specify multiple values by using comma-separated values. If you do not specify this input option, the current configuration value is retained. For more information, see the section about query history in the IBM® Netezza® System Administrator’s Guide.
LOADINTERVAL Specified in minutes. It must be at least 0. Maximum value is 60 minutes. The “alcapture” process attempts to message the “alcloader” at this interval to load data provided the min threshold is reached. If not specified, the current value is retained.
LOADMINTHRESHOLD Specified in MB. At the expiry of every LOADINTERVAL if this minimum threshold of data is accumulated, the “alcapture” process can message the “alcloader” process to load this data. If not specified, the current value is retained. This value can be zero.
LOADMAXTHRESHOLD Specified in MB. When this amount of data is reached, the “alcapture” sends a message to “alcloader” to load the data. If not specified, the current value is retained. This value can be zero.
DISKFULLTHRESHOLD Specified in MB. If the amount of total free disk space in /nz/data falls below this threshold, the query or audit history data collection is stopped until manual intervention occurs. For audit history, the system is stopped.
STORAGELIMIT Specified in MB. If this limit is reached in the staging area then the query or audit history data collection is stopped until manual intervention occurs. For audit history, the system is stopped. If not specified, the current value is retained. This value can be zero. If zero, the storage limit checking is disabled.
LOADRETRY The number of times the load operation is retried by the “alcloader” process. Minimum is 0 and maximum is 2. If not specified, the current value is retained.
ENABLEHIST This TRUE / FALSE flag enables or disables the inclusion of queries on query or audit history database in query or audit history. If not specified, the current value is retained. Even if this value is specified as FALSE, if queries against history database have syntax errors they are captured.
ENABLESYSTEM This TRUE / FALSE flag enables or disables the inclusion of queries on system tables in query or audit history. If not specified, the current value is retained. Even if this value is set to FALSE, a query with syntax error against system tables is captured.
VERSION <version> The schema version of the history database. If not specified, the current value is retained.
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.
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.

Outputs

The ALTER HISTORY CONFIGURATION command has the following outputs:
Table 2. ALTER HISTORY CONFIGURATION outputs
Output Description
ALTER HISTORY CONFIGURATION The message that the system returns if the command is successful.
ERROR: permission denied. You must have Manage Security privilege.
ERROR: login failed. The credentials provided for the writer did not work. This check can be made only if the target database is on the same system as the source.
ERROR: <config-name> not found. The specified configuration name cannot be found.
ERROR: database <dbname> not found. The query or audit history database was not found on the target system. This validation is done only if the target database is on the same system as the source.

Description

For details about all the options of the ALTER HISTORY CONFIGURATION command, see the IBM Netezza Database User’s Guide. Current query or audit is not interrupted by ALTER as this action can be done only on a configuration that is not current. The ALTER is logged to the current query or audit history log.

The target query or audit database does not need to be empty.

If you frequently change configurations, it is possible to have some staged data not loaded yet. If the configuration corresponding to part of the staged data is changed, it is possible the loader can error out and the files moved to the error directory.

Privileges required
You must have Manage Security privilege to alter query or audit logging.
Common tasks
The ALTER HISTORY CONFIGURATION command updates the query or audit history configuration in the catalog, and can be done only on a configuration that is not current.

Usage

The following provides sample usage:
ALTER HISTORY CONFIGURATION all_hist VERSION 3;