Creating history configurations

A history configuration specifies such things as whether history-data collection is enabled, the name of the history database, the user account that is to be used to access it, and how frequently to load captured data. Only one history configuration is active at a time.

History data always includes information about:
  • Login failures
  • Session creation
  • Session termination
  • The startup of the history-data collection process.
The history configuration specifies whether the system is also to collect one or more of the following types of data:
query data
Information about general query parameters such as data about log entries for the operations that run on the system, session creations, failed authentications, session completions, user queries, and query status.
plan data
Information about the query plans for the system. This information is obtained at the start of the plan (prolog data) and at the end of the plan execution (epilog data). This information provides insight into plan priority, start and finish times, snippet counts, completed snippet counts, and result data sizes.
table data
Information about table access attempts such as the table being accessed and the type of operation being run against the table (create, insert, select, drop, delete, update, or lock).
column data
Information about column access attempts such as the column being accessed and the type of operation being run against the column (select, set, where, group by, having, order by, or alter).
service data
CLI commands such as nzbackup, nzrestore, and nzstate.
state data
The state (ONLINE, PAUSED, OFFLINE, STOPPED) of the system.
You can define several history configurations, each of which collects a different set of history data. For example, you might have a different configuration to collect each of the following types of information:
  • All possible history data. You might record this level of information when you introduce a new application or a new group of users, or when troubleshooting service issues.
  • Basic history information that you use during routine operational periods.
  • Detailed information about a specific area, such as table access. You can use this information to identify tables that might be unused and are candidates for cleanup.

To create a history configuration, issue the CREATE HISTORY CONFIGURATION command, as described in the IBM® Netezza® Database User’s Guide.

To create, show, and manage history configurations, your user account must have the Manage Security privilege. The specified history database must already be created on the system.

For example, the following command creates a history configuration named hist_plancol:
SYSTEM.ADMIN(ADMIN)=> CREATE HISTORY CONFIGURATION hist_plancol HISTTYPE QUERY
  DATABASE histdb USER loaduser PASSWORD 'loaduserpw' COLLECT PLAN,COLUMN
  LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 STORAGELIMIT 25
  LOADRETRY 1 VERSION 3;

The configuration name, user name, and database name are identifiers and can be enclosed in double quotation marks. For example: "sample configuration", "sample user", and "sample qhist db" are all valid names.

Important: The following history configuration settings must match the corresponding settings for the history database; otherwise, the loader process fails:
Setting Parameter in the history configuration Parameter in the nzhistcreatedb command
History database type HISTTYPE (if either QUERY or AUDIT) -t or --db-type
Load user USER -u or --user
Version number VERSION -v or --schema
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 and automatically sets the following default values:
  • CONFIG_LEVEL to HIST_LEVEL_NONE
  • CONFIG_TARGETTYPE to HIST_TARGET_LOCAL
  • CONFIG_COLLECTFILTER to COLLECT_ALL
For example, the following command creates a history configuration named hist_disabled that can be used to disable history collection:
SYSTEM.ADMIN(ADMIN)=> CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE
  NONE;