Viewing and creating monitoring profiles - columns and fields (CAE Browser Client)

The following columns and fields are available for working with monitoring profiles in the CAE Browser Client.

Fields

Exclude QM Plans: Indicates whether or not the profile excludes QM plans from exceptions, alerts, and current activity. Valid values are Yes (or checked in the CAE Browser Client) to exclude the plans listed in the QM Plan1, QM Plan2, QM Plan3 fields, and No (or unchecked in the CAE Browser Client) to not exclude any QM plans.
QM Plan#1: The QM plan to be excluded from exceptions, alerts, and current activity. Wildcards can be used when specifying QM plans.
QM Plan#2: The QM plan to be excluded from exceptions, alerts, and current activity. Wildcards can be used when specifying QM plans.
QM Plan#3: The QM plan to be excluded from exceptions, alerts, and current activity. Wildcards can be used when specifying QM plans.
Workload: 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.
DB2®: The Db2 subsystem on which the activity occurred.
Plan: The Db2 plan name.
Program: The Db2 package or DBRM name.

Workload Filter tab

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.

Disable Summary Reporting

Indicates whether or not 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.

Gather Host Variables

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

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.

DB2 Subsystem

The Db2 subsystem.

Plan

The Db2 plan name.

Program

The Db2 package or DBRM name.

Auth ID

The primary authorization ID.

Job

The name of the job.

Connection

The connection ID.

Correlation Name

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

Correlation ID

The correlation ID.

Workstation User

The workstation user.

Workstation Tran

The workstation transaction.

Workstation Name

The workstation name.

Summaries tab

Exclude SQL Codes: Indicates whether or not SQL codes listed in the SQL Codes Excluded box are to be excluded from summary reporting.
SQL Codes Excluded: A list of SQL codes to be excluded from summary reporting if the Exclude SQL Codes field is set to True.
Include SQL Codes: Indicates whether or not SQL codes listed in the SQL Codes Included box are to be included from summary reporting.
SQL Codes Included: A list of SQL codes to be included in summary reporting if the Included SQL Codes field is set to True.
Generate Positive SQLCODEs: Indicates whether or not positive SQLCODEs are processed in addition to negative SQLCODEs for the SQL code summaries. This column is only checked for profile include lines.

Exceptions and Alerts tab

Threshold Exceptions

Check this box to generate threshold-based exceptions for CPU, Elapsed, Getpages, and SQL Calls. These options in this section define parameters used for the profile line's exception processing.

Threshold Exceptions - CPU: The Db2 CPU time that, when exceeded, produces an exception for that unit of SQL activity.; Note: You must select the Threshold Exceptions box to enable Threshold Exceptions - CPU.
Threshold Exceptions - 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 select the Threshold Exceptions box to enable Threshold Exceptions - Elapsed.
Threshold Exceptions - 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 select the Threshold Exceptions box to enable Threshold Exceptions - Getpages.
Threshold Exceptions - SQL Calls: The number of SQL calls that, when exceeded, produces an exception for the specified unit of SQL activity.; Note: You must select the Threshold Exceptions box to enable Threshold Exceptions - SQL Calls.
Exceptions - 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).

Generate SQLCODE Exceptions

Indicates whether or not SQLCODES generate exceptions. Valid values are True (SQLCODES generate exceptions) and False (SQLCODES do not generate exceptions).

Exclude SQL Codes

Indicates whether or not exceptions for SQLCODEs listed in the SQL Codes Excluded box are excluded by the profile line. This field is only active for profile include lines.

SQL Codes Excluded

A list of SQL codes to exclude from exceptions when Exclude SQL codes is set to True.

Include SQL Codes

Indicates whether or not SQL codes listed in the SQL Codes Included box are to be included from exceptions.

SQL Codes Included

A list of SQL codes to be included in exceptions if the Included SQL Codes field is set to True.

Generate Positive SQLCODEs

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

Anomaly-based Exceptions

Anomaly-based Exceptions - CPU

Check this box to generate exceptions for CPU anomalies for the specified sensitivity.

Anomaly-based Exceptions - CPU - Sensitivity

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

Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_CPU_TIME_PROF_EXCP_TOLER

Anomaly-based Exceptions - Elapsed

Check this box to generate exception for elapsed time anomalies for the specified sensitivity.

Anomaly-based Exceptions - Elapsed - Sensitivity

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

Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_ELAP_TIME_PROF_EXCP_TOLER

Anomaly-based Exceptions - Getpages

Check this box to generate exceptions for getpage anomalies for the specified sensitivity.

Anomaly-based Exceptions - Getpage - Sensitivity

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

Offload table name: CQM_EXCEPTIONS
Offload column name: TOTAL_GETP_PROF_EXCP_TOLER

Threshold Alerts

Check this box to generate threshold-based alerts for CPU, Elapsed, Getpages, and SQL Calls.

Threshold Alerts - CPU: The CPU time that when exceeded produces an alert for the workload.; Note: You must select the Threshold Alerts box to enable Threshold Alerts - CPU.
Threshold Alerts - Elapsed: The elapsed time that when exceeded produces an alert for the workload.; Note: You must select the Threshold Alerts box to enable Threshold Alerts - Elapsed.
Threshold Alerts - Getpages: The number of getpages that when exceeded produces an alert for the workload.; Note: You must select the Threshold Alerts box to enable Threshold Alerts - Getpages.
Threshold Alerts - SQL Calls: The number of SQL calls that when exceeded produces an alert for the workload.; Note: You must select the Threshold Alerts box to enable Threshold Alerts - SQL Calls.

Generate SQLCODE Alerts

Indicates whether or not SQLCODES generate alerts. Valid values are True (SQLCODES generate alerts) and False (SQLCODES do not generate alerts).

Exclude SQL Codes

Indicates whether or not alerts for SQLCODEs listed in the SQL Codes Excluded box are excluded by the profile line. This field is only active for profile include lines.

SQL Codes Excluded

A list of SQL codes to be excluded from alerts.

Include SQL Codes

Indicates whether or not SQL codes listed in the SQL Codes Included box are to be included from alerts.

SQL Codes Included

A list of SQL codes to be included in alerts if the Included SQL Codes field is set to True.

Generate Positive SQLCODEs

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

Anomaly-based Alerts

Anomaly-based Alerts - CPU

Check this box to generate alerts for CPU anomalies for the specified sensitivity.

Anomaly-based Alerts - CPU - Sensitivity

Indicates the CPU sensitivity (toleration) that when exceeded produces an anomaly alert for CPU time for an SQL statement.

Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_CPU_TIME_PROF_ALRT_TOLER

Anomaly-based Alerts - Elapsed

Check this box to generate alert for elapsed time anomalies for the specified sensitivity.

Anomaly-based Alerts - Elapsed - Sensitivity

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

Offload table name: CQM_EXCEPTIONS
Offload column name: DB2_ELAP_TIME_PROF_ALRT_TOLER

Anomaly-based Alerts - Getpages

Check this box to generate alerts for getpage anomalies for the specified sensitivity.

Anomaly-based Alerts - Getpage - Sensitivity

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

Offload table name: CQM_EXCEPTIONS
Offload column name: TOTAL_GETP_PROF_ALRT_TOLER

Discard Tolerance

Discard CPU data above: 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.
Discard Elapsed Time data above: 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.
Discard Getpages data above: 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.

Optional Keys tab

Override Optional Keys

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.

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.

Authids

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

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

Corrnum

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

Wsuser

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

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.

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.

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.

Conntype

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

Conname

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

SP

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

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.

Common Collection tab

Collect Object Data: Indicates whether or not 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.
Override CQMPARMS Settings: Indicates whether or not Db2 Query Monitor overrides CQMPARMS settings.
Collect Static SQL: Indicates whether or not data is collected for static SQL for the workload.
Collect Dynamic SQL: Indicates whether or not data is collected for dynamic SQL for the workload.
Ignore SQLCODE 100: Indicates whether or not to ignore positive SQLCODE 100.