Performance History Database tables and column descriptions

The following sections describe the Db2 Query Monitor Performance History Database tables and the columns they contain.

CQM_INTERVALS

The CQM_INTERVALS table defines the start and end times of each interval.

This table is uniquely identified by the columns: SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, and CQM_VERSION

SMFID: The z/OS® SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The date and time that an individual SQL statement started executing its first SQL call.
DB2_SUBSYSTEM: The Db2 subsystem.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
CQM_VERSION: The version of Db2 Query Monitor.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
INTERVAL_END: The date and time that an individual SQL statement finished executing its last SQL call.
INTERVAL_END_UTC: The UTC timestamp for the interval end.
OPTKEYS_AUTHIDS: Override the OPTKEYS(AUTHID) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_TEXT: Override the OPTKEYS(TEXT) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CORRID: Override the OPTKEYS(CORRID) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_WSUSER: Override the OPTKEYS(WSUSER) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_WSNAME: Override the OPTKEYS(WSNAME) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_WSTRAN: Override the OPTKEYS(WSTRAN) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CALLS: Override the OPTKEYS(CALLS) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CORRNAME: Override the OPTKEYS(CORRNAME) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CORRNUM: Override the OPTKEYS(CORRNUM) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_PTEXT: Override the OPTKEYS(PTEXT) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_SCHEMA: Override the OPTKEYS(SCHEMA) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CONNTYPE: Override the OPTKEYS(CONNTYPE) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_CONNNAME: Override the OPTKEYS(CONNNAME) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_SP: Override the OPTKEYS(SP) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_JOBNAME: Override the OPTKEYS(JOBNAME) parameter in CQMPARMS when OPTKEYS is set to Y.
OPTKEYS_PARALLEL: Override the OPTKEYS(JOBNAME) parameter in CQMPARMS when OPTKEYS is set to Y.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.
TOTAL_DB2_ELAPSED: The total accumulated elapsed time while executing within Db2.
TOTAL_DB2_CPU: The total of all TCB and SRB CPU time spent while executing in Db2.
TOTAL_GETPAGES: The number of GETPAGE requests. This count includes conditional, unconditional, successful, and unsuccessful requests.
TOTAL_DELAY_TIME: The total time spent waiting due to specific delay events.
TOTAL_SQL_CALLS: The total number of individual SQL calls executed by Db2.

CQM_SUMM_METRICS

The CQM_SUMM_METRICS table contains metrics data collected by Db2 Query Monitor.

A query for objects with the same SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, METRICS_TOKEN will yield all objects associated with the metric row.

A query for text with the same SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, and TEXT_TOKEN will yield all SQL text associated with the metric row.

SMFID

The z/OS SMFID.

CQM_SUBSYSTEM

The Db2 Query Monitor Subsystem ID that created the interval.

INTERVAL_NUMBER

The interval number.

INTERVAL_START

The timestamp for the interval start.

DB2_SUBSYSTEM

The Db2 subsystem on which the activity occurred.

DB2_VERSION

A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.

INTERVAL_START_UTC

The UTC timestamp for the interval start.

METRICS_TOKEN

The metrics token. When METRICS_TOKEN is combined with SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, and OBJECT_TOKEN, the combined key can be used to find metric row associated with this object.

METRICS_TIMESTAMP

The metrics timestamp.

METRICS_TIMESTAMP_UTC

The metrics timestamp.

OBJECT_TOKEN

The object token.

TEXT_TOKEN

The text token.

CONSISTENCY_TOKEN

The hexadecimal value of the consistency token.

PLAN

The Db2 plan name.

COLLECTION

The collection ID.

PROGRAM

The Db2 package or DBRM name.

PROGRAM_VERSION

The package version associated with the SQL statement.

SECTION

The section number.

STMT

The statement number assigned by PRECOMPILER.

TYPE

The type of object. Valid values are TABLE and INDEX.

CORRID

The correlation ID.

AUTHID

The primary authorization ID.

WORKSTATION_USER

The workstation user.

WORKSTATION_TRAN

The workstation transaction.

WORKSTATION_NAME

The workstation name.

IMPLICIT_QUALIFIER

The implicit qualifier.

DECLARE_STMT_NUM

The number assigned by the PRECOMPILER to declare statements.

DB2_CPU

The total amount of CPU time SQL calls spent in Db2.

DB2_ELAPSED

The accumulated elapsed time while executing within Db2.

SQL_CALLS

The total number of individual SQL calls executed by Db2.

TRG_DB2_ELAPSED

The total elapsed time consumed by the SQL activity while executing under the control of triggers. A trigger might invoke a stored procedure or a user-defined function. The time spent there is not included in this counter.

TRG_DB2_CPU

The accumulated CPU time consumed in Db2 by the SQL activity while executing under the control of triggers.

UDF_APP_ELAPSED

The elapsed time generated by a user-defined function as a result of executing the SQL Call. A user-defined function might initiate a trigger or invoke a stored procedure. The time spent there is not included in this counter.

UDF_APP_CPU

The accumulated CPU time used to satisfy user-defined function requests processed in the WLM address space.

UDF_DB2_ELAPSED

The accumulated elapsed time consumed in Db2 when processing SQL statements that were used by user-defined functions.

UDF_DB2_CPU

The CPU time consumed in Db2 by the user-defined function.

SP_APP_ELAPSED

The elapsed time generated by a stored procedure as a result of executing the SQL call. A stored procedure might initiate a trigger or invoke a user-defined function. The time spent there is not included in this counter.

SP_APP_CPU

The accumulated CPU time used to satisfy stored procedure requests processed in the WLM address space.

SP_DB2_ELAPSED

The accumulated elapsed time consumed in Db2 when processing SQL statements that were issued by stored procedures.

SP_DB2_CPU

The accumulated CPU time consumed in Db2 when processing SQL statements that were issued by stored procedures.

LOCK_LATCH_DLY

The accumulated lock and latch elapsed wait time for lock and latch suspensions.

SYNC_IO_DLY

The accumulated elapsed wait time for I/O. Synchronous I/O delays are further broken-down into Database I/O Delays and Log Write I/O Delays.

OTHER_READ_DLY

The accumulated wait time for read I/O.

OTHER_WRITE_DLY

The accumulated wait time for write I/O.

SERVTASK_SW_DLY

The accumulated wait time due to synchronous execution unit switch to Db2 services.

ARCHLOG_QS_DLY

The accumulated wait time for archive log quiesces.

ARCHLOG_RD_DLY

The accumulated wait time for archive log reads.

DRAIN_LOCK_DLY

The accumulated wait time for drain locks.

CLAIM_REL_DLY

The accumulated wait time for claim releases.

PAGE_LATCH_DLY

The accumulated wait time due to page latch contention.

SP_DLY

The accumulated wait time due to stored procedure contention.

NOTIFY_MSGS_DLY

The accumulated wait time due to notify messages.

GLOBAL_CONT_DLY

The accumulated elapsed wait time due to global contention for parent L-LOCKS.

LOG_WRITE_DLY

The accumulated elapsed wait time due to log writes.

OPEN_CLOSE_DLY

The Db2 service waits for OPEN/CLOSE DATASET.

SYSLOG_REC_DLY

The Db2 service waits for SYSLGRNG UPDATE.

EXTDEL_DEF_DLY

The Db2 service waits for EXTEND DATASET, DELETE DATASET, and DEFINE DATASET.

OTHER_SERVE_DLY

The Db2 service waits for HSM RECALL DATASET and DATASPACE MANAGER SERVICES.

ASYNCH_CFREQ_DLY

The accumulated wait time for IXLCACHE and IXLFCOMP asynchronous requests.

COMM_PH1WRT_DLY

The accumulated wait time for commit phase 1 I/O.

LLOCKS_CHILD_DLY

The accumulated wait time due to child object locks (pages, rows).

LLOCKS_OTHER_DLY

The accumulated wait time not due to child or parent object locks.

PLOCKS_PAGESET_DLY

The accumulated wait time due to physical locks for pagesets or partitions.

PLOCKS_PAGE_DLY

The accumulated wait time due to page contention.

PLOCKS_OTHER_DLY

The accumulated wait time for other physical contention.

UDF_SCHED_DLY

The accumulated wait time for scheduling user defined functions.

LOCK_LATCH_EVT

The accumulated lock and latch elapsed wait time (in seconds) for lock and latch suspensions.

SYNC_IO_EVT

The number of synchronous I/O events.

OTHER_READ_EVT

The number of I/O read events.

OTHER_WRITE_EVT

The number of I/O write events.

SERVTASK_SW_EVT

The number of synchronous switch to Db2 services to Db2 services which include OPEN/CLOSE data set, SYSLGRNG update, HSM recall data set, dataspace manager, define data set, extend data sets and delete data sets.

ARCHLOG_QS_EVT

The number of archive log quiese commands.

ARCHLOG_RD_EVT

The number of archive log reads.

DRAIN_LOCK_EVT

The number of drain lock events.

CLAIM_REL_EVT

The number of waits for claims to be released prior to a drain.

PAGE_LATCH_EVT

The number of page latch contentions.

SP_EVT

The number of times an SQL CALL statement was delayed waiting for the scheduling of a stored procedure.

NOTIFY_MSGS_EVT

The number of IRLM notify messages sent.

GLOBAL_CONT_EVT

The number of global contentions.

LOG_WRITE_EVT

The number of log write IO events detected.

OPEN_CLOSE_EVT

The number of OPEN/CLOSE data sets.

SYSLOG_REC_EVT

The number of SYSLGRNG updates.

EXTDEL_DEF_EVT

The number of extend, delete, or define data sets.

OTHER_SERVE_EVT

The number of other services which include HSM recall and dataspace manager.

ASYNCH_CFREQ_EVT

The number of IXLCACHE and IXLFCOMP asynchronous requests.

COMM_PH1WRT_EVT

The number of commit phase 1 I/O requests.

LLOCKS_CHILD_EVT

The number of child lock requests.

LLOCKS_OTHER_EVT

The number of other physical contentions.

PLOCKS_PAGESET_EVT

The number of physical locks requests for pagesets or partitions.

PLOCKS_PAGE_EVT

The number of page lock requests.

PLOCKS_OTHER_EVT

The number of other physical contention events.

UDF_SCHED_EVT

The number of user-defined functions scheduling requests.

LOCK_DEADLOCKS

The number of lock deadlocks.

LOCK_SUSPENSIONS

The number of suspensions due to locking conflicts.

LOCK_TIMEOUTS

The number of lock timeouts.

LATCH_SUSPENSIONS

The number of suspensions due to latch conflicts.

OTHER_SUSPENSIONS

The number of suspensions (aside from lock and latch suspensions).

LOCK_REQUESTS

The number of lock requests.

UNLOCK_REQUESTS

The number of unlock requests.

QUERY_REQUESTS

The number of query requests.

CHANGE_REQUESTS

The number of change requests.

OTHER_REQUESTS

The number of all other requests.

CLAIM_REQUESTS

The number of claim requests.

CLAIM_FAILED

The number of unsuccessful claim requests.

DRAIN_REQUESTS

The number of drain requests.

DRAIN_FAILED

The number of unsuccessful drain requests.

XES_LOCK_REQUESTS

The number of XES lock requests.

XES_CHG_REQUESTS

The number of XES change requests.

XES_UNLK_REQUESTS

The number of XES unlock requests.

IRLM_GLOBAL_CONT

The accumulated wait time due to global contention for parent L-locks.

XES_GLOBAL_CONT

The accumulated wait time due to XES global contention for parent L-locks.

FALSE_RES_CONT

The accumulated wait time due to false resource consumption.

INCOMPAT_RET_LOCK

The accumulated wait time due to incompatible retain locks.

SHARED_LOCK_ESC

The number of lock escalations to shared mode.

EXCL_LOCK_ESC

The number of lock escalations to exclusive mode.

LOCK_REQ_PLOCKS

The accumulated wait time due to parent object locks (database, table space, table, partition).

CHANGE_REQ_PLOCKS

The number of change requests for parent locks.

UNLOCK_REQ_PLOCKS

The number of unlock requests for parent locks.

NOTIFY_MSGS_SENT

The number of notify messages sent.

RID_USED

The number of times RID list (also called RID pool) processing is used.

RID_FAIL_NO_STOR

The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access).

RID_LIMIT_EXC

The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.

RID_MAX_DEGREE

UNUSED

RID_GROUPS_EXEC

The number of parallel groups executed.

RID_SEQ_CURSOR

The total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.

RID_SEQ_NO_SORT

The total number of parallel groups that fell back to sequential mode due to a lack of ESA sort storage.

RID_SEQ_NO_BUFF

The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.

RID_RAN_REDUCED

The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool.

RID_RAN_PLANNED

The total number of parallel groups that executed in the planned parallel degree. This field is raised by an increment of one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).

RID_PROC_ABENDS

The number of times a stored procedure terminated abnormally.

RID_CALL_TIMEOUT

The number of times a SQL call timed out waiting to be scheduled.

RID_CALL_REJECT

The number of times an SQL CALL statement was rejected due to the procedure being in the STOP ACTION(REJECT) state.

RID_SEQ_ENC_SERVE

The total number of parallel groups that executed in sequential mode due to the unavailability of MVS™ ESA enclave services.

RID_ONE_DB2_CONO

The total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO. When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a Db2 subsystem with COORDINATOR=YES, but is run on a Db2 subsystem with COORDINATOR=NO.

RID_ONE_DB2_ISO

The total number of parallel groups executed on a single Db2 subsystem due to repeatable-read or read-stability isolation.

RID_REOPTIMIZED

The number of times the access path for static and dynamic SQL Queries were re-optimized at run time.

RID_PREP_MATCHED

The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.

RID_PREP_NOMATCH

The number of times that Db2 searched the prepared statement cache but could not find a suitable prepared statement.

RID_IMP_PREPS

The number of implicit prepares (prepares that occur when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache and the application plan or package is bound with KEEPDYNAMIC YES).

RID_PREP_CACHE

The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.

RID_CACHE_LIM_EXC

The number of times statements are invalidated in the local dynamic SQL cache because the MAXKEEPD limit has been reached and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.

RID_PREP_PURGED

The number of times statements are invalidated in the local dynamic SQL cache because of SQL DDL or updated RUNSTATS information and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.

RID_MAX_STOR_LOB

UNUSED

RID_ROWID_DIRECT

The number of times that direct access was successful.

RID_ROWID_INDEX

The number of times that direct row access failed and an index was used to find a record.

RID_TS_SCANNED

The number of times that an attempt to use direct row access reverted to using a table space scan because Db2 was unable to use a matching index scan.

RID_STMT_TRIGGER

The number of times a statement trigger was activated.

RID_ROW_TRIGGER

The number of times a row trigger was activated.

RID_ERROR_TRIGGER

The number of times an SQL error occurred during the execution of a triggered action. This includes errors that occur in user-defined functions or stored procedures that are called from triggers and that pass back a negative SQLCODE.

MAX_CASCADE_LEVEL

The maximum cascade level.

TOTAL_GETPAGES

The accumulated getpage requests.

GETPAGES

The number of getpage requests. This includes conditional, unconditional, successful, and unsuccessful requests. The GETPAGE information for a program reported on the activity summary might not add up to the sum of object detail GETPAGEs of that program due to the trade-off between optimizing the collector for efficiency and increasing the level of detail in some statistics.

BUFFER_UPDATES

The number of buffer pages updated.

SYNC_READS

The number of synchronous read I/O for the object.

SEQ_PREFETCH

The number of SEQ PREFETCH requested for the object.

SYNC_WRITES

The number of synchronous write I/O for the object.

LIST_PREFETCH

The number of LIST PREFETCH requests for the object.

DYNAMIC_PREFETCH

The number of DYNAMIC PREFETCH requested for the object.

HPOOL_READS

The number of successful hiperpool reads.

HPOOL_READS_FAIL

The number of hiperpool reads that failed.

HPOOL_WRITES

The number of successful hiperpool writes.

HPOOL_WRITES_FAIL

The number of hiperpool writes that failed.

GETPAGES_FAILED

The number of times a parallel query failed to find a page in the buffer pool.

ASYNCH_PAGES_READ

The number of asynchronous pages read by prefetch.

ASYNCH_HPOOL_PAGES

The number of pages found and moved from a hiperpool to a virtual buffer by prefetch.

INTERVAL_END

The timestamp identifying the end time of the interval.

INTERVAL_END_UTC

The timestamp identifying the UTC end time of the interval.

ZIIP_CPU_TIME

The amount of CPU time accumulated while executing in Db2 on a zIIP processor.

DB2_GROUP_NAME

The Db2 subsystem ID or Db2 data sharing group.

STATEMENT_COUNT

The number of SQL calls that occurred for the displayed activity.

EXECUTION_COUNT

The number of times the SQL statement has been executed. Db2 Query Monitor does not update the execution count for SQL that does not have a CLOSE call (for example, if the SQL ends with a negative SQLCODE or the SQL is canceled). In this case, the execution count of the SQL is shown as zero.

CURRENT_SCHEMA

The current schema that executed the SQL.

ACCELERATOR

The name of the IBM® Db2 Analytics Accelerator for z/OS where the activity might run. When the ACCELERATOR column is blank for a line item, it means that no queries for that line item were offloaded to the accelerator.

DB2_VERSION_LONG

A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.

ACCEL_ELIGIBLE_ELAPSED

The amount of elapsed time that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.

ACCEL_ELIGIBLE_CPU

The amount of CPU time spent on a non-specialty engine that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.

ACCEL_ELIGIBLE_ZIIP

The amount of CPU time spent on a specialty engine that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.

CORRNAME

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

ORIGINAL_TEXT_TOKEN

The text token of the unstripped SQL text.

DATABASE_IO_DLY

Accumulated elapsed wait time due to database IO.

UPDATE_COMMIT_DLY

Accumulated elapsed wait time due to update commits.

LLOCK_PARENT_DLY

Accumulated elapsed wait time due to locks requested by parent processes.

DATABASE_IO_EVT

Number of database IO events.

UPDATE_COMMIT_EVT

Number of update commit events.

LLOCK_PARENT_EVT

Number of parent lock requests.

CORRNUM

The correlation number which is set based on the default OMEGAMON® parsing of the CORRID value.

STMT_ID

The SQL statement number assigned by the Db2 pre-compiler to an individual SQL call.

CONNECTION_NAME

The connection name.

CONNECTION_TYPE

The Db2 connection type. Valid connection types include:

1 - TSO: TSO Foreground and Background
2 - DB2CALL: Db2 Call Attach
3 - IMSDLI: DL/I Batch
4 - CICS: CICS Attach
5 - IMSBMP: IMS Attach BMP
6 - IMSMMP: IMS Attach MPP
7 - DB2PRIV: Db2 Private Protocol
8 - DRDA: DRDA Protocol
9 - IMSCTL: IMS Control Region
A - IMSTRAN: IMS Transaction BMP
B - UTILITY: Db2 Utilities
C - RRSAF: RRSAF Attach

JOBNAME

The name of the job.

PARALLEL

Indicates whether or not the SQL activity was formulated using Db2 query parallelism.

SP_CREATOR

The creator of the stored procedure.

SP_NAME

The name of the stored procedure.

LOG_BYTES_WRITTEN

The number of log bytes written.

LOG_RECORDS_WRITTEN

The total number of log records written.

DB2_ELAPSED_COUNT

The number of statements that were counted in the interval that were incorporated into the mean and sum of delta squares calculations for Db2 Elapsed Time

DB2_ELAPSED_MEAN

The mean of the Db2 Elapsed Time for the interval

DB2_ELAPSED_DELTA_SQR

The sum of delta squares for Db2 Elapsed time for the interval

DB2_CPU_COUNT

The number of statements that were counted in the interval that were incorporated into the mean and sum of delta squares calculations for Db2 CPU Time

DB2_CPU_MEAN

The mean of the Db2 CPU Time for the interval

DB2_CPU_DELTA_SQR

The sum of delta squares for Db2 CPU time for the interval

GETPAGES_D_COUNT

The number of statements that were counted in the interval that were incorporated into the mean and sum of delta squares calculations for Getpages

GETPAGES_D_MEAN

The mean of the Getpages for the interval

GETPAGES_D_DELTA_SQR

The sum of delta squares for Getpages for the interval

FIRST_ELAPSED_MEAN

The first rolling average value for Db2 Elapsed time for the interval

FIRST_ELAPSED_VARIANCE

The first rolling variance value for Db2 Elapsed time for the interval

FIRST_CPU_MEAN

The first rolling average value for Db2 CPU time for the interval

FIRST_CPU_VARIANCE

The first rolling variance value for Db2 CPU time for the interval

FIRST_GETPAGES_MEAN

The first rolling average value for Getpages for the interval

FIRST_GETPAGES_VARIANCE

The first rolling variance value for Getpages for the interval

FIRST_INSTANCE_TIMESTAMP

The timestamp of the first SQL statement to update the rolling mean and variance for the interval

LAST_ELAPSED_MEAN

The last rolling average value for Db2 Elapsed time for the interval

LAST_ELAPSED_VARIANCE

The last rolling variance value for Db2 Elapsed time for the interval

LAST_CPU_MEAN

The last rolling average value for Db2 CPU time for the interval

LAST_CPU_VARIANCE

The last rolling variance value for Db2 CPU time for the interval

LAST_GETPAGES_MEAN

The last rolling average value for Getpages for the interval

LAST_GETPAGES_VARIANCE

The last rolling variance value for Getpages for the interval

LAST_INSTANCE_TIMESTAMP

The timestamp of the last SQL statement to update the rolling mean and variance for the interval

CQM_SUMM_OBJECTS

The CQM_SUMM_OBJECTS table contains information about objects.

This table is associated with a metric row by the columns: INTERVAL_NUMBER, METRICS_TOKEN, DBID, OBID, PSID, OBJECT_TOKEN

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
METRICS_TOKEN: The metrics token. When combined with SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, and OBJECT_TOKEN, the METRICS_TOKEN can be used to find metric row associated with this object.
METRICS_TIMESTAMP: The metrics timestamp.
METRICS_TIMESTAMP_UTC: The metrics timestamp.
OBJECT_TOKEN: The object token.
DBID: The database ID.
OBID: The object ID.
PSID: The pageset ID.
BUFFERPOOL_NORM: The normalized bufferpool number (BP0, BP16K0).
BUFFERPOOL_NUM: The bufferpool number.
TYPE: The object type. Valid values are I (index) and T (table).
DATABASE_NAME: The database name.
PAGESET_NAME: The pageset name.
OBJECT_CREATOR: The object creator.
OBJECT_NAME: The object name.
TBCREATOR: The table creator. For indexes, it is the table creator for the table associated with the index.
TBNAME: The name of the table. For indexes, it is the table name of the table associated with the index.
GETPAGE_ELAPSED: The accumulated elapsed time for getpage requests.
GETPAGES: The number of getpages issued which includes conditional, non-conditional, successful and unsuccessful requests.
BUFFER_UPDATES: The number of buffer updates.
SYNC_READS: The number of synchronous read I/O requests.
SEQ_PREFETCH: The number of sequential prefetches.
SYNC_WRITES: The number of synchronous write requests.
LIST_PREFETCH: The number of list prefetch requests.
DYNAMIC_PREFETCH: The number of dynamic prefetch requests.
HPOOL_READS: The number of successful hiperpool reads.
HPOOL_READS_FAIL: The number of hiperpool reads that failed.
HPOOL_WRITES: The number of successful hiperpool writes.
HPOOL_WRITES_FAIL: The number of hiperpool writes that failed.
GETPAGES_FAILED: The number of getpages that failed.
ASYNCH_PAGES_READ: The number of asynchronous pages read by prefetch.
ASYNCH_HPOOL_PAGES: The number of pages found and moved from a hiperpool to a virtual buffer by prefetch.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_SUBSYSTEM: The Db2 subsystem on which the activity occurred.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
LOCK_EVENTS: The number of lock events detected for the object.
LOCK_DELAYS: The total amount of lock delay time for the object spent in Db2.
LATCH_EVENTS: The number of latch events detected for the object.
LATCH_DELAYS: The total amount of latch delay time for the object spent in Db2.
SYNC_IO_EVENTS: The number of synchronous IO events detected for the object.
SYNC_IO_DELAYS: The total amount of Sync IO delay time for the object spent in Db2.
LOG_WRITE_EVENTS: The number of log write IO events detected.
LOG_WRITE_DELAYS: The total amount of log write IO delay time for the object spent in Db2.
PAGE_LATCH_EVENTS: The number of page latch events detected for the object.
PAGE_LATCH_DELAYS: The total amount of page latch delay time for the object spent in Db2.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.

CQM_SUMM_TEXT

The CQM_SUMM_TEXT table contains summary information about SQL text.

This table is associated with metrics by SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, TEXT_TOKEN. When combined with SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, and INTERVAL_START, the combined key can be used to find the metric row associated with this SQL text.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_END: The timestamp identifying the end time of the interval.
TEXT_TOKEN: The text token.
TEXT_TIMESTAMP: The timestamp for the SQL text.
CCSID: The encoding CCSID for the SQL text.
ROW_ID: The system generated row ID.
SQLTEXT: The abbreviated view of the SQL text.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
STRIPPED_TEXT: Indicates whether or not the text is stripped text.

CQM_EXCEPTIONS

The CQM_EXCEPTIONS table holds information about exceptions generated based on the active monitoring profile.

A query for exception calls, host variables, or exception objects with the SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, EXCEPTION_TOKEN will yield associated data in other tables.

A query for text with the same SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION, and TEXT_TOKEN will yield all SQL text associated with the exception.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
DB2_SUBSYSTEM: The Db2 subsystem on which the activity occurred.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
EXCEPTION_TOKEN: The exception token.
EXCEPTION_TIMESTMP: The exception timestamp.
EXCEPTION_TIMESTMP_UTC: The exception timestamp in UTC.
START_TIME: The date and time that an individual SQL statement started executing its first SQL call.
START_TIME_UTC: The UTC date and time that an individual SQL statement started executing its first SQL call.
END_TIME: The date and time that an individual SQL statement finished executing its last SQL call.
END_TIME_UTC: The UTC date and time that an individual SQL statement started executing its first SQL call.
TEXT_TOKEN: The text token.
THREAD_TOKEN: The thread token. A thread token uniquely identifies an individual connection to a Db2 subsystem.
CONSISTENCY_TOKEN: The hexadecimal consistency token.
ACCOUNTING_TOKEN: The accounting token.
ORIGNINATING_TOKEN: The thread token assigned to the thread that generated the parallel task(s) on the parallelism coordinator Db2 subsystem.
PLAN: The plan name.
COLLECTION: The collection ID.
PROGRAM: The Db2 package or DBRM name.
PROGRAM_VERSION: The package version associated with the SQL statement.
SECTION: The section number.
CORRID: The correlation ID.
AUTHID: The primary authorization ID.
WORKSTATION_USER: The workstation user.
WORKSTATION_TRAN: The workstation transaction.
WORKSTATION_NAME: The workstation name.
IMPLICIT_QUALIFIER: The implicit qualifier.
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.
JOBNAME: The name of the job.
CONNECTION: The connection name.
CONNECTION_TYPE: The Db2 connection type. Valid connection types include: 1 - TSO (TSO Foreground and Background), 2 - DB2CALL (Db2 Call Attach), 3 - IMSDLI (DL/I Batch), 4 - CICS (CICS Attach), 5 - IMSBMP (IMS Attach BMP), 6 - IMSMMP (IMS Attach MPP), 7 - DB2PRIV (Db2 Private Protocol), 8 - DRDA (DRDA Protocol), 9 - IMSCTL (IMS Control Region), A - IMSTRAN (IMS Transaction BMP), B - UTILITY (Db2 Utilities), and C - RRSAF (RRSAF Attach).
NETID: The network identifier.
LUNAME: The logical unit name.
UNIQUESS_VALUE: The uniqueness value.
COMMIT_COUNT: The number of commits that were issued.
ACE_ADDRESS: The ACE address.
ORIGINATING_SSID: The originating Db2 subsystem ID.
ORIGINATING_MEMBER: The Db2 subsystem from which the parallel activity originated.
REQ_SITE_NAME: The requesting site name.
CURSOR_NAME: The cursor name.
SQLCAID: The SQLCA eyecatcher.
SQLCABC: The length of the SQLCA.
CURSOR_NAME: The cursor name.
SQLCAID: The SQLCA eyecatcher.
SQLCABC: The length of the SQLCA.
SQLCODE: The SQL return code issued by Db2.
SQLERRM: The SQL error message.
SQLERRP: The SQL diagnostic information.
SQLERRD1: Indicates either an internal error code or the number of rows in the result set after the cursor position is at the end (SQLCODE=+100).
SQLERRD2: An internal error code.
SQLERRD3: Indicates the reason code for timeout or deadlock for SQLCODES -911 or -913 or it contains the number of rows affected by an INSERT, UPDATE, or DELETE but not for a cascading delete.
SQLERRD4: A floating point number indicative of the amount of resources used.
SQLERRD5: The position or column number for a syntax error during a PREPARE or EXECUTE IMMEDIATE statement.
SQLERRD6: An internal error code.
SQLWARN1: An SQL warning. Valid values are W (a value was truncated when assigned to a HOSTVAR), N (non-scrollable cursor), and S (scrollable cursor).
SQLWARN2: An SQL warning. Valid values are W (null values were excluded but it is not necessarily set for a MIN function since the result is not dependent on null values).
SQLWARN2: An SQL warning. Valid values are W (null values were excluded but it is not necessarily set for a MIN function since the result is not dependent on null values).
SQLWARN3: An SQL warning. Valid values are W (the number of result columns is larger than the number of HOSTVARS) and Z (fewer locators were provided in the associated locators statement than the stored procedure returned).
SQLWARN4: An SQL warning. Valid values are W (a prepared UPDATE or DELETE statement does not include a WHERE clause), I (a scrollable insensitive cursor), S (a scrollable sensitive cursor), and blank (the cursor is not scrollable).
SQLWARN5: An SQL warning. Valid values are W (the SQL statement was invalid for the Db2 subsystem), 1 (the cursor is read only), 2 (the cursor is read and delete), 4 (the cursor is read, delete, and update).
SQLWARN6: An SQL warning. Valid values are W (the addition of a date or timestamp yields an invalid date). The code indicates the date was reset to a valid date.
SQLWARN7: An SQL warning. Valid values are W (one or more nonzero digits were eliminated from fractional parts of a number as a result of a decimal multiply or divide).
SQLWARN8: An SQL warning. Valid values are W (a character could not be converted and was replaced with a substitute character).
SQLWARN9: An SQL warning. Valid values are W (arithmetic exceptions were ignored during count or count big processing) or Z (the stored procedure returned multiple result sets).
SQLWARNA: An SQL warning. Valid values are W (at least one character field of the SQLCA or SQLDA names or labels is invalid due to a character conversion error).
SQLSTATE: The return code for the outcome of the most recent execution of an SQL statement.
LAST_SQLCODE: The last SQL return code.
EXCEPTION_SQLCODE: The SQL code that raised an exception condition. Exception criteria are defined within a monitoring profile.
EXCEPTION_CPU_TIME: The Db2 CPU time that, when exceeded, produces an exception for that unit of SQL activity.
EXCEPTION_ELAPSED_TIME: 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.
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.
EXCEPTION_SQLCALLS: The number of SQL calls that, when exceeded, produces an exception for that unit of SQL activity. A 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.
EXCEPTION_NEGATIVE_SQLCODE: The exception SQLCODE.
ALERT_SQLCODE: The SQL code that raised an alert condition. Alert criteria are defined within a monitoring profile.
ALERT_CPU_TIME: The CPU time that when exceeded produces an alert for the workload.
ALERT_ELAPSED_TIME: The elapsed time that when exceeded produces an alert for the workload.
ALERT_GETPAGES: The number of getpages that when exceeded produces an alert for the workload.
ALERT_SQLCALLS: The number of SQL calls that when exceeded produces an alert for the workload.
ALERT_NEGATIVE_SQLCODE: The alert SQLCODE.
DB2_CPU: The accumulated total of all TCB and SRB CPU time spent executing in Db2.
DB2_ELAPSED: The accumulated elapsed time while executing within Db2.
SQL_CALLS: The total number of individual SQL calls executed by Db2.
TRG_DB2_ELAPSED: The total elapsed time consumed by the SQL activity while executing under the control of triggers. A trigger might invoke a stored procedure or a user-defined function. The time spent there is not included in this counter.
TRG_DB2_CPU: The accumulated CPU time consumed in Db2 by the SQL activity while executing under the control of triggers.
UDF_APP_ELAPSED: The total elapsed time spent by the SQL activity in user-defined functions. A user-defined function might invoke a stored procedure or initiate a trigger. The time spent there is not included in this counter.
UDF_APP_CPU: The accumulated CPU time used to satisfy user-defined function requests processed in WLM address space.
UDF_DB2_ELAPSED: The elapsed time consumed in Db2 by the user-defined function.
UDF_DB2_CPU: The CPU time consumed in Db2 by the user-defined function.
SP_APP_ELAPSED: The total elapsed time spent by the SQL activity in stored procedures. A stored procedure might initiate a trigger or invoke a user-defined function. The time spent there is not included in this counter.
SP_APP_CPU: The total CPU time spent by the SQL activity in stored procedures.
SP_DB2_ELAPSED: The TCB time accumulated in Db2 for processing SQL statements issued by stored procedures.
SP_DB2_CPU: The CPU time accumulated in Db2 for processing SQL statements issued by stored procedures.
LOCK_LATCH_DLY: The accumulated lock and latch elapsed wait time for lock and latch suspensions.
SYNC_IO_DLY: The accumulated elapsed wait time for I/O. Synchronous I/O delays are further broken-down into Database I/O Delays and Log Write I/O Delays.
OTHER_READ_DLY: The accumulated wait time for read I/O.
OTHER_WRITE_DLY: The accumulated wait time for write I/O.
SERVTASK_SW_DLY: The accumulated wait time due to synchronous execution unit switch to Db2 services.
ARCHLOG_QS_DLY: The accumulated wait time for archive log quiesces.
ARCHLOG_RD_DLY: The accumulated wait time for archive log reads.
DRAIN_LOCK_DLY: The accumulated wait time for drain locks.
CLAIM_REL_DLY: The accumulated wait time for claim releases.
PAGE_LATCH_DLY: The accumulated wait time due to page latch contention.
SP_DLY: The accumulated wait time due to stored procedure contention.
NOTIFY_MSGS_DLY: The accumulated wait time due to notify messages.
GLOBAL_CONT_DLY: The accumulated elapsed wait time due to global contention for parent L-LOCKS.
LOG_WRITE_DLY: The accumulated elapsed wait time due to log writes.
OPEN_CLOSE_DLY: The Db2 service waits for OPEN/CLOSE DATASET.
SYSLOG_REC_DLY: The Db2 service waits for SYSLGRNG UPDATE.
EXTDEL_DEF_DLY: The Db2 service waits for EXTEND DATASET, DELETE DATASET, and DEFINE DATASET.
OTHER_SERVE_DLY: The Db2 service waits for HSM RECALL DATASET and DATASPACE MANAGER SERVICES.
ASYNCH_CFREQ_DLY: Accumulated wait time for IXLCACHE and IXLFCOMP asynchronous requests.
COMM_PH1WRT_DLY: Accumulated wait time for commit phase 1 I/O.
LLOCKS_CHILD_DLY: The accumulated wait time due to child object locks (pages, rows).
LLOCKS_OTHER_DLY: The accumulated wait time not due to child or parent object locks.
PLOCKS_PAGESET_DLY: The accumulated wait time due to physical locks for pagesets or partitions.
PLOCKS_PAGE_DLY: The accumulated wait time due to page contention.
PLOCKS_OTHER_DLY: The accumulated wait time for other physical contention.
UDF_SCHED_DLY: The accumulated wait time for scheduling user defined functions.
LOCK_LATCH_EVT: The accumulated lock and latch elapsed wait time (in seconds) for lock and latch suspensions.
SYNC_IO_EVT: The number of synchronous I/O events.
OTHER_READ_EVT: The number of I/O read events.
OTHER_WRITE_EVT: The number of I/O write events.
SERVTASK_SW_EVT: The number of synchronous switch to Db2 services to Db2 services which include OPEN/CLOSE data set, SYSLGRNG update, HSM recall data set, dataspace manager, define data set, extend data sets and delete data sets.
ARCHLOG_QS_EVT: The number of archive log quiesce commands.
ARCHLOG_RD_EVT: The number of archive log reads.
DRAIN_LOCK_EVT: The number of drain lock events.
CLAIM_REL_EVT: The number of waits for claims to be released prior to a drain.
PAGE_LATCH_EVT: The number of page latch contentions.
SP_EVT: The number of times an SQL CALL statement was delayed waiting for the scheduling of a stored procedure.
NOTIFY_MSGS_EVT: The number of IRLM notify messages sent.
GLOBAL_CONT_EVT: The number of global contentions.
LOG_WRITE_EVT: The number of log write IO events detected.
OPEN_CLOSE_EVT: The number of OPEN/CLOSE data sets.
SYSLOG_REC_EVT: The number of SYSLGRNG updates.
EXTDEL_DEF_EVT: The number of extend, delete, or define data sets.
OTHER_SERVE_EVT: The number of other services which include HSM recall and dataspace manager.
ASYNCH_CFREQ_EVT: The number of IXLCACHE and IXLFCOMP asynchronous requests.
COMM_PH1WRT_EVT: The number of commit phase 1 I/O requests.
LLOCKS_CHILD_EVT: The number of child lock requests.
LLOCKS_OTHER_EVT: The number of other physical contentions.
PLOCKS_PAGESET_EVT: The number of physical locks requests for pagesets or partitions.
PLOCKS_PAGE_EVT: The number of page lock requests.
PLOCKS_OTHER_EVT: The number of other physical contention events.
UDF_SCHED_EVT: The number of user-defined functions scheduling requests.
LOCK_DEADLOCKS: The number of lock deadlocks.
LOCK_SUSPENSIONS: The number of suspensions due to locking conflicts.
LOCK_TIMEOUTS: The number of lock timeouts.
LATCH_SUSPENSIONS: The number of suspensions due to latch conflicts.
OTHER_SUSPENSIONS: The number of suspensions (aside from lock and latch suspensions).
LOCK_REQUESTS: The number of lock requests.
UNLOCK_REQUESTS: The number of unlock requests.
QUERY_REQUESTS: The number of query requests.
CHANGE_REQUESTS: The number of change requests.
OTHER_REQUESTS: The number of all other requests.
CLAIM_REQUESTS: The number of claim requests.
CLAIM_FAILED: The number of unsuccessful claim requests.
DRAIN_REQUESTS: The number of drain requests.
DRAIN_FAILED: The number of unsuccessful drain requests.
XES_LOCK_REQUESTS: The number of XES lock requests.
XES_CHG_REQUESTS: The number of XES change requests.
XES_UNLK_REQUESTS: The number of XES unlock requests.
IRLM_GLOBAL_CONT: The accumulated wait time due to global contention for parent L-locks.
XES_GLOBAL_CONT: The accumulated wait time due to XES global contention for parent L-locks.
FALSE_RES_CONT: The accumulated wait time due to false resource consumption.
INCOMPAT_RET_LOCK: The accumulated wait time due to incompatible retain locks.
SHARED_LOCK_ESC: The number of lock escalations to shared mode.
EXCL_LOCK_ESC: The number of lock escalations to exclusive mode.
LOCK_REQ_PLOCKS: The accumulated wait time due to parent object locks (database, table space, table, partition).
CHANGE_REQ_PLOCKS: The number of change requests for parent locks.
UNLOCK_REQ_PLOCKS: The number of unlock requests for parent locks.
NOTIFY_MSGS_SENT: The number of notify messages sent.
RID_USED: The number of times RID list (also called RID pool) processing is used.
RID_FAIL_NO_STOR: The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access).
RID_LIMIT_EXC: The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.
RID_MAX_DEGREE: UNUSED
RID_GROUPS_EXEC: The number of parallel groups executed.
RID_SEQ_CURSOR: The total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.
RID_SEQ_NO_SORT: The total number of parallel groups that fell back to sequential mode due to a lack of ESA sort storage.
RID_SEQ_NO_BUFF: The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.
RID_RAN_REDUCED: The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool.
RID_RAN_PLANNED: The total number of parallel groups that executed in the planned parallel degree. This field is raised by an increment of one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).
RID_PROC_ABENDS: The number of times a stored procedure terminated abnormally.
RID_CALL_TIMEOUT: The number of times a SQL call timed out waiting to be scheduled.
RID_CALL_REJECT: The number of times an SQL CALL statement was rejected due to the procedure being in the STOP ACTION(REJECT) state.
RID_SEQ_ENC_SERVE: The total number of parallel groups that executed in sequential mode due to the unavailability of MVS ESA enclave services.
RID_ONE_DB2_CONO: The total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO. When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a Db2 subsystem with COORDINATOR=YES, but is run on a Db2 subsystem with COORDINATOR=NO.
RID_ONE_DB2_ISO: The total number of parallel groups executed on a single Db2 subsystem due to repeatable-read or read-stability isolation.
RID_REOPTIMIZED: The number of times the access path for static and dynamic SQL queries were re-optimized at run time.
RID_PREP_MATCHED: The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.
RID_PREP_NOMATCH: The number of times that Db2 searched the prepared statement cache but could not find a suitable prepared statement.
RID_IMP_PREPS: The number of implicit prepares (prepares that occur when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache and the application plan or package is bound with KEEPDYNAMIC YES).
RID_PREP_CACHE: The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.
RID_CACHE_LIM_EXC: The number of times statements are invalidated in the local dynamic SQL cache because the MAXKEEPD limit has been reached and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.
RID_PREP_PURGED: The number of times statements are invalidated in the local dynamic SQL cache because of SQL DDL or updated RUNSTATS information and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.
RID_MAX_STOR_LOB: UNUSED
RID_ROWID_DIRECT: The number of times that direct access was successful.
RID_ROWID_INDEX: The number of times that direct row access failed and an index was used to find a record.
RID_TS_SCANNED: The number of times that an attempt to use direct row access reverted to using a table space scan because Db2 was unable to use a matching index scan.
RID_STMT_TRIGGER: The number of times a statement trigger was activated.
RID_ROW_TRIGGER: The number of times a row trigger was activated.
RID_ERROR_TRIGGER: The number of times an SQL error occurred during the execution of a triggered action. This includes errors that occur in user-defined functions or stored procedures that are called from triggers and that pass back a negative SQLCODE.
MAX_CASCADE_LEVEL: The maximum cascade level.
GETPAGES: The number of getpage requests. This includes conditional, unconditional, successful, and unsuccessful requests. The GETPAGE information for a program reported on the activity summary might not add up to the sum of object detail GETPAGEs of that program due to the trade-off between optimizing the collector for efficiency and increasing the level of detail in some statistics.
BUFFER_UPDATES: The number of buffer pages updated.
SYNC_READS: The number of synchronous read I/O for the object.
SEQ_PREFETCH: The number of SEQ PREFETCH requested for the object.
SYNC_WRITES: The number of synchronous write I/O for the object.
LIST_PREFETCH: The number of LIST PREFETCH requests for the object.
DYNAMIC_PREFETCH: The number of DYNAMIC PREFETCH requested for the object.
HPOOL_READS: The number of successful hiperpool reads.
HPOOL_READS_FAIL: The number of hiperpool reads that failed.
HPOOL_WRITES: The number of successful hiperpool writes.
HPOOL_WRITES_FAIL: The number of hiperpool writes that failed.
GETPAGES_FAILED: The number of times a parallel query failed to find a page in the buffer pool.
ASYNCH_PAGES_READ: The number of asynchronous pages read by prefetch.
ASYNCH_HPOOL_PAGES: The number of pages found and moved from a hiperpool to a virtual buffer by prefetch.
CLIENT_ENDUSER: The client enduser.
CORRNAME: The correlation ID adjusted by the conventions used by IMS and CICS.
CORRNUM: The correlation number which is set based on the default OMEGAMON parsing of the CORRID value.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
ZIIP_CPU_TIME: The amount of CPU time accumulated while executing in Db2 on a zIIP processor.
STRIPPED_TEXT_TOKEN: The stripped text token.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.
CURRENT_SCHEMA: The current schema that executed the SQL.
DATABASE_IO_DLY: Accumulated elapsed wait time due to database IO.
UPDATE_COMMIT_DLY: Accumulated elapsed wait time due to update commits.
LLOCK_PARENT_DLY: Accumulated elapsed wait time due to locks requested by parent processes.
DATABASE_IO_EVT: Number of database IO events.
UPDATE_COMMIT_EVT: Number of update commit events.
LLOCK_PARENT_EVT: Number of parent lock requests.
STMT_ID: The SQL statement number assigned by the Db2 pre-compiler to an individual SQL call.
ACCELERATOR: The name of the IBM Db2 Analytics Accelerator for z/OS where the activity might run. When the ACCELERATOR column is blank for a line item, it means that no queries for that line item were offloaded to the IBM Db2 Analytics Accelerator for z/OS.
ACCEL_ELIGIBLE_ELAPSED: The amount of elapsed time that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.
ACCEL_ELIGIBLE_CPU: The amount of CPU time spent on a non-specialty engine that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.
ACCEL_ELIGIBLE_ZIIP: The amount of CPU time spent on a specialty engine that could be saved if the statement or call were to run on an accelerator. This column contains a value only if the statement or call is eligible to run on an accelerator.
PARALLEL: Indicates whether or not the SQL activity was formulated using Db2 query parallelism.
SP_CREATOR: The creator of the stored procedure.
SP_NAME: The name of the stored procedure.
HOSTV_COUNT: The host variable count.
LOG_BYTES_WRITTEN: The number of log bytes written.
LOG_RECORDS_WRITTEN: The total number of log records written.
CPU_ROLL_AVG: The value of the CPU rolling average at the time the exception was generated.
CPU_ROLL_STDV: The value of the CPU rolling standard deviation at the time the exception was generated.
CPU_FACTOR: The amount of standard deviations from the mean the CPU was at the time the exception was generated.
ECPU_ROLLLM: The tolerance level that would have to be crossed in order for a SQL statement to generate an anomaly exception for CPU.
ACPU_ROLLLM: The tolerance level that would have to be crossed in order for a SQL statement to generate an anomaly alert for CPU.
CPU_DSC: Indicates whether or not the value of the CPU for a given SQL statement was factored into the rolling mean and standard deviation.
CPU_DSCT: The discard level that would have to be crossed in order for a SQL statement's CPU time not to update the rolling mean and standard deviation.
ELAP_ROLL_AVG: The value of the elapsed time rolling average at the time the exception was generated.
ELAP_ROLL_STDV: The value of the elapsed time rolling standard deviation at the time the exception was generated.
ELAP_FACTOR: The amount of standard deviations from the mean the elapsed time was at the time the exception was generated.
EELAP_ROLLLM: The tolerance level that would have to be crossed in order for a SQL statement to generate an anomaly exception for elapsed time.
AELAP_ROLLLM: The tolerance level that would have to be crossed in order for an SQL statement to generate an anomaly alert for elapsed time.
ELAP_DSC: Indicates whether or not the value of the elapsed time for a given SQL statement was factored into the rolling mean and standard deviation.
ELAP_DSCT: The discard level that would have to be crossed in order for a SQL statement's elapsed time not to update the rolling mean and standard deviation.
GETPGS_ROLL_AVG: The value of the getpage rolling average at the time the exception was generated.
GETPGS_ROLL_STDV: The value of the getpages rolling standard deviation at the time the exception was generated.
GETPGS_FACTOR: The amount of standard deviations from the mean the getpages were at the time the exception was generated.
EGETPGS_ROLLLM: The tolerance level that would have to be crossed in order for an SQL statement to generate an anomaly exception for getpages.
AGETPGS_ROLLLM: The tolerance level that would have to be crossed in order for an SQL statement to generate an anomaly alert for getpages.
GETPGS_DSC: Indicates whether or not the value of the getpages for a given SQL statement was factored into the rolling mean and standard deviation.
GETPGS_DSCT: The discard level that would have to be crossed in order for a SQL statement's getpages not to update the rolling mean and standard deviation.
EXCEPTION_ELAPSED_ANOMALY: Db2 Elapsed Time exception anomaly detected
EXCEPTION_CPU_ANOMALY: Db2 CPU Time exception anomaly detected
EXCEPTION_GETPGS_ANOMALY: GETPAGE exception anomaly detected
ALERT_ELAPSED_ANOMALY: Db2 Elapsed Time alert anomaly detected
ALERT_CPU_ANOMALY: Db2 CPU Time alert anomaly detected
ALERT_GETPGS_ANOMALY: GETPAGE alert anomaly detected
EXCEPTION_ANOMALIES: Indicates whether anomalies were detected for the statement based on the exception anomaly settings in the profile.
ALERT_ANOMALIES: Indicates whether anomalies were detected for the statement based on the alert anomaly settings in the profile.
ANOMALIES: Indicates whether either exception or alert anomalies were detected.
DB2_CPU_TIME_PROF_EXCP_TOLER: Indicates the CPU toleration, when exceeded, produces an anomaly exception for CPU time for a SQL statement.
DB2_ELAP_TIME_PROF_EXCP_TOLER: Indicates the elapsed time toleration, when exceeded produces an elapsed time anomaly exception for a SQL statement.
TOTAL_GETP_PROF_EXCP_TOLER: Indicates the getpage toleration, when exceeded, produces an anomaly getpage exception for a SQL statement.
DB2_CPU_TIME_PROF_ALRT_TOLER: Indicates the CPU time toleration, when exceeded produces an anomaly CPU alert for a SQL statement.
DB2_ELAP_TIME_PROF_ALRT_TOLER: Indicates the elapsed time toleration when exceeded, produces an elapsed time anomaly alert for a SQL statement.
TOTAL_GETP_PROF_ALRT_TOLER: Indicates the getpage toleration, when exceeded, produces a getpage anomaly alert for a SQL statement.

CQM_EXCP_CALLS

The CQM_EXCP_CALLS table contains information about the exceptions statement detail for exceptions.

This table is associated by the columns: SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, EXCEPTION_TOKEN

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
EXCEPTION_TOKEN: The exception token.
EXCEPTION_TIMESTMP: The exception timestamp.
EXCEPTION_TIMESTMP_UTC: The exception timestamp in UTC.
STMT: The statement number assigned by PRECOMPILER.
TYPE: The type of SQL call executed by a package or DBRM within Db2 (for example, PREPARE, OPEN, FETCH, etc).
SQLCAID: The SQLCA eyecatcher.
SQLCABC: The length of the SQLCA.
SQLCODE: The SQL return code issued by Db2.
SQLERRM: The SQL error message.
SQLERRP: The SQL diagnostic information.
SQLERRD1: Indicates either an internal error code or the number of rows in the result set after the cursor position is at the end (SQLCODE=+100).
SQLERRD2: An internal error code.
SQLERRD3: Indicates the reason code for timeout or deadlock for SQLCODES -911 or -913 or it contains the number of rows affected by an INSERT, UPDATE, or DELETE but not for a cascading delete.
SQLERRD4: A floating point number indicative of the amount of resources used.
SQLERRD5: The position or column number for a syntax error during a PREPARE or EXECUTE IMMEDIATE statement.
SQLERRD6: An internal error code.
SQLWARN0: An SQL warning. Valid values are blank (no other SQLWARNx indicator is set) or W.
SQLWARN1: An SQL warning. Valid values are W (a value was truncated when assigned to a HOSTVAR), N (non-scrollable cursor), and S (scrollable cursor).
SQLWARN2: An SQL warning. Valid values are W (null values were excluded but it is not necessarily set for a MIN function since the result is not dependent on null values).
SQLWARN3: An SQL warning. Valid values are W (the number of result columns is larger than the number of HOSTVARS) and Z (fewer locators were provided in the associated locators statement than the stored procedure returned).
SQLWARN4: An SQL warning. Valid values are W (a prepared UPDATE or DELETE statement does not include a WHERE clause), I ( a scrollable insensitive cursor), S (a scrollable sensitive cursor), and blank (the cursor is not scrollable).
SQLWARN5: An SQL warning. Valid values are W (the SQL statement was invalid for the Db2 subsystem), 1 (the cursor is read only), 2 (the cursor is read and delete), 4 (the cursor is read, delete, and update).
SQLWARN6: An SQL warning. Valid values are W (the addition of a date or timestamp yields an invalid date). The code indicates the date was reset to a valid date.
SQLWARN7: An SQL warning. Valid values are W (one or more nonzero digits were eliminated from fractional parts of a number as a result of a decimal multiply or divide).
SQLWARN8: An SQL warning. Valid values are W (a character could not be converted and was replaced with a substitute character).
SQLWARN9: An SQL warning. Valid values are W (arithmetic exceptions were ignored during count or count big processing) or Z (the stored procedure returned multiple result sets).
SQLWARNA: An SQL warning. Valid values are W (at least one character field of the SQLCA or SQLDA names or labels is invalid due to a character conversion error).
SQLSTATE: The return code for the outcome of the most recent execution of an SQL statement.
DB2_CPU: The accumulated total of all TCB and SRB CPU time spent executing in Db2.
DB2_ELAPSED: The accumulated elapsed time while executing within Db2.
SQL_CALLS: The total number of individual SQL calls executed by Db2.
TRG_DB2_ELAPSED: The total elapsed time consumed by the SQL activity while executing under the control of triggers. A trigger might invoke a stored procedure or a user-defined function. The time spent there is not included in this counter.
TRG_DB2_CPU: The accumulated CPU time consumed in Db2 by the SQL activity while executing under the control of triggers.
UDF_APP_ELAPSED: The total elapsed time spent by the SQL activity in user-defined functions. A user-defined function might invoke a stored procedure or initiate a trigger. The time spent there is not included in this counter.
UDF_APP_CPU: The accumulated CPU time used to satisfy user-defined function requests processed in WLM address space.
UDF_DB2_ELAPSED: The elapsed time consumed in Db2 by the user-defined function.
UDF_DB2_CPU: The CPU time consumed in Db2 by the user-defined function.
SP_APP_ELAPSED: The total elapsed time spent by the SQL activity in stored procedures. A stored procedure might initiate a trigger or invoke a user-defined function. The time spent there is not included in this counter.
SP_APP_CPU: The total CPU time spent by the SQL activity in stored procedures.
SP_DB2_ELAPSED: The TCB time accumulated in Db2 for processing SQL statements issued by stored procedures.
SP_DB2_CPU: The CPU time accumulated in Db2 for processing SQL statements issued by stored procedures.
LOCK_LATCH_DLY: The accumulated lock and latch elapsed wait time for lock and latch suspensions.
SYNC_IO_DLY: The accumulated elapsed wait time for I/O. Synchronous I/O delays are further broken-down into Database I/O Delays and Log Write I/O Delays.
OTHER_READ_DLY: The accumulated wait time for read I/O.
OTHER_WRITE_DLY: The accumulated wait time for write I/O.
SERVTASK_SW_DLY: The accumulated wait time due to synchronous execution unit switch to Db2 services.
ARCHLOG_QS_DLY: The accumulated wait time for archive log quiesces.
ARCHLOG_RD_DLY: The accumulated wait time for archive log reads.
DRAIN_LOCK_DLY: The accumulated wait time for drain locks.
CLAIM_REL_DLY: The accumulated wait time for claim releases.
PAGE_LATCH_DLY: The accumulated wait time due to page latch contention.
SP_DLY: The accumulated wait time due to stored procedure contention.
NOTIFY_MSGS_DLY: The accumulated wait time due to notify messages.
GLOBAL_CONT_DLY: The accumulated elapsed wait time due to global contention for parent L-LOCKS.
LOG_WRITE_DLY: The accumulated elapsed wait time due to log writes.
OPEN_CLOSE_DLY: The Db2 service waits for OPEN/CLOSE DATASET.
SYSLOG_REC_DLY: The Db2 service waits for SYSLGRNG UPDATE.
EXTDEL_DEF_DLY: The Db2 service waits for EXTEND DATASET, DELETE DATASET, and DEFINE DATASET.
OTHER_SERVE_DLY: The Db2 service waits for HSM RECALL DATASET and DATASPACE MANAGER SERVICES.
ASYNCH_CFREQ_DLY: Accumulated wait time for IXLCACHE and IXLFCOMP asynchronous requests.
COMM_PH1WRT_DLY: Accumulated wait time for commit phase 1 I/O.
LLOCKS_CHILD_DLY: The accumulated wait time due to child object locks (pages, rows).
LLOCKS_OTHER_DLY: The accumulated wait time not due to child or parent object locks.
PLOCKS_PAGESET_DLY: The accumulated wait time due to physical locks for pagesets or partitions.
PLOCKS_PAGE_DLY: The accumulated wait time due to page contention.
PLOCKS_OTHER_DLY: The accumulated wait time for other physical contention.
UDF_SCHED_DLY: The accumulated wait time for scheduling user defined functions.
LOCK_LATCH_EVT: The accumulated lock and latch elapsed wait time (in seconds) for lock and latch suspensions.
SYNC_IO_EVT: The number of synchronous I/O events.
OTHER_READ_EVT: The number of I/O read events.
OTHER_WRITE_EVT: The number of I/O write events.
SERVTASK_SW_EVT: The number of synchronous switch to Db2 services to Db2 services which include OPEN/CLOSE data set, SYSLGRNG update, HSM recall data set, dataspace manager, define data set, extend data sets and delete data sets.
ARCHLOG_QS_EVT: The number of archive log quiese commands.
ARCHLOG_RD_EVT: The number of archive log reads.
DRAIN_LOCK_EVT: The number of drain lock events.
CLAIM_REL_EVT: The number of waits for claims to be released prior to a drain.
PAGE_LATCH_EVT: The number of page latch contentions.
SP_EVT: The number of times an SQL CALL statement was delayed waiting for the scheduling of a stored procedure.
NOTIFY_MSGS_EVT: The number of IRLM notify messages sent.
GLOBAL_CONT_EVT: The number of global contentions.
LOG_WRITE_EVT: The number of log write IO events detected.
OPEN_CLOSE_EVT: The number of OPEN/CLOSE data sets.
SYSLOG_REC_EVT: The number of SYSLGRNG updates.
EXTDEL_DEF_EVT: The number of extend, delete, or define data sets.
OTHER_SERVE_EVT: The number of other services which include HSM recall and dataspace manager.
ASYNCH_CFREQ_EVT: The number of IXLCACHE and IXLFCOMP asynchronous requests.
COMM_PH1WRT_EVT: The number of commit phase 1 I/O requests.
LLOCKS_CHILD_EVT: The number of child lock requests.
LLOCKS_OTHER_EVT: The number of other physical contentions.
PLOCKS_PAGESET_EVT: The number of physical locks requests for pagesets or partitions.
PLOCKS_PAGE_EVT: The number of page lock requests.
PLOCKS_OTHER_EVT: The number of other physical contention events.
UDF_SCHED_EVT: The number of user-defined functions scheduling requests.
LOCK_DEADLOCKS: The number of lock deadlocks.
LOCK_SUSPENSIONS: The number of suspensions due to locking conflicts.
LOCK_TIMEOUTS: The number of lock timeouts.
LATCH_SUSPENSIONS: The number of suspensions due to latch conflicts.
OTHER_SUSPENSIONS: The number of suspensions (aside from lock and latch suspensions).
LOCK_REQUESTS: The number of lock requests.
UNLOCK_REQUESTS: The number of unlock requests.
QUERY_REQUESTS: The number of query requests.
CHANGE_REQUESTS: The number of change requests.
OTHER_REQUESTS: The number of all other requests.
CLAIM_REQUESTS: The number of claim requests.
CLAIM_FAILED: The number of unsuccessful claim requests.
DRAIN_REQUESTS: The number of drain requests.
DRAIN_FAILED: The number of unsuccessful drain requests.
XES_LOCK_REQUESTS: The number of XES lock requests.
XES_CHG_REQUESTS: The number of XES change requests.
XES_UNLK_REQUESTS: The number of XES unlock requests.
IRLM_GLOBAL_CONT: The accumulated wait time due to global contention for parent L-locks.
XES_GLOBAL_CONT: The accumulated wait time due to XES global contention for parent L-locks.
FALSE_RES_CONT: The number of false resource contentions.
INCOMPAT_RET_LOCK: The accumulated wait time due to incompatible retain locks.
SHARED_LOCK_ESC: The number of lock escalations to shared mode.
EXCL_LOCK_ESC: The number of lock escalations to exclusive mode.
LOCK_REQ_PLOCKS: The accumulated wait time due to parent object locks (database, table space, table, partition).
CHANGE_REQ_PLOCKS: The number of change requests for parent locks.
UNLOCK_REQ_PLOCKS: The number of unlock requests for parent locks.
NOTIFY_MSGS_SENT: The number of notify messages sent.
RID_USED: The number of times RID list (also called RID pool) processing is used.
RID_FAIL_NO_STOR: The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access).
RID_LIMIT_EXC: The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.
RID_MAX_DEGREE: UNUSED
RID_GROUPS_EXEC: The number of parallel groups executed.
RID_SEQ_CURSOR: The total number of parallel groups that fell back to sequential mode due to a cursor that can be used by UPDATE or DELETE.
RID_SEQ_NO_SORT: The total number of parallel groups that fell back to sequential mode due to a lack of ESA sort storage.
RID_SEQ_NO_BUFF: The total number of parallel groups that fell back to sequential mode due to a storage shortage or contention on the buffer pool.
RID_RAN_REDUCED: The total number of parallel groups that did not reach the planned parallel degree because of a lack of storage space or contention on the buffer pool.
RID_RAN_PLANNED: The total number of parallel groups that executed in the planned parallel degree. This field is raised by an increment of one for each parallel group that executed in the planned degree of parallelism (as determined by Db2).
RID_PROC_ABENDS: The number of times a stored procedure terminated abnormally.
RID_CALL_TIMEOUT: The number of times a SQL call timed out waiting to be scheduled.
RID_CALL_REJECT: The number of times an SQL CALL statement was rejected due to the procedure being in the STOP ACTION(REJECT) state.
RID_SEQ_ENC_SERVE: The total number of parallel groups that executed in sequential mode due to the unavailablity of MVS ESA enclave services.
RID_ONE_DB2_CONO: The total number of parallel groups executed on a single Db2 subsystem due to the COORDINATOR subsystem value being set to NO. When the statement was bound, the COORDINATOR subsystem value was set to YES. This situation can also occur when a package or plan is bound on a Db2 subsystem with COORDINATOR=YES, but is run on a Db2 subsystem with COORDINATOR=NO.
RID_ONE_DB2_ISO: The total number of parallel groups executed on a single Db2 subsystem due to repeatable-read or read-stability isolation.
RID_REOPTIMIZED: The number of times the access path for static and dynamic SQL Queries were re-optimized at run time.
RID_PREP_MATCHED: The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.
RID_IMP_PREPS: The number of implicit prepares (prepares that occur when the user copy of the prepared SQL statement no longer exists in the local dynamic SQL cache and the application plan or package is bound with KEEPDYNAMIC YES).
RID_PREP_CACHE: The number of times a PREPARE command was satisfied by copying a statement from the prepared statement cache.
RID_CACHE_LIM_EXC: The number of times statements are invalidated in the local dynamic SQL cache because the MAXKEEPD limit has been reached and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.
RID_PREP_PURGED: The number of times statements are invalidated in the local dynamic SQL cache because of SQL DDL or updated RUNSTATS information and prepared SQL statements in the local dynamic SQL cache have to be reclaimed.
RID_MAX_STOR_LOB: UNUSED
RID_ROWID_DIRECT: The number of times that direct access was successful.
RID_ROWID_INDEX: The number of times that direct row access failed and an index was used to find a record.
RID_TS_SCANNED: The number of times that an attempt to use direct row access reverted to using a table space scan because Db2 was unable to use a matching index scan.
RID_STMT_TRIGGER: The number of times a statement trigger was activated.
RID_ROW_TRIGGER: The number of times a row trigger was activated.
RID_ERROR_TRIGGER: The number of times an SQL error occurred during the execution of a triggered action. This includes errors that occur in user-defined functions or stored procedures that are called from triggers and that pass back a negative SQLCODE.
MAX_CASCADE_LEVEL: The maximum cascade level.
GETPAGES: The number of getpage requests. This includes conditional, unconditional, successful, and unsuccessful requests. The GETPAGE information for a program reported on the activity summary might not add up to the sum of object detail GETPAGEs of that program due to the trade-off between optimizing the collector for efficiency and increasing the level of detail in some statistics.
BUFFER_UPDATES: The number of buffer pages updated.
SYNC_READS: The number of synchronous read I/O for the object.
SEQ_PREFETCH: The number of SEQ PREFETCH requested for the object.
SYNC_WRITES: The number of synchronous write I/O for the object.
LIST_PREFETCH: The number of LIST PREFETCH requests for the object.
DYNAMIC_PREFETCH: The number of DYNAMIC PREFETCH requested for the object.
HPOOL_READS: The number of successful hiperpool reads.
HPOOL_READS_FAIL: The number of hiperpool reads that failed.
HPOOL_WRITES: The number of successful hiperpool writes.
HPOOL_WRITES_FAIL: The number of hiperpool writes that failed.
GETPAGES_FAILED: The number of times a parallel query failed to find a page in the buffer pool.
ASYNCH_PAGES_READ: The number of asynchronous pages read by prefetch.
ASYNCH_HPOOL_PAGES: The number of pages found and moved from a hiperpool to a virtual buffer by prefetch.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
ZIIP_CPU_TIME: The amount of CPU time accumulated while executing in Db2 on a zIIP processor.
DATABASE_IO_DLY: Accumulated elapsed wait time due to database IO.
UPDATE_COMMIT_DLY: Accumulated elapsed wait time due to update commits.
LLOCK_PARENT_DLY: Accumulated elapsed wait time due to locks requested by parent processes.
DATABASE_IO_EVT: Number of database IO events.
UPDATE_COMMIT_EVT: Number of update commit events.
LLOCK_PARENT_EVT: Number of parent lock requests.

CQM_EXCP_HOSTV

The CQM_EXCP_HOSTV table contains information about the host variables for exceptions.

This table is associated by the columns: SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, EXCEPTION_TOKEN

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
EXCEPTION_TOKEN: The exception token.
HOSTV_TIMESTMP: The exception timestamp.
HOSTV_TIMESTMP_UTC: The exception timestamp in UTC.
THREAD_TOKEN: The thread token. A thread token uniquely identifies an individual connection to a Db2 subsystem.
STMT: The statement number assigned by PRECOMPILER.
TYPE: The type of SQL call executed by a package or DBRM within Db2 (for example, PREPARE, OPEN, FETCH, etc).
HOSTV_CCSID: The encoding CCSID for the host variable.
VARNUM: The PRECOMPILER assigned variable number.
HOSTV_TYPE: The numeric type of host variable.
SQLLEN: The length of the host variable in the SQLDA.
HOSTV_ACTUAL_LEN: The actual length of the host variable.
INDICATOR: The host variable indicator variable.
HOSTV_TEXT: When the host variable data type is not character, the HOSTV_TEXT is the bytes of the machine format. When the host variable data type is character, the value is the character codes in the code page indicated by the HOSTV_CCSID column.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
HOSTV_IEEE_FLOAT: Indicates whether or not the HOSTV_TEXT column contains an IEEE floating point value or other data type. Valid values are Y (the HOSTV_TEXT column contains an IEEE floating point value) and N (the HOSTV_TEXT column contains other data type).

CQM_EXCP_OBJS

The CQM_EXCP_OBJS table contains information about objects for exceptions.

This table is associated by the columns: SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, EXCEPTION_TOKEN

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
EXCEPTION_TOKEN: The exception token.
EXCEPTION_TIMESTMP: The exception timestamp.
EXCEPTION_TIMESTMP_UTC: The exception timestamp in UTC.
THREAD_TOKEN: The thread token. A thread token uniquely identifies an individual connection to a Db2 subsystem.
DBID: The database ID.
OBID: The object ID.
PSID: The pageset ID.
BUFFERPOOL_NORM: The normalized bufferpool number (BP0, BP16K0).
BUFFERPOOL_NUM: The bufferpool number.
OBJECT_TYPE: The object type. Valid values are I (index) and T (table).
DBNAME: The database name.
PAGESET_NAME: The pageset name.
OBJECT_CREATOR: The object creator.
OBJECT_NAME: The object name.
TBCREATOR: The table creator. For indexes, it is the table creator for the table associated with the index.
TBNAME: The name of the table. For indexes, it is the table name of the table associated with the index.
GETPAGE_ELAPSED: The accumulated elapsed time for getpage requests.
GETPAGES: The number of getpages issued which includes conditional, non-conditional, successful and unsuccessful requests.
BUFFER_UPDATES: The number of buffer updates.
SYNC_READS: The number of synchronous read I/O requests.
SEQ_PREFETCH: The number of sequential prefetches.
SYNC_WRITES: The number of synchronous write requests.
LIST_PREFETCH: The number of list prefetch requests.
DYNAMIC_PREFETCH: The number of dynamic prefetch requests.
HPOOL_READS: The number of successful hiperpool reads.
HPOOL_READS_FAIL: The number of hiperpool reads that failed.
HPOOL_WRITES: The number of successful hiperpool writes.
HPOOL_WRITES_FAIL: The number of hiperpool writes that failed.
GETPAGES_FAILED: The number of getpages that failed.
ASYNCH_PAGES_READ: The number of asynchronous pages read by prefetch.
ASYNCH_HPOOL_PAGES: The number of pages found and moved from a hiperpool to a virtual buffer by prefetch.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.

CQM_EXCP_TEXT

The CQM_EXCP_TEXT table contains SQL text for exceptions.

This table is associated with exceptions by SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, TEXT_TOKEN

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_END: The timestamp for the interval end.
TEXT_TOKEN: When combined with SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, and INTERVAL_START, the combined key can be used to find the exception row associated with this SQL text.
TEXT_TIMESTAMP: The timestamp for the SQL text.
CCSID: The encoding CCSID for the SQL text.
ROW_ID: A system-generated row id.
SQLTEXT: The SQL text.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
STRIPPED_TEXT: Indicates whether or not the text is stripped text.

CQM_DB2_COMMANDS

The CQM_DB2_COMMANDS table contains information about Db2 commands.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
DB2_SUBSYSTEM: The Db2 subsystem on which the activity occurred.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
COMMAND_TIMESTAMP: The date and time that Query Monitor recorded the execution of a given Db2 command.
COMMAND_TIMESTAMP_UTC: The UTC date and time that Query Monitor recorded the execution of a given Db2 command.
JOBNAME: The name of the job.
AUTHID: The primary authorization ID.
COMMAND_TEXT: The text of the Db2 command.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.

CQM_SQLCODES

The CQM_SQLCODES table contains information about SQLCODES collected for a monitored Db2 subsystem.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor Subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
DB2_SUBSYSTEM: The Db2 subsystem on which the activity occurred.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
SQLCODE: The SQLCODE.
SQLCODE_COUNT: The number of occurrences of the SQLCODE.
SQLCODE_DETAIL: The number of details captured for the SQLCODE.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.

CQM_SQLCODE_DET

The CQM_SQLCODE_DET table contains detailed information about SQLCODES.

This table is associated with summary rows by the columns: SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, INTERVAL_START, DB2_SUBSYSTEM, DB2_VERSION and SQLCODE.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
DB2_SUBSYSTEM: The Db2 subsystem on which the activity occurred.
DB2_VERSION: A 3-digit value indicating the version of Db2. For example, the DB2_VERSION for Db2 V12 is 120 and the DB2_VERSION for Db2 V13 is 130.
INTERVAL_START_UTC: The UTC timestamp for the interval start.
TEXT_TOKEN: The hexadecimal value of text token, which is used as a part of key to find the duplicate SQL code records.
THREAD_TOKEN: The thread token. A thread token uniquely identifies an individual connection to a Db2 subsystem.
CONSISTENCY_TOKEN: The hexadecimal value of consistency token. Its value corresponds to CONTOKEN column from SYSIBM.SYSPACKAGE.
SQLCODE_TOKEN: This column was retained for compatibility with Db2 Query Monitor V2.1.
SQLCODE_TIMESTAMP: The timestamp of the SQLCODE.
SQLCODE_TIMESTAMP_UTC: The UTC timestamp of the SQLCODE.
IMPLICIT_QUALIFIER: The implicit qualifier.
DETAIL_COUNT: The number of details collected.
PLAN: The Db2 plan name.
COLLECTION: The collection ID.
PROGRAM: The Db2 package or DBRM name.
SECTION: The section number.
STMT: The statement number assigned by PRECOMPILER.
TYPE: The type of SQL call executed by a package or DBRM within Db2 (for example, PREPARE, OPEN, FETCH, etc).
AUTHID: The primary authorization ID.
JOBNAME: The name of the job.
CONNECTION: The connection name.
CURSOR_NAME: The cursor name.
SQLCAID: The SQLCA eyecatcher.
SQLCABC: The length of the SQLCA.
SQLCODE: The SQL return code issued by Db2.
SQLERRM: The SQL error message.
SQLERRP: The SQL diagnostic information.
SQLCODE: The SQL return code issued by Db2.
SQLERRM: The SQL error message.
SQLERRP: The SQL diagnostic information.
SQLCODE: The SQL return code issued by Db2.
SQLERRM: The SQL error message.
SQLERRP: The SQL diagnostic information.
SQLERRD1: Indicates either an internal error code or the number of rows in the result set after the cursor position is at the end (SQLCODE=+100).
SQLERRD2: An internal error code.
SQLERRD3: Indicates the reason code for timeout or deadlock for SQLCODES -911 or -913 or it contains the number of rows affected by an INSERT, UPDATE, or DELETE but not for a cascading delete.
SQLERRD4: A floating point number indicative of the amount of resources used.
SQLERRD5: The position or column number for a syntax error during a PREPARE or EXECUTE IMMEDIATE statement.
SQLERRD6: An internal error code.
SQLWARN0: An SQL warning. Valid values are blank (no other SQLWARNx indicator is set) or W.
SQLWARN1: An SQL warning. Valid values are W (a value was truncated when assigned to a HOSTVAR), N (non-scrollable cursor), and S (scrollable cursor).
SQLWARN2: An SQL warning. Valid values are W (null values were excluded but it is not necessarily set for a MIN function since the result is not dependent on null values).
SQLWARN3: An SQL warning. Valid values are W (the number of result columns is larger than the number of HOSTVARS) and Z (fewer locators were provided in the associated locators statement than the stored procedure returned).
SQLWARN4: An SQL warning. Valid values are W (a prepared UPDATE or DELETE statement does not include a WHERE clause), I ( a scrollable insensitive cursor), S (a scrollable sensitive cursor), and blank (the cursor is not scrollable).
SQLWARN5: An SQL warning. Valid values are W (the SQL statement was invalid for the Db2 subsystem), 1 (the cursor is read only), 2 (the cursor is read and delete), 4 (the cursor is read, delete, and update).
SQLWARN6: An SQL warning. Valid values are W (the addition of a date or timestamp yields an invalid date). The code indicates the date was reset to a valid date.
SQLWARN7: An SQL warning. Valid values are W (one or more nonzero digits were eliminated from fractional parts of a number as a result of a decimal multiply or divide).
SQLWARN8: An SQL warning. Valid values are W (a character could not be converted and was replaced with a substitute character).
SQLWARN9: An SQL warning. Valid values are W (arithmetic exceptions were ignored during count or count big processing) or Z (the stored procedure returned multiple result sets).
SQLWARNA: An SQL warning. Valid values are W (at least one character field of the SQLCA or SQLDA names or labels is invalid due to a character conversion error).
SQLSTATE: The return code for the outcome of the most recent execution of an SQL statement.
INTERVAL_END: The timestamp identifying the end time of the interval.
INTERVAL_END_UTC: The timestamp identifying the UTC end time of the interval.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
DB2_VERSION_LONG: A 4-digit value indicating the version of Db2. For example, the DB2_VERSION_LONG for Db2 V12 is 1210 and the DB2_VERSION_LONG for Db2 V13 is 1310.
CURRENT_SCHEMA: The current schema that executed the SQL.

CQM_SQLCODE_TEXT

The CQM_SQLCODE_TEXT table contains information about the SQL text.

SMFID: The z/OS SMFID.
CQM_SUBSYSTEM: The Db2 Query Monitor subsystem ID that created the interval.
INTERVAL_NUMBER: The interval number.
INTERVAL_START: The timestamp for the interval start.
INTERVAL_END: The timestamp for the interval end.
TEXT_TOKEN: When combined with SMFID, CQM_SUBSYSTEM, INTERVAL_NUMBER, and INTERVAL_START, the combined key can be used to find the exception row associated with this SQL text.
TEXT_TIMESTAMP: The timestamp for the SQL text.
CCSID: The encoding CCSID for the SQL text.
ROW_ID: A system-generated row id.
SQLTEXT: The SQL text.
DB2_GROUP_NAME: The Db2 subsystem ID or Db2 data sharing group.
STRIPPED_TEXT: Indicates whether or not the text is stripped text.