TSTAT-Trace Statistics

Accounting sections:

ELAPSED

Elapsed time analysis

Elapsed time data if accounting class 2 or 3 is active

SQLCOUNTS

SQL statement execution counts

Statistics for the individual types of SQL statements executed

BPOOL

Buffer pool activity

Buffer pool usage

LOCKS

Lock activity

Lock usage

PRLL

Parallelism

Parallel CPU and I/O activity

RTN

Routines

Routine statistics if stored procedure calls have been issued

ACC

Accelerator

Accelerator activity

DDF

DDF summary

DDF statistics if there is DDF activity for the displayed thread

Detail trace summary section:

SCANS

Database summary

Database summary for all records

IO/LOCK

Database lock and I/O summary

Synchronous reads and writes and lock suspensions

SORTS

Sort summary

Summary of number of sorts, records sorted, sort elapsed time, and record size

trace id

A unique ID identifying the trace. The TSTAT trace ID must match the trace ID of the ATRAC request. You can use a blank if the ATRAC ID is also blank. This parameter is positional and a comma delimits the next parameter. For example, if there is no identifier, a comma must precede any other parameter. However, if there is an ID, it must be in the first position.

,DTL

Selects the data for the Database Summary, the Database Lock and I/O Summary, and the Sort Summary sections when viewing a history trace.

,A | AUTHID= xxxxxxxx

Selects a subset of the trace entries by AUTHID. Specify one AUTHID, or a generic group of AUTHIDs by replacing character positions with a + (one position) or an * (multiple positions).

,C | CONNECT= xxxxxxxx

Selects a subset of the trace entries by connection name. Specify one connect type (TSO, BATCH, DB2CALL, imsid, cicsjobname), or a generic group of connections by replacing character positions with a + (one position) or an * (multiple positions).

,P | PLAN= xxxxxxxx

Selects a subset of the trace entries by plan. Specify one plan, or a generic group of plans by replacing character positions with a + (one position) or an * (multiple positions).

,L | LOC= xxxxxxxx

Selects a subset of the trace entries by location. Specify one location, or a generic group of locations by replacing character positions with a + (one position) or an * (multiple positions).

,R | CORRID= xxxxxxxxxxxxxxxx

Selects a subset of the trace entries by correlation ID. Specify one correlation ID, or a generic group of correlation IDs by replacing character positions with a + (one position) or an * (multiple positions).

,T | TIME= hhmm [ -hhmm ]

Selects a subset of the trace entries by a start time or time period; hhmm indicates hours and minutes. Midnight wrap is supported where the start time is higher than the end time; for example, TIME=2000-3000. The end time stamp of an accounting record is used to assign it a time period.

• A time qualification or the DTL parameter is specified for an historical trace log data set

• The display is requested for an active trace, regardless of time qualification

,D | DAY= nn [- -nn ]

Selects a subset of the trace entries by a day or range of days; nn can be a relative number from the date of the first accounting record in the trace buffer (first date = 1, up to a maximum of 99).

If a time period is selected without a DAY specification, it uses the default value of the day of the most current record in the buffer. If there is data for only one day in the trace buffer, the format of the time stamp is hh:mm:ss. If there is data for more than one day, the format is dd-hh:mm, where dd is the relative day number from the date of the first accounting record in the trace buffer. Each accounting record traced is assigned to a time period based on its end time stamp. Therefore, the processing done for that unit of work is assigned to one interval, but it may have partially occurred during one or more previous time intervals depending on its elapsed time.

MON(WKLD)

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

HISTORY

Data from the current trace log data set for this trace (if you are viewing a current trace with logging, this provides access to data no longer in the online buffer).

It is only shown when logging is active.

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)

ACCOUNTING

Format data from the DB2 accounting record

ELAPSED

TSTAT Elapsed Time Analysis display section (accounting classes 2 and 3 only)

SQLCOUNTS

TSTAT SQL Statement Execution Counts display section

BPOOL

TSTAT Buffer Pool Usage Analysis display section

LOCKS

TSTAT Lock Activity display section

PRLL

TSTAT Parallelism display section

RTN

TSTAT Routines display section (only if stored procedure calls have been issued)

DDF

TSTAT DDF Summary display section (available only if there is DDF activity for the displayed thread)

SUMMARIES

Format data summarized from detail trace events and are available only if these events are traced

SCANS

TSTAT Database Summary display section

IO/LOCK

TSTAT Lock and I/O Summary display section

SORTS

TSTAT Sort Summary display section

These indicators show the cause of degradation and are highlighted

TIMEOUT (or DEADLOCK) = nnnnnn

Indicates the number of units of work that failed because either a timeout condition or a deadlock condition was detected. TIMEOUT means that a 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 each process normally.

N/A

• QTXATIM

• QTXADEA

STORED PROC. FAILED = nnnnnn

Indicates the number of times a stored procedure call failed. Possible reasons for failure are as follows:

• The stored procedure call terminated abnormally.

• The stored procedure call was rejected.

• The stored procedure call was timed out while it was waiting to be scheduled.

N/A

• QXCALLAB

• QXCALLRJ

• QXCALLTO

RID FAILURE - STORAGE

Indicates the number of times RID list processing failed for the units of work in this trace because not enough storage was available.

Review the storage allocations specified when DB2 was installed.

QXNSMIAP

RID FAILURE - NUMBER OF RIDS

Indicates the number of times RID list processing failed for the units of work in this trace 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.

N/A

QXMRMIAP

CLAIM FAILURE

Indicates the number of times CLAIM processing failed for the units of work in this trace.

N/A

QTXACLUN

DRAIN FAILURE

Indicates the number of times DRAIN processing failed for the units of work in this trace.

N/A

QTXADRUN

PARALLEL CPU FALLBACK - nnnnnn

Indicates the number of times parallel CPU processing was disabled for the units of work in this trace.

N/A

• QXDEGENC

• QXRLFDPA

PARALLEL I/O FALLBACK - nnnnnn

Indicates the number of times parallel I/O processing was turned off and sequential mode was used for the units of work in this trace.

N/A

• QXDEGBUF

• QXDEGESA

• QXDEGCUR

PARALLEL I/O REDUCED DEGREE - nnnnnn

Indicates the number of times 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.

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

INCREMENTAL BINDS = nnnnnn

Indicates the number of incremental binds performed by the units of work in this trace. 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

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.

N/A

TOTAL DDL = nnnnnn

Indicates the number of Data Definition Language (DDL) statements performed by the units of work in this trace. 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

BUFFER INCOMPLETE

Indicates at least one of the threads spans multiple trace log data sets. Accounting statistics data is available for those threads only 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.

N/A

These indicators are informational in nature and are not highlighted.

GRANTS/REVOKES = nnnnnn

Indicates the number of GRANT and REVOKE SQL statements issued by the units of work in this trace.

N/A

• QXGRANT

• QXREVOK

zHyperlink eligible=n

(PTFs BPD4919 and BPD4920 applied)

zHyperlink used=n

(PTFs BPD4919 and BPD4920 applied)

IAG2 used=n

(PTFs BPD4919 and BPD4920 applied)

LOCK TABLE = nnnnnn

Indicates the number of LOCK TABLE SQL statements issued by the units of work in this trace.

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

QXLOCK

LOCK ESCALATIONS = nnnnnn

Indicates the number of times the total locks per table have exceeded the installation limit for the units of work in this trace. 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

PARALLEL I/O CONDITIONAL GETPAGE FAILURE

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

N/A

QBACNGT

SQL: SELECT= nnnnnn, FETCH= nnnnnn

Is a 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.

N/A

• QXSELECT

• QXFETCH

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

Is a summary of the number of SQL INSERT, UPDATE, and DELETE SQL statements issued by the units of work in this trace.

N/A

• QXINSRT

• QXUPDTE

• QXDELET

SQL: MERGE= nnnnnn, TRUNCATE= nnnnnn

Is a summary of the number of SQL MERGE and TRUNCATE SQL statements issued by the units of work in this trace.

N/A

• QXMERGE

• QXTRTBL

SQL: DYNAMIC(PREPARE)= nnnnnn

Is a summary of the number of PREPARE SQL statements issued by the units of work in this trace. 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.

N/A

QXPREP

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

Indicates the average I/O response times the units of work in this trace experienced for synchronous and asynchronous requests.

N/A

For synchronous requests:

• QWACARNE

• QWACAWTI

For asynchronous requests:

• QWACARNR

• QWACARNW

• QWACAWTR

• QWACAWTW

DDF/RRSAF ROLLUP, LIMIT= nnnnnn, STG= nnnnnn, TIME= nnnnnn

Indicates the number of DDF or RRSAF threads terminated for the following reasons:

• Limit–The threshold was reached for the amount of data accumulated by the user.

• STG–The internal storage threshold was reached for data accumulated by the user.

• TIME–The time limit (staleness threshold) was reached for data accumulated by the user.

N/A

• QWACRACC

• QWACRSTG

• QWACRSTL

LOCK SUSPENSIONS = nnnnnn

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

N/A

• QTXASLOC

• QTXASLAT

• QTXASOTH

PARALLELISM, MAINTASKS = nnnnnn, SUBTASKS = nnnnnn

Indicates that this trace contains units of work that are the primary, or originating, unit of work in a group supporting a query or utility using parallel tasks. MAINTASKS shows the total number of main tasks created to support queries or utilities using parallel tasks in this trace. SUBTASKS shows the total number of subtasks created to support queries or utilities using parallel subtasks in this trace.

N/A

• QWACPACE

• QWACPCNT

SYSPLEX PARALLELISM - COORDINATOR = nnnnnn

Indicates that queries are being processed across more than one DB2, where this DB2 was the coordinator.

N/A

QWDA

SYSPLEX PARALLELISM - ASSISTANT = nnnnnn

Indicates that queries are being processed across more than one DB2, where this DB2 was an assistant.

N/A

QWDA

NUMBER OF DISTRIBUTED LOCATIONS = nnnnnn

Indicates the number of separate DDF locations accessed by the units of work in this trace.

N/A

N/A

RID LIST PROCESSING USED = nnnnnn

Indicates the number of times RID list processing was used for the units of work in this trace. 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.

N/A

QXMIAP