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.