Dynamic SQL Cache Attributes

Use these attributes to view dynamic SQL cache information.

AVG Actual Degree The average actual degree of parallelism for all parallel groups. The actual degree of parallelism is calculated at execution time, after buffer pool negotiation and system negotiation are taken into account (Field name: Q316ACT).

AVG Estimated Degree The average estimated degree of parallelism for all parallel groups. The estimated degrees are calculated at bind time, based on the cost formula. The average is calculated at execution time (Field name: Q316ESTI).

AVG Planned Degree The average planned maximum degree of parallelism for all parallel groups. The average is calculated at execution time. It is the optimal degree of parallelism that can be obtained at execution time, after host variables or parameter markers are resolved, and before buffer pool negotiation and system negotiation are performed (Field name: Q316PLAN).

Auth ID The text of the user ID. If QW0316T3_OFF is not 0, this value is truncated (Field name: QW0316T3).

CPU Time The accumulated CPU time. This value includes CPU time that is consumed on an IBM specialty engine (Field name: QW0316CT).

Claim Rel Wait The accumulated wait time for drains during waits for claims to be released (Field name: QW0316WA).

Coll Begin STCK The timestamp when statistics collection began. Data collection begins when a trace for IFCID 318 is started (Field name: QW0316TS).

Copies of Statements The number of copies of the STMT owned by all threads in the system. This includes QW0316US plus any copies owned by plans/packages bound with KEEPDYNAMIC(YES) that were not used in their current unit of work. These users prepared the statement in a previous unit of work and still have it in a prepared state (Field name: QW0316CP).

Current users The number of current users of STMT. These are the users that have prepared or executed the statement during their current unit of work (Field name: QW0316US).

DB2 Subsystem The DB2 subsystem ID.

DB2 Version The DB2 version.

DSG Member Name The Data Sharing Group (DSG) member name.

Data Sharing Group The Data Sharing Group name.

Drain Lock Wait The accumulated wait time for drain locks (DB2 field name: QW0316W9).

Elapsed Time The accumulated elapsed time used for statement (Field name: QW0316AE).

End User ID The end-user ID. This value is provided during RRS signon or re-signon (Field name: SQW316XE).

Filler1Filler for padding.

Get Pages The number of GETPAGE operations performed for statement (Field name: QW0316NG).

Global Lock Wait The accumulated wait time for global locks (Field name: QW0316W4).

Global child\L-locks waitWaits due to global contention for child L-locks (Field name: QW0316WC).

Global other\L-locks waitWaits due to global contention for other L-locks (Field name: QW0316WD).

Global P/P P-locks waitWaits due to global contention for Pageset/Partition P-locks (Field name: QW0316WE).

Global Page\P-locks waitWaits due to global contention for Page P-locks (Field name: QW0316WF).

Global Other\P-locks waitWaits due to global contention for other P-locks (Field name: QW316WG).

Index Scans The number of index scans performed for statement (Field name: QW0316NI).

Insert Date Time The date and time when the statement was inserted into the cache (in DB2 timestamp format) (Field name: QW0316TM).

Interval Start The start time of this interval.

Latch Req Wait The accumulated wait time for latch requests (Field name: QW0316W7).

Lock-Req Wait The accumulated wait time for lock requests (Field name: QW0316W2).

Log Write Wait The accumulated wait time for log writers. (Field name: QW0316WB).

MVS System ID The MVS identifier.

Originating System The managed system name of the agent. It is an alphanumeric text string, with a maximum of 32 characters; for example, DB91:SYS1:DB2.

Page Latch Wait The accumulated wait time for page latches (Field name: QW0316W8).

Parallel Groups Created The number of parallel groups created for this statement (Field name: QW0316NL).

QW0316NM 1 Part 1 of QW0316NM.

QW0316NM 2 Part 2 of QW0316NM.

QW0316NM 3 Part 3 of QW0316NM.

QW0316NM 4 Part 4 of QW0316NM.

RIDs HB Join/Limit The number of times that appending to a RID list for a hybrid join was interrupted because the number RIDs exceeded one or more internal limits (Field name: QW316HJT).

RIDs HB Join/Storage The number of times that appending to a RID list for a hybrid join was interrupted because no RID pool storage was available to hold the list of RIDs (Field name: QW316HJS).

RIDs No IX Access The number of times that RID list retrieval for multiple index access was not done because DB2 could determine the outcome of index ANDing or ORing (Field name: QW316RSM).

RIDs Not Used/Limit The number of times that a RID list was not used because the number of RIDs exceeded one or more internal DB2 limits, and the number of RID blocks exceeded the value of subsystem parameter MAXTEMPS_RID (Field name: QW0316RT).

RIDs Not Used/Storage The number of times that a RID list was not used because not enough storage was available to hold the RID list, or work file storage or resources were not available (Field name: QW0316RS).

RIDs Overflow/Limit The number of times that a RID list overflowed to a work file because the number of RIDs exceeded one or more internal limits (Field name: QW316WRT).

RIDs Overflow/Storage The number of times that a RID list overflowed to a work file because no RID pool storage was available to hold the list of RIDs (Field name: QW316WRS).

Read Activity Wait The accumulated wait time for read activity that is done by another thread (Field name: QW0316W5).

Rows Examined The number of rows examined for statement (Field name: QW0316NR).

Rows Processed The number of rows processed for statement. For example, the number of rows returned for a SELECT, or the number of rows affected by an INSERT, UPDATE, or DELETE (Field name: QW0316NP).

SQL Text The first 60 bytes of statement text (Field name: QW0316TX).

Sorts Performed The number of sorts performed for statement (Field name: QW0316NS).

Statement Identifier The unique statement identifier. A generated number for uniquely identifying a statement in the prepared statement cache (Field name: QW0316TK).

Statement Name The statement name generated by DB2 for this statement (Field name: QW0316NM).

Stats Insert STCK The date and time when the statement was inserted into the cache, in store clock format. (Field name: QW316TM2).

Stats Update STCK The date and time when the statement was updated, in store clock format (Field name: QW316UT1).

Stmt Length The length of entire statement (Field name: QW0316LN).

Sync I/O Wait The accumulated wait time for synchronous I/O. This wait time and the following wait times are collected only when a class 3 accounting trace is started (Field name: QW0316W1).

Sync Switch Wait The accumulated wait time for a synchronous execution unit switch (DB2 field name: QW0316W3).

Sync Reads The number of synchronous buffer reads performed for statement (Field name: QW0316NB).

Sync Writes The number of synchronous buffer write operations performed for statement (Field name: QW0316NW).

Tablespace Scans The number of tablespace scans performed for statement (Field name: QW0316NT).

Times Exec. The number of executions of the statement. For a cursor statement, this is the number of OPENs (Field name: QW0316NE).

Total Wait The total wait time for the dynamic SQL statement (Field name: QW0316WT).

Transaction ID The transaction name. This value is provided during RRS signon or re-signon (Field name: SQW316T2).

Update Date Time The date and time when the statement was updated, in internal format (Field name: QW316UT2).

Workstation ID The workstation name. This value is provided during RRS signon or re-signon (Field name: SQW316XF).

Write Activity Wait The accumulated wait time for write activity that is done by another thread (Field name: QW0316W6).