Monitoring profiles - columns and fields (ISPF)

Information about each monitoring profile is displayed on the View Monitoring Profile panel, Update Monitoring Profile panel, View Profile Line panel, Insert Profile Line panel, and Update Profile Line panel.

AUTHID

The primary authorization ID.

Column name on View Profile panel: AUTHID

Collect Dynamic SQL

Indicates whether data is collected for dynamic SQL for the workload.

Column name on View Profile panel: COLLDSQL

Collect Object Data

Indicates whether object data is collected for the workload.

Note: If you specify N for this parameter and you attempt to issue the ENABLE_OBJECTS dynamic configuration change commands to enable object statistics collection, it will be ignored.

Column name on View Profile panel: OBJECTS

Collect Static SQL

Indicates whether data is collected for static SQL for the workload.

Column name on View Profile panel: COLLSSQL

Collect Summary Positive SQLCODEs

Indicates whether positive SQLCODEs are processed in addition to negative SQLCODEs for the SQL code summaries. This column is only checked for profile include lines.

Column name on View Profile panel: SPOSSQLC

Connection ID

The connection ID.

Column name on View Profile panel: Conn

CORRID

The correlation ID.

Column name on View Profile panel: Corrid

CORRNAME

The correlation ID adjusted by the conventions used by IMS and CICS.

Column name on View Profile panel: CORRNAME

Creator

The user ID that created the monitoring profile.

Column name on View Profile panel: not applicable

Creator

The user ID that created the monitoring profile.

Created

The date and time the monitoring profile was created.

DB2 Subsystem

The Db2 subsystem.

Column name on View Profile panel: SSID

Disable Summary Reporting

Indicates whether summary information is to be reported for the unit of SQL activity.

Note:

A Disable Summary Reporting value of Y is only valid for EXCLUDE profile lines. A Disable Summary Reporting value of N must be specified for all INCLUDE profile lines.
Disable Summary Reporting does not impact Db2 command reporting. Monitoring profiles do not have any effect on Db2 commands.

Column name on View Profile panel: DISABLE

Exclude Summary SQLCODES

Indicates whether SQLCODES are included in View SQLCODEs displays. Valid values are Y (SQLCODES are excluded in summary displays) and N (No SQLCODES is excluded in summary displays). If Y is specified for Exclude Summary SQLCODES, the Exception SQLCODE Exclusion List displays, enabling you to list the SQLCODES you want to exclude from summary display.

Column name on View Profile panel: SSQLCODES

Ignore SQLCODE 100 for Read Activity

Indicates whether to ignore positive SQLCODE 100.

Offload table name: CQM_EXCEPTIONS
Column name: ISQL100

INCLUDE/EXCLUDE

Specifies whether detected SQL activity is to be included or excluded from further processing. Valid values are I (includes matching SQL activity in processing as defined by the monitoring profile) and E (excludes matching SQL activity from processing as defined by the monitoring profile).

Include Getpages Objects: Enables you to toggle switch the display of the Alert GETPAGE Inclusion List panel. Valid values are N (DEFAULT - No) and Y (Yes - the Alert GETPAGE Inclusion List panel is displayed upon exit (PF3) to allow entry or update of GETPAGE objects).; Note: If Include or Exclude SQLCODES are also selected as Y, then the entry panels will display before the Alert GETPAGE Inclusion List panel.

Include Summary SQLCODEs

Indicates whether to specify a list of SQLCODES to include in summaries. Valid values are Y (shows the inclusion SQLCODE list panel where the user can specify a list of SQLCODES to include in summaries) and N (DEFAULT – do not specify SQLCODES to include in summaries).

Column name on View Profile panel: SSQLCODEI

HOSTVARS

Indicates whether host variables are to be collected for the workload.

Column name on View Profile panel: HOSTVARS

INCLUDE/EXCLUDE

Indicates whether matching SQL activity is to be included in or excluded from processing. If excluded activity is also to be removed from summaries, Disable Summary Reporting must be set to Y on the exclude line.

Column name on View Profile panel: INCL/EXCL

JOBNAME

The name of the job.

Column name on View Profile panel: Jobname

Name

The name of the monitoring profile.

OPTKEYS(AUTHIDS)

The AUTHIDS parameter reduces collected information down to the level of individual Db2 authorization IDs.

Column name on View Profile panel: OPTAUTHID

OPTKEYS(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.

Column name on View Profile panel: OPTCALLS

OPTKEYS(CONNNAME)

The CONNNAME parameter reduces collected information down to the level of the individual Db2 connection name.

Column name on View Profile panel: OPTCNAME

OPTKEYS(CONNTYPE)

The CONNTYPE parameter reduces collected information down to the level of the individual Db2 connection type.

Column name on View Profile panel: OPTCTYPE

OPTKEYS(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.

Column name on View Profile panel: OPTCORRID

OPTKEYS(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#).

Column name on View Profile panel: OPTCORRNM

OPTKEYS(CORRNUM)

The CORRNUM parameter reduces collected information down to the level of the individual correlation number.

Column name on View Profile panel: OPTCRNUM

OPTKEYS(JOBNAME)

The JOBNAME parameter reduces collected information down to the level of the individual z/OS batch jobname.

Column name on View Profile panel: OPTJOBNM

OPTKEYS(PARALLEL)

The PARALLEL parameter reduces collected information down to the level of individual queries that are formulated using Db2 query parallelism.

Column name on View Profile panel: OPTPARLL

OPTKEYS(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.

Column name on View Profile panel: OPTPTEXT

OPTKEYS(SCHEMA)

The SCHEMA parameter reduces collected information down to the level of the individual Db2 Special Register Current SCHEMA value.

Column name on View Profile panel: OPTSCHEM

OPTKEYS(SP)

The SP parameter reduces collected information down to the level of the individual stored procedure value.

Column name on View Profile panel: OPTSP

OPTKEYS(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.

Column name on View Profile panel: OPTTEXT

OPTKEYS(WSNAME)

The WSNAME parameter reduces collected information down to the level of the individual workstation name.

Column name on View Profile panel: OPTWSNAME

OPTKEYS(WSTRAN)

The WSTRAN parameter reduces collected information down to the level of the individual workstation transaction.

Column name on View Profile panel: OPTWSTRAN

OPTKEYS(WSUSER)

The WSUSER parameter reduces collected information down to the level of the individual workstation user ID.

Column name on View Profile panel: OPTWSUSER

Override OPTKEYS

The OPTKEYS parameter specifies the level of granularity for summary buckets. You can set up monitoring profile lines to override OPTKEYS settings in CQMPARMS for individual OPTKEYS.

Column name on View Profile panel: OPTKEYS

Override SQL Collection

Indicates whether Db2 Query Monitor overrides the COLLECT_STATIC_SQL and COLLECT_DYNAMIC_SQL parameters in CQMPARMS for the workload according to the values specified in the Collect Static SQL and Collect Dynamic SQL fields.

Column name on View Profile panel: OVRDPRMS

Plan Name

The Db2 plan name.

Column name on View Profile panel: Plan

Program Name

The Db2 package or DBRM name.

Column name on View Profile panel: Program

QM Plans

Indicates whether a monitoring agent is to exclude SQL activity generated by plans associated with Db2 Query Monitor. This specification removes the Db2 Query Monitor activity from exception processing and current activity. It does not impact summary data collection. This column works in conjunction with the QM Plan1, QM Plan2, and QM Plan3 columns that indicate the names of the plans associated with Db2 Query Monitor.

QM Plan1, QM Plan2, QM Plan3

Patterns that will be used by a monitoring agent to determine if a plan is associated with Db2 Query Monitor. This pattern is only used if the QM Plans column is set to Y.

Updated

The most recent date and time the monitoring profile was updated.

Workload Name

The name of the SQL workload. The workload name is a 32-byte character string that is assigned to the SQL activity by the selection criteria of the profile line and identifies the SQL activity in current activity, exceptions, and alerts. It is recommended that you name your workload to facilitate the identification of the monitoring profile line and the workload with which captured activity is associated.

Column name on View Profile panel: WORKLOAD NAME

Workstation Name

The workstation name.

Column name on View Profile panel: WSNAME

Workstation Trans

The workstation transaction.

Column name on View Profile panel: WSTRAN

Workstation User

The workstation user.

Column name on View Profile panel: WSUSER

Alerts

Threshold Alerts

Indicates whether threshold-based alerts are generated for the specified CPU time, elapsed time, delay time, number of getpages, and number of SQL Calls.

Column name on View Profile panel: ATHRSHLD

Alert CPU

The CPU time that when exceeded produces an alert for the workload.

Note: You must specify Y in the Threshold Alerts field to enable Alert CPU.

Column name on View Profile panel: ACPU

Alert Elapsed

The elapsed time that when exceeded produces an alert for the workload.

Note: You must specify Y in the Threshold Alerts field to enable Alert Elapsed.

Column name on View Profile panel: AELAPSED

Alert Delays

The delay time that, when exceeded, produces an alert for that unit of SQL activity.

Note: You must specify Y in the Threshold Alerts field to enable Alert Delays.

Column name on View Profile panel: ADELAYS

Alert Getpages

The number of getpages that when exceeded produces an alert for the workload.

Note: You must specify Y in the Threshold Alerts field to enable Alert Getpages.

Column name on View Profile panel: AGETPAGES

Alert SQL Calls

The number of SQL calls that when exceeded produces an alert for the workload.

Note: You must specify Y in the Threshold Alerts field to enable Alert SQL Calls.

Column name on View Profile panel: ASQLCALLS

Anomaly-based Alerts

Anomaly-based Alerts - CPU Anomalies

Indicates whether to generate alerts for CPU anomalies.

Column name on View Profile panel: ACPUANM

Anomaly-based Alerts - CPU Anomalies - Toleration Level

Indicates the tolerance value that specifies the number of standard deviation units over the rolling average CPU time that, when exceeded, produces an anomaly alert for CPU time for an SQL statement.

Column name on View Profile panel: ACPUTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_CPU_TIME_PROF_ALRT_TOLER

Anomaly-based Alerts - Elapsed Anomalies

Indicates whether to generate alert for elapsed time anomalies.

Column name on View Profile panel: AELAPANM

Anomaly-based Alerts - Elapsed Anomalies - Toleration Level

Indicates the elapsed time toleration that when exceeded produces an anomaly alert for elapsed time for an SQL statement.

Column name on View Profile panel: AELAPTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_ELAP_TIME_PROF_ALRT_TOLER

Anomaly-based Alerts - Getpage Anomalies

Indicates whether to generate alerts for getpage anomalies.

Column name on View Profile panel: AGETPANM

Anomaly-based Alerts - Getpage Anomalies - Toleration Level

Indicates the getpage toleration that when exceeded produces an anomaly alert for getpage for an SQL statement.

Column name on View Profile panel: AGETPTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: TOTAL_GETP_PROF_ALRT_TOLER

Generate SQLCODE Alerts

Indicates whether SQLCODES generate alerts. Valid values are Y (SQLCODES generate alerts) and N (SQLCODES do not generate alerts).

Notes:

The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters behave independently of one another.
The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters work together with the Exclude Exception SQLCODEs and Exclude Alert SQLCODEs parameters. The Exclude Exception SQLCODEs and Exclude Alert SQLCODEs parameters define whether specific SQLCODES (defined by the SQLCODE exclusion list) are excluded from exception/alert processing. The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters act as high-level flags to turn on or off the generation of exceptions/alerts for SQLCODES (in general). So, for example:
- If you specify Exclude Exception SQLCODEs = Y, and specify -901 SQLCODE on the SQLCODE exclusion list, and specify Generate SQLCODE Exceptions = Y, then exceptions will be produced for all SQLCODEs except -901.
- If you specify Exclude Exception SQLCODEs = Y, and specify -901 SQLCODE on the SQLCODE exclusion list, and specify Generate SQLCODE Exceptions = N, then exceptions will not be produced for any SQLCODEs (in this case, the specification of -901 SQLCODE on the SQLCODE exclusion list is not really necessary since it will be enforced by the higher-level Generate SQLCODE Exceptions = N setting).
- If you specify Exclude Exception SQLCODEs = N, and specify Generate SQLCODE Exceptions = Y, then exceptions will be produced for all SQLCODEs.
- If you specify Exclude Exception SQLCODEs = N, and specify Generate SQLCODE Exceptions = N, then exceptions will not be produced for any SQLCODEs.

Column name on View Profile panel: SQLCALRTS

Include Alert SQLCODEs

Indicates whether to specify a list of SQLCODES to include in alert processing. Valid values are Y (shows the inclusion SQLCODE list panel where the user can specify a list of SQLCODES to include in alert processing) and N (DEFAULT – do not specify SQLCODES to include in alert processing).

Column name on View Profile panel: ASQLCODEI

Exceptions

Exception Limit

The maximum number of exceptions that will be generated for workload line in the monitoring profile. If you specify a value of zero, collected activity that matches other criteria in the profile line will not be treated as an exception for display (because the exception limit threshold of zero would have been exceeded).

Column name on View Profile panel: ELIMIT

Threshold Exceptions

Indicates whether threshold-based exceptions are generated for the specified CPU time, elapsed time, delay time, number of getpages, and number of SQL Calls.

Column name on View Profile panel: ETHRSHLD

Exception CPU

The Db2 CPU time that, when exceeded, produces an exception for that unit of SQL activity.

Note: You must specify Y in the Threshold Exceptions field to enable Exception CPU.

Column name on View Profile panel: ECPU

Exception Elapsed

The Db2 elapsed time that, when exceeded, produces an exception for that unit of SQL activity. A value of zero causes these criteria not to be used in determining if the profile line should be included or excluded as an alert or exception.

Note: You must specify Y in the Threshold Exceptions field to enable Exception Elapsed.

Column name on View Profile panel: EELAPSED

Exception Delays

The delay time that, when exceeded, produces an exception for that unit of SQL activity. If the value of this field is zero, this criterion is not used in determining whether the profile line should be included or excluded as an alert or exception.

Note: You must specify Y in the Threshold Exceptions field to enable Exception Delays.

Column name on View Profile panel: EDELAYS

Exception Getpages

The number of getpages that, when exceeded, produces an exception for that unit of SQL activity. A value of zero causes these criteria not to be used in determining if the profile line should be included or excluded as an alert or exception.

Note: You must specify Y in the Threshold Exceptions field to enable Exception Getpages.

Column name on View Profile panel: EGETPAGES

Exception SQL Calls

The number of SQL calls that, when exceeded, produces an exception for that unit of SQL activity. An Exception SQL Calls value of zero causes this criterion not to be used in determining if the profile line should be included or excluded as an alert or exception.

Column name on View Profile panel: ESQLCALLS

Anomaly-based Exceptions

Anomaly-based Exceptions - CPU Anomalies

Indicates whether to generate exceptions for CPU anomalies.

Column name on View Profile panel: ECPUANM

Anomaly-based Exceptions - CPU Anomalies - Toleration Level

Indicates the CPU toleration that when exceeded produces an anomaly exception for CPU time for an SQL statement.

Column name on View Profile panel: ECPUTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_CPU_TIME_PROF_EXCP_TOLER

Anomaly-based Exceptions - Elapsed Anomalies

Indicates whether to generate exception for elapsed time anomalies.

Column name on View Profile panel: EELAPANM

Anomaly-based Exceptions - Elapsed Anomalies - Toleration Level

Indicates the elapsed time toleration that when exceeded produces an anomaly exception for elapsed time for an SQL statement.

Column name on View Profile panel: EELAPTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_ELAP_TIME_PROF_EXCP_TOLER

Anomaly-based Exceptions - Getpage Anomalies

Indicates whether to generate exceptions for getpage anomalies.

Column name on View Profile panel: EGETPANM

Anomaly-based Exceptions - Getpage Anomalies - Toleration Level

Indicates the getpage toleration that when exceeded produces an anomaly exception for getpage for an SQL statement.

Column name on View Profile panel: EGETPTOL
Offload table name: CQM_EXCEPTIONS
Offload column name: TOTAL_GETP_PROF_EXCP_TOLER

Generate SQLCODE Exceptions

Indicates whether SQLCODES generate exceptions. A non-zero value indicates exception processing is active and is checked on include lines. Valid values are Y (SQLCODES generate exceptions) and N (SQLCODES do not generate exceptions).

Notes:

The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters behave independently of one another.
The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters work together with the Exclude Exception SQLCODEs and Exclude Alert SQLCODEs parameters. The Exclude Exception SQLCODEs and Exclude Alert SQLCODEs parameters define whether specific SQLCODES (defined by the SQLCODE exclusion list) are excluded from exception/alert processing. The Generate SQLCODE Exceptions and Generate SQLCODE Alerts parameters act as high-level flags to turn on or off the generation of exceptions/alerts for SQLCODES (in general). So, for example:
- If you specify “Exclude Exception SQLCODEs = Y”, and specify -901 SQLCODE on the SQLCODE exclusion list, and specify “Generate SQLCODE Exceptions = Y”, then exceptions will be produced for all SQLCODEs except -901.
- If you specify “Exclude Exception SQLCODEs = Y”, and specify -901 SQLCODE on the SQLCODE exclusion list, and specify “Generate SQLCODE Exceptions = N”, then exceptions will not be produced for any SQLCODEs (in this case, the specification of -901 SQLCODE on the SQLCODE exclusion list is not really necessary since it will be enforced by the higher-level “Generate SQLCODE Exceptions = N” setting).
- If you specify “Exclude Exception SQLCODEs = N”, and specify “Generate SQLCODE Exceptions = Y”, then exceptions will be produced for all SQLCODEs.
- If you specify “Exclude Exception SQLCODEs = N”, and specify “Generate SQLCODE Exceptions = N”, then exceptions will not be produced for any SQLCODEs.

Column name on View Profile panel: SQLCEXCPS

Include Exception SQLCODEs

Indicates whether to specify a list of SQLCODES to include in exception processing. Valid values are Y (shows the inclusion SQLCODE list panel where the user can specify a list of SQLCODES to include in exception processing) and N (DEFAULT – do not specify SQLCODES to include in exception processing).

Column name on View Profile panel: ESQLCODEI

Exclude Exception SQLCODEs

Indicates whether to exclude SQLCODEs from exception processing. To exclude an SQLCODE from exception processing:

Specify an INCLUDE/EXCLUDE value of I.
Specify an Exclude Exception SQLCODES value of Y.
List the SQLCODE on the Exception SQLCODE Exclusion List.

Notes:

Every negative SQLCODE is treated as an exception unless explicitly excluded from processing.
The Exception SQLCODE Exclusion List appears whenever you specify an Exclude Exception SQLCODES value of Y.
The exclusion of SQLCODES does not prevent exceptions from being generated due to other criteria (such as GETPAGES, number of calls). SQLCODES are recorded as exceptions if a workload exceeds a threshold defined in the active monitoring profile. The ESQLCODE and ASQLCODE columns show SQLCODES if an SQL exception occurs. If a workload exceeds a threshold for an alert or for an exception, then Db2 Query Monitor posts the record that includes an SQLCODE, if one was generated, as long as the workload matches criteria in the active monitoring profile.

Column name on View Profile panel: ESQLCODES

Collect Exception Positive SQLCODEs

Indicates whether positive SQLCODEs are treated as exceptions in addition to negative SQLCODEs for the SQL activity identified by the selection criteria on the line.

Offload table name: CQM_EXCEPTIONS
Column name: EPOSSQLC

Exclude Alert SQLCODEs

Indicates whether to exclude SQLCODEs from alert processing. To exclude an SQLCODE from alert processing, specify an INCLUDE/EXCLUDE value of I, an Exclude Alert SQLCODES value of Y, and list the SQLCODE on the Alert SQLCODE Exclusion List.

Note: The Alert SQLCODE Exclusion List appears whenever you specify an Exclude Alert SQLCODES value of Y.

Column name on View Profile panel: ASQLCODES

Collect Alert Positive SQLCODEs

Indicates whether positive SQLCODEs are treated as alerts in addition to negative SQLCODEs for the SQL activity identified by the selection criteria on the line.

Offload table name: CQM_EXCEPTIONS
Column name: APOSSQLC

Discarded above (sigmas)

CPU Discard Level

Indicates the discard level that has to be crossed in order for an SQL statement's CPU time not to update the rolling mean and standard deviation.

Column name on View Profile panel: CPUDISC

Elapsed Discard Level

Indicates the discard level that has to be crossed in order for an SQL statement's elapsed time not to update the rolling mean and standard deviation.

Column name on View Profile panel: ELAPDISC

Getpage Discard Level

Indicates the discard level that has to be crossed in order for an SQL statement's number of getpages not to update the rolling mean and standard deviation.

Column name on View Profile panel: GETPDISC