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. Specifying OPTKEYS values enables the collection of additional information about query activity to help determine the origin of query activity and problems. As you specify more OPTKEYSvalues, Db2® Query Monitor requires greater amounts of storage for the additional bucketing.

You can override the OPTKEYS settings in CQMPARMS by using monitoring profile lines for individual OPTKEYS.

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 enables collection of Db2 authorization IDs for summarization.
CALLS
The CALLS parameter enables collection of SQL calls for summarization. If CALLS is not specified in the OPTKEYS parameter, the statement number and description can contain N/A in the operational summaries.
CONNNAME>
The CONNNAME parameter enables collection of Db2 connection names for summarization.
CONNTYPE
The CONNTYPE parameter enables collection of individual Db2 connection types for summarization.
CORRID
The CORRID parameter enables collection of correlation IDs for summarization. When CORRID is specified, both the CORRNAME and CORRNUMBER translations are performed.

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 populated according to the TSO/CAF/RRSAF/CICS/IMS descriptions.

When OPTKEYS(CORRID) is specified both the CORRNAME and CORRNUM translations are performed. When OPTKEYS(CORRNAME) is specified only the CORRNAME translation is performed.

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®, and IMS™). 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#).

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.

CORRNUM
The CORRNUM parameter enables collection of individual correlation numbers for summarization.
JOBNAME
The JOBNAME parameter enables collection of individual z/OS batch job names for summarization.
PARALLEL
The PARALLEL parameter enables collection of individual queries that use Db2 query parallelism. This information can then be used for summary reporting.
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.
Note:
  • OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified together.
  • Stripping is done after a conversion to UTF-8 (codepage 1208).
REQSITE
The REQSITE parameter enables collection of the requesting site name for summarization.
SCHEMA
The SCHEMA parameter enables collection of CURRENT SCHEMA special register values for summarization.
Note:
  • OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified together.
  • Stripping is done after a conversion to UTF-8 (codepage 1208).
SP
The SP parameter enables collection of stored procedures for summarization.
Note:
  • OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified together.
  • Stripping is done after a conversion to UTF-8 (codepage 1208).
TEXT
The TEXT parameter enables collection of the unique pieces of SQL text for summarization. OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified together.
WORKLOAD
The WORKLOAD parameter enables collection of workload names for summarization.
WSNAME
The WSNAME parameter enables collection of workstation names for summarization.
WSTRAN
The WSTRAN parameter enables collection of workstation transactions for summarization.
WSUSER>
The WSUSER parameter enables collection of individual workstation user IDs for summarization.
Examples
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.