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.