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.

The profile information is displayed either as a series of fields or columns. The line panels (View Profile Line panel, Insert Profile Line panel, and Update Profile Line panel) display field names, such as Collect Dynamic SQL, and the profile panels (View Monitoring Profile panel, Update Monitoring Profile panel) display column names, such as COLLDSQL.

Specifically, the following profile information is displayed:

AUTHID

The primary authorization ID.

Column name on panels: AUTHID

Collect Dynamic SQL

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

Column name on panels: COLLDSQL

Collect Object Data

Indicates whether object data is collected for the workload.

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

Column name on panels: OBJECTS

Collect Static SQL

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

Column name on panels: 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 panels: SPOSSQLC

Connection ID

The connection ID.

Column name on panels: Conn

CORRID

The correlation ID.

Column name on panels: Corrid

CORRNAME

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

Column name on panels: CORRNAME

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 panels: SSID

Disable Summary Reporting

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

A value of Y is valid only for EXCLUDE profile lines. A 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 panels: DISABLE

Exclude Summary SQLCODES

Indicates whether SQL codes are included in View SQLCODEs displays. Valid values are:

Y: SQL codes are excluded in summary displays
N: SQL codes are not excluded in summary displays. (In other words, SQL codes are included in summary displays.)

If you specify Y, the Summary SQLCODE Exclusion List panel is displayed. On that panel, you can list the SQL codes that you want to exclude from the summary display.

Column name on panels: SSQLCODES

Ignore SQLCODE 100 for Read Activity

Indicates whether to ignore positive SQLCODE 100.

Offload table name: CQM_EXCEPTIONS
Column name on panels: 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 panels: SSQLCODEI

HOSTVARS

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

Column name on panels: 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 panels: INCL/EXCL

JOBNAME

The name of the job.

Column name on panels: Jobname

Name

The name of the monitoring profile.

OPTKEYS(AUTHIDS)

The AUTHIDS parameter enables collection of Db2 authorization IDs for summarization.

Column name on panels: OPTAUTHID

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

Column name on panels: OPTCALLS

OPTKEYS(CONNNAME)

The CONNNAME parameter enables collection of Db2 connection names for summarization.

Column name on panels: OPTCNAME

OPTKEYS(CONNTYPE)

The CONNTYPE parameter enables collection of individual Db2 connection types for summarization.

Column name on panels: OPTCTYPE

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

Column name on panels: 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®, 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.

Column name on panels: OPTCORRNM

OPTKEYS(CORRNUM)

The CORRNUM parameter enables collection of individual correlation numbers for summarization.

Column name on panels: OPTCRNUM

OPTKEYS(JOBNAME)

The JOBNAME parameter enables collection of individual z/OS batch job names for summarization.

Column name on panels: OPTJOBNM

OPTKEYS(PARALLEL)

The PARALLEL parameter enables collection of individual queries that use Db2 query parallelism. This information can then be used for summary reporting.

Column name on panels: not applicable

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 panels: OPTPTEXT

OPTKEYS(REQSITE)

The REQSITE parameter enables collection of the requesting site name for summarization.

Column name on panels: OPTREQST

OPTKEYS(SCHEMA)

The SCHEMA parameter enables collection of CURRENT SCHEMA special register values for summarization.

Column name on panels: OPTSCHEM

OPTKEYS(SP)

The SP parameter enables collection of stored procedures for summarization.

Column name on panels: OPTSP

OPTKEYS(TEXT)

The TEXT parameter enables collection of the unique pieces of SQL text for summarization. OPTKEYS(TEXT) and OPTKEYS(PTEXT) cannot be specified together.

Column name on panels: OPTTEXT

OPTKEYS(WORKLOAD)

The WORKLOAD parameter enables collection of workload names for summarization.

Column name on panels: OPTWRKLD

OPTKEYS(WSNAME)

The WSNAME parameter enables collection of workstation names for summarization.

Column name on panels: OPTWSNAME

OPTKEYS(WSTRAN)

The WSTRAN parameter enables collection of workstation transactions for summarization.

Column name on panels: OPTWSTRAN

OPTKEYS(WSUSER)

The WSUSER parameter enables collection of individual workstation user IDs for summarization.

Column name on panels: OPTWSUSER

Override OPTKEYS

Indicates whether to override the OPTKEYS settings in CQMPARMS. If you specify Y, you can override the OPTKEYS settings for individual OPTKEYS values, such as OPTKEYS(TEXT).

For more information about OPTKEYS, see OPTKEYS.

Column name on panels: 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 panels: OVRDPRMS

Plan Name

The Db2 plan name.

Column name on panels: Plan

Program Name

The Db2 package or DBRM name.

Column name on panels: 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

Column name on panels: WORKLOAD NAME

Workstation Name

The workstation name.

Column name on panels: WSNAME

Workstation Trans

The workstation transaction.

Column name on panels: WSTRAN

Workstation User

The workstation user.

Column name on panels: WSUSER

Alerts

The following profile information for alerts is also displayed on the profile panels:

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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: ASQLCALLS

Anomaly-based Alerts

Anomaly-based Alerts - CPU Anomalies

Indicates whether to generate alerts for CPU anomalies.

Column name on panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: ASQLCODEI

Exceptions

The following profile information for exceptions is also displayed on the profile panels:

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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: ESQLCALLS

Anomaly-based Exceptions

Anomaly-based Exceptions - CPU Anomalies

Indicates whether to generate exceptions for CPU anomalies.

Column name on panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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 panels: 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)

The following profile information for discard levels is also displayed on the profile panels:

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 panels: 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 panels: 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 panels: GETPDISC