OPTKEYS

Use the OPTKEYS parameter in CQMPARMS to control the summary reporting behavior of your Query Monitor subsystem.

Description
The level of granularity for summary buckets.
Note:
  • Specifying OPTKEYS values enables the collection of additional information about query activity to help determine the origin of query activity and problems.
  • You can override OPTKEYS settings in CQMPARMS using monitoring profile lines for individual OPTKEYS.
  • As you specify more OPTKEYS values, Db2 Query Monitor requires greater amounts of storage for the additional bucketing.
Syntax
OPTKEYS(optkey1,optkey2,...)
Default
If OPTKEYS is not specified, Db2 Query Monitor collects the following information about query activity:
  • Db2 subsystem
  • plan name
  • program name
  • section number
  • statement number
  • statement type
  • collection ID
  • consistency token
Valid values

The following values specify the level of granularity for which Db2 Query Monitor collects information:

AUTHIDS
The AUTHIDS parameter reduces collected information down to the level of individual Db2 authorization IDs.
CALLS
The CALLS parameter reduces collected information down to the level of the individual SQL calls. If the CALLS option is not specified in the OPTKEYS parameter, the statement number and description can contain N/A in the operational summaries.
CONNNAME
The CONNNAME parameter reduces collected information down to the level of the individual Db2 connection name.
CONNTYPE
The CONNTYPE parameter reduces collected information down to the level of the individual Db2 connection type.
CORRID
The CORRID parameter reduces collected information down to the level of the individual correlation ID. When OPTKEYS CORRID is specified both the CORRNAME and CORRNUMBER translations are performed.
Note: OPTKEYS(CORRNAME) and OPTKEYS(CORRID) are mutually exclusive. If OPTKEYS(CORRID) is used, the regular CORRID is collected, if OPTKEYS(CORRNAME) is used, the field is filled in according to the TSO/CAF/RRSAF/CICS/IMS descriptions above.
CORRNAME
The CORRNAME parameter directs Db2 Query Monitor to move only certain subsets of bytes from the originating correlation ID to the target summary record during the collection process. When OPTKEYS CORRNAME is specified only the CORRNAME translation is performed.

These subsets of bytes vary depending on the type of connection to Db2 (for example, TSO, BATCH, RRSAF, CICS®, IMS, etc.). The bytes that will be moved for the various connection types are shown below (the remaining right-most bytes will be space padded with EBCDIC blanks):

  • TSO, CAF, RRSAF - Bytes 1-8 of the originating correlation ID.
  • CICS - Bytes 5-8 of the correlation ID (Transaction ID).
  • IMS - Bytes 5-8 of the correlation ID (IMS PST#).
Note: OPTKEYS(CORRNAME) and OPTKEYS(CORRID) are mutually exclusive. If OPTKEYS(CORRID) is used, the regular CORRID is collected, if OPTKEYS(CORRNAME) is used, the field is filled in according to the TSO/CAF/RRSAF/CICS/IMS descriptions above.
CORRNUM
The CORRNUM parameter reduces collected information down to the level of the individual correlation number.
JOBNAME
The JOBNAME parameter reduces collected information down to the level of the individual z/OS batch jobname.
PARALLEL
The PARALLEL parameter reduces collected information down to the level of individual queries that are formulated using Db2 query parallelism.
PTEXT
The PTEXT parameter strips literals and multiple blanks from summary text. Literals are replaced by the indicator "&". Multiple whitespace characters, including blank (X'20'), tab (X'09'), line feed (x'0A'), form feed (x'0c'), and carriage return (X'0d') are reduced to a single blank. Literals included after an SQL "IS IN" clause will be stripped and replaced by the & indicator. This allows SQL text that differs by only literal values to be summarized together. OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified concurrently.
SCHEMA
The SCHEMA parameter reduces collected information down to the level of the individual Db2 Special Register Current SCHEMA value.
SP
The SP parameter reduces collected information down to the level of the individual stored procedure value.
TEXT
The TEXT parameter reduces collected information down to the level of the unique piece of SQL text. OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified concurrently.
WSNAME
The WSNAME parameter reduces collected information down to the level of the individual workstation name.
WSTRAN
The WSTRAN parameter reduces collected information down to the level of the individual workstation transaction.
WSUSER
The WSUSER parameter reduces collected information down to the level of the individual workstation user ID.
Example

OPTKEYS(TEXT)

Db2 Query Monitor collects information down to the level of the individual SQL text statement.

OPTKEYS(TEXT,AUTHIDS)

Db2 Query Monitor collects information down to the level of the individual SQL text statement and to the level of the individual Db2 authorization ID.