ALTER HISTORY CONFIGURATION

Use the ALTER HISTORY CONFIGURATION command to modify a configuration for history data collections. You cannot alter the current configuration.

Any changes that you make to a configuration by issuing this command are saved immediately. However, they take effect only after you:

Issue the SET HISTORY CONFIGURATION command for the configuration.
Stop and restart the Netezza Performance Server system by issuing the nzstop and nzstart commands.

The ALTER command is logged to the current history log. The target history database does not need to be empty.

Syntax

Syntax for altering the history configuration:

ALTER HISTORY CONFIGURATION <config_name> <hist_clause> …
<hist-clause> ::=
| HISTTYPE {QUERY | NONE}
| NPS { LOCAL }
| DATABASE <dbname>
| SCHEMA <schema_name>
| USER <username>
| PASSWORD <writer-password>
| COLLECT  <history_item>[,<history_item>…]
| LOADINTERVAL {number }
| LOADMINTHRESHOLD {number}
| LOADMAXTHRESHOLD {number}
| DISKFULLTHRESHOLD {number}
| STORAGELIMIT {number}
| LOADRETRY {number}
| ENABLEHIST {boolean}
| ENABLESYSTEM {boolean}
| VERSION <version>

<history_item>
  QUERY
| PLAN
| TABLE
| COLUMN

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 system. You cannot alter the current configuration, and you cannot 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 or NONE. Specify NONE to disable history collection. If you do not specify this input option, the current configuration value is retained.
NPS® LOCAL	Store the history logging information of the local Netezza Performance Server system. If you do not specify this option, the current configuration value is retained.
DATABASE <dbname>	The history database to which the captured data is written. The database must exist and must be created with the nzhistcreatedb script command on the Netezza Performance Server system. If you do not specify this input option, the current configuration value is retained. This input is a delimited identifier. If not delimited, the system converts the name to the host case.
SCHEMA <schema_name>	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 important for systems where the enable_user_schema setting is TRUE.
USER <username>	The user name for accessing and inserting data to the history database. (This name is the user name that is specified in the nzhistcreatedb command.) If you do not specify this input option, the current configuration value is retained. This input option is a delimited identifier. If not delimited, the system converts the name to the host case.
PASSWORD <writer password>	The password for the database user account. If you do not specify this input option, the current configuration value is retained. This input option 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. If you do not specify this input option, the current configuration value is retained. 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 it 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. If you do not specify this input option, the current configuration value is retained. 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 maximum value is 102400 MB (100 GB). The STORAGELIMIT value must be greater than LOADMAXTHRESHOLD. If you do not specify this input option, the current configuration value is retained. Valid values are 0 to any positive integer. If you specify 0, storage limit checking is disabled.
LOADRETRY	The number of times that the load operation is retried. The valid values are 0 (no retry), 1, or 2. If you do not specify this input option, the current configuration value is retained.
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. If you do not specify this input option, the current configuration value is retained. 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. If you do not specify this input option, the current configuration value is retained. If you specify FALSE, any queries against system tables that 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 and later, the version number is 1. Starting in Release 7.0.3, the version number changed to 2 to support multiple schemas in a database. The version must match the version number that is specified in the nzhistcreatedb command; otherwise, the loader process fails.

Outputs

The ALTER HISTORY CONFIGURATION command has the following outputs:

Table 2. ALTER HISTORY CONFIGURATION outputs
Output	Description
`ALTER HISTORY CONFIGURATION`	The command was successful.
`ERROR: permission denied`	You do not have the necessary priveleges to issue this command.
`ERROR: <config-name> not found.`	The specified configuration name cannot be found.
`ERROR: database <dbname> not found.`	The history database was not found on the system.

Privileges

You must be the admin user or your account must have Manage Security permissions to alter history configurations.

Usage

The following command changes the type of history data that is captured to only Query data (the other settings remain unchanged):

ALTER HISTORY CONFIGURATION all_hist COLLECT QUERY;