Viewing summary data for a thread

MON(WKLD)

Active Timer Requests display of all active monitors in the Db2 workload (WKLD) area

DETAIL

Detail Trace Entry display

HISTORY

Historical trace data set for this trace.

If you are viewing a current trace with logging, this display provides access to data no longer in the online buffer.

CURRENT

Active trace data still in the buffers (if you are viewing a trace log data set for a still-active trace or a complete trace that has not yet been purged)

EXPLAIN

On the SQL statement pop-up display, for static SQL, hyperlinks to the Explain Object Specification panel (if the Data Collector is installed)

On this panel you can select the plan or package and find the static SQL statement text to explain it.

SQL 

SCANS

STRAC Database Summary display section

IO/LOCK

STRAC Lock and I/O Summary display section

SORTS

STRAC Sort Summary display section

Data about detail trace events
For more information, see SUMMARIES sections.

CURRENT

ENV

ELAPSED TIME

ELAPSED

TOTAL SQL

SQLCOUNTS

GETPAGES

BPOOL

SYNC I/O (PRLL=nn)

PRLL

UPDATES/COMMIT

LOCKS

BUFFER INCOMPLETE

Indicates that the thread spans multiple trace log data sets. No accounting statistics data is available except in the last log, and detail trace data is incomplete in each log. However, accounting data in the final log is complete and represents the entire thread. The detail trace data in each log can be added together for total results.

Allocate a larger trace log data set.

Not applicable

CLAIM FAILURE

Indicates that CLAIM processing failed for this unit of work.

Not applicable

QTXACLUN

DRAIN FAILURE

Indicates that DRAIN processing failed for this unit of work.

Not applicable

QTXADRUN

FILTER: fieldname= xxxxx (> n )

Indicates that this trace was selected because one of the trace exception filter conditions was met. These filter conditions are specified when the trace is started.

• fieldname-Name of the trace exception filter condition
• xxxxxx-Target value
• nnnnnn-Actual value measured

Not applicable

Not applicable

INCREMENTAL BINDS= nnnnnn

Indicates the number of incremental binds this unit of work performed. This message indicates that the plan/package had become invalid for some reason, such as an ALTER on an index or table.

Nothing needs to be done for this plan, but you might want to explicitly bind any plans whose tables/indexes are being altered. Incremental binds can have a significant impact in an online transaction system.

QXINCRB

PARALLEL CPU FALLBACK - reason

Indicates that parallel CPU processing was turned off and sequential mode was used. The possible causes indicated by reason are as follows:

• NO ENCLAVE-The MVS ENCLAVE feature is not supported on this machine.
• RLF-The Resource Limit Facility has limited CPU parallelism for some SELECT statement.

Not applicable

• QXDEGENC
• QXRLFDPA

PARALLEL I/O FALLBACK - reason

Indicates that parallel I/O processing was turned off and sequential mode was used. The possible causes indicated by reason are as follows:

• STORAGE-Storage shortage or insufficient buffers in the buffer pool.
• ESA SORT-The ESA sort feature is not installed on this machine.
• AMBIG. CURSOR-The cursor in use is capable of UPDATE or DELETE.

Not applicable

• QXDEGBUF
• QXDEGESA
• QXDEGCUR

PARALLEL I/O REDUCED DEGREE - nnnnnn

Indicates that parallel I/O processing was reduced from the planned degree to a lesser degree because of insufficient storage or insufficient buffers in the buffer pool. nnnnnn is the number of times this condition has occurred

If this number is not zero, consider increasing the size of the buffer pool or specifying a different buffer pool for the table space.

QXREDGRP

RID FAILURE - NUMBER OF RIDS

Indicates that RID list processing for this unit of work failed because one of the internal limits was exceeded. The internal limits include the physical limit of the number of RIDs a RID list can contain and internal thresholds for the retrieval and manipulation of RIDs.

Not applicable

QXMRMIAP

RID FAILURE - STORAGE

Indicates that RID list processing for this unit of work failed because not enough storage was available.

Review the storage allocations specified when Db2 was installed.

QXNSMIAP

Indicates the Resource Limit Facility (RLF) CPU service units (SU) limit. A value of 0 indicates no limit.         

Not applicable

QTXASLMT  

SEE RTN FOR MORE CPU/ELAPSED

Indicates that the total elapsed time may be less than the CPU time or Db2 wait time if stored procedures, user-defined functions, or triggers are present.

See the Routines (RTN) section for more information on stored procedures, user-defined functions, and triggers when any of them are present.

Not applicable

STORED PROC. FAILED (reason) = nnnnnn

Indicates the number of times a stored procedure call failed for one of the following reasons:

• ABEND-The stored procedure call terminated abnormally.
• REJECT-The stored procedure call was rejected.
• TIMEOUT-The stored procedure call was timed out while waiting to be scheduled.

Not applicable

• QXCALLAB
• QXCALLRJ
• QXCALLTO

SYSPLEX PRLL FALLBACK - reason

Indicates that sysplex parallel processing was disabled and the parallel group was executed on a single Db2. Possible causes indicated by reason are as follows:

• COORDINATR=NO-Bound COORDINATOR=YES, but run on COORDINATOR=NO system.
• ISOLAT..RR/RS-PLAN/PACKAGE was bound with isolation level of repeatable read.
• BYPASS-BUFFER-Bypassed due to insufficient buffer pool storage.

Not applicable

• QXCOORNO
• QXISOOR
• QXXCSKIP

TIMEOUT (or DEADLOCK)

Indicates that this unit of work failed because either a timeout condition or deadlock condition was detected. TIMEOUT means that the unit of work was suspended for a length of time longer than permitted by installation options. DEADLOCK means that two units of work attempted to access the same resource and prevented each other from completing their processes normally.

Not applicable

• QTXATIM
• QTXADEA

TOTAL DDL=nnnnnn

Indicates the number of Data Definition Language (DDL) statements performed by this unit of work. DDL statements, such as CREATE TABLE, can have a significant impact on system performance. To perform DDL operations, exclusive locks need to be obtained against the Db2 catalog.

In an online transaction system, avoid designing transactions that use DDL operations.

• QXCRTAB
• QXCRINX

The following indicators are informational in nature and are not highlighted

DDF/RRSAF ROLL UP RECORD, COUNT= nnnnnn

Indicates the number of DDF or RRSAF threads rolled up into this accounting record.

Not applicable

• QWACRACC
• QWACRSTG
• QWACRSTL

GRANTS/REVOKES = nnnnnn

Indicates the number of GRANT and REVOKE SQL statements issued by this unit of work.

Not applicable

• QXGRANT
• QXREVOK

zHyperlink eligible=n

Indicates the number of read I/Os with DASD cache hits

Not applicable

QBACIOC

zHyperlink used=n

Indicates the number of read I/Os that used zHyperLink

Not applicable

QBACSYI

IAG2 used=n

Indicates whether Fast INSERT (IAG2) was used

Not applicable

QXRWSINSRTDAlg2

I/O RSP: SYNC= time, ASYNC= time

This indicator provides an indication of the average I/O response times this unit of work experienced for synchronous and asynchronous requests.

Not applicable

For synchronous requests:

• QWACARNE
• QWACAWTI

For asynchronous requests:

• QWACARNR
• QWACARNW
• QWACAWTR
• QWACAWTW

LOCK ESCALATIONS = nnnnnn

Indicates the number of times that the total locks per table have exceeded the installation limit for this unit of work. When this limit is exceeded, Db2 promotes the current page locks to a single table space lock of the same type.

Lock escalation usually indicates that the application has encountered an exceptional condition. If this situation occurs frequently, the design of the application should be reviewed.

• QTXALES
• QTXALEX

LOCK SUSPENSIONS = nnnnnn

Indicates the number of times this unit of work was suspended due to a lock or latch conflict with another unit of work. This number should be small, ideally zero.

Not applicable

• QTXASLOC
• QTXASLAT
• QTXASOTH

LOCK TABLE = nnnnnn

Indicates the number of LOCK TABLE SQL statements issued by this unit of work.

Use LOCK TABLE carefully. It means that this unit of work owns exclusive control of the table, allowing no concurrent access.

QXLOCK

NUMBER OF DISTRIBUTED LOCATIONS = nnnnnn

Indicates the number of separate DDF locations accessed by this unit of work.

Not applicable

Not applicable

PARALLEL - SUBTASK

Indicates that this unit of work was created in support of a query or utility using parallel tasks.

Not applicable

QWACPACE

PARALLEL I/O CONDITIONAL GETPAGE FAILURE

Indicates that a conditional GETPAGE request was not satisfied for this buffer pool. This condition applies only to parallel I/O that is conditional in nature. When this condition occurs, it indicates that a prefetch operation failed to retrieve a page before the application required it.

Not applicable

QBACNGT

PARALLEL MAINTASK, SUBTASKS = nnnnnn

Indicates that this unit of work is the primary, or originating unit of work in a group supporting a query or utility using parallel tasks. SUBTASKS shows the number of subtask units of work that were created to support this query or utility.

Not applicable

• QWACPACE
• QWACPCNT

RID LIST PROCESSING USED = nnnnnn

Indicates the number of times RID list processing was used for this unit of work. During RID list processing, Db2 produces a list of candidate record IDs from an index. The resulting RID list can be used to efficiently retrieve the qualifying rows.

Not applicable

QXMAIP

RLF(nn), TYPE= xxxxxxxxxxxxxxx

Indicates that the Resource Limit Facility (RLF) is active and displays the RLF limit rule that applies to this unit of work.

• nn-Suffix of the Resource Limit Facility (RLF) table in effect.
• xx..xx-RLF rule that applies to this thread.

Refer to the IBM Db2 Administration Guide for the definition of the rule.

Not applicable

• QTXARLID
• QTXAPREC

SQL: DYNAMIC(PREPARE)= nnnnnn

Indicates the summary of the number of PREPARE SQL statements issued by this unit of work. Each PREPARE statement is equivalent to one dynamic SQL statement. In a DDF environment, the number of PREPARE statements might not truly reflect the number of dynamic SQL statements issued by the application due to internal DDF processing.

Not applicable

QXPREP

SQL: INS= nnnnnn, UPD= nnnnnn, DEL= nnnnnn

Indicates the summary of the number of SQL INSERT, UPDATE, and DELETE SQL statements issued by this unit of work.

Not applicable

• QXINSRT
• QXUPDTE
• QXDELET

SQL: MERGE= nnnnnn, TRUNCATE= nnnnnn

Indicates the summary of the number of SQL MERGE and TRUNCATE SQL statements issued by this unit of work.

Not applicable

• QXMERGE
• QXTRTBL

SQL: SELECT= nnnnnn, FETCH= nnnnnn

Indicates the summary of the number of singleton SQL SELECTs, as well as FETCH statements, issued by this unit of work.

Applications that only use cursor processing for the reading of data show FETCH statements but not SELECT statements. In a DDF environment, the number of FETCH statements might not truly reflect the number of FETCHes issued by the application due to internal DDF processing.

Not applicable

• QXSELECT
• QXFETCH

SYSPLEX PARALLELISM - COORDINATOR/ASSISTANT

Indicates that a query was processed across more than one Db2, where this Db2 was either the coordinator or an assistant.

Not applicable

QWDA