STRAC-Summary Trace Entry

The Summary Trace Entry display (STRAC) shows summary data for a completed transaction or thread.

This display shows accounting data on the activity generated by one transaction or thread, and might include summaries of detail trace events. You can access the STRAC by hyperlinking on LINESEL(STRAC) from the LTRAC display.

The following figure shows a representative display of the base section of the STRAC view. Depending on your level of PTF maintenance, select one of the following tabs:

PTFs BPD4919 and BPD4920 applied
PTFs BPD4919 and BPD4920 not applied

BMC SOFTWARE -------------- SUMMARY TRACE ENTRY    ----------PERFORMANCE MGMT
SERV ==> STRAC            INPUT   22:45:20 INTVL=> 3 LOG=> N TGT==> DLY6
PARM ==> THRDHIST,SEQ=1                             ROW 1 OF 170 SCROLL=> CSR
EXPAND: MON(WKLD), DETAIL
         ACCOUNTING: ENV, ELAP, SQLCNTS, BPOOL, LOCKS, PRLL, PKG, RTN, ACC, DDF

STOP.....30MAR 03.42.22.68 PLAN..............DAA121QA TYPE..............ALLIED
START....30MAR 03.42.22.28 AUTHID.............RCDDXI1 CONNECT......DB2CALL/CAF
ELAPSED.............404 ms ORIG PRIM AUTH.....RCDDXI1 CORR ID.....RCDDXI1
TERM........NORMAL/DEALLOC COMMITS..................1 ROLLBACKS..............0
------------------------------------------------------------------------------
RUNTIME ANALYSIS   IN DB2     IN APPL.      TOTAL      %IN DB2(=)     TOTAL(*)
----------------   --------   --------   --------     0 ...25...50...75..100%
ELAPSED TIME         399 ms   5,049 us     404 ms     | ===================* |
CPU TIME              39 ms   2,759 us      42 ms     | =*                   |
DB2 WAIT TIME        358 ms                           | =================    |
- Specialty Engine CPU times - -
   - In DB2 CPU         0 us                  0 us     |                      |
   - STORED PROC        0 us
   - UDF                0 us
   - TRIGGER            0 us
- Accel-elig elpsd     0 us
   - Eligible CPU       0 us
   - Eligible zIIP      0 us
- - - - - - ACTIVITY - - - - - -     - - - - - - KEY INDICATORS - - - - - - -
TOTAL SQL.....................522     SQL: SELECT=     0, FETCH=   264
GETPAGES....................1,077     ZHYPERLINK ELIGIBLE =     47
SYNC READS (PRLL=00)...........67     SQL: DYNAMIC(PREPARE)=     6
PREFETCH PAGES READ...........105     I/O RSP: SYNC= 1,454 us, ASYNC= 3,697 us
UPDATES/COMMIT................0.0     LOCK SUSPENSIONS =      5
BFR HIT RATIOS:...........VP= 84%     RID LIST PROCESSING USED =    121
LOG RECORDS WRITTEN.............0

BMC SOFTWARE -------------- SUMMARY TRACE ENTRY    -----------PERFORMANCE MGMT
SERV ==> STRAC            INPUT   09:15:13 INTVL=> 3 LOG=> N TGT==> JXODEHF
PARM ==> T121608,SEQ=1                              ROW 1 OF 203 SCROLL=> CSR
EXPAND: MON(WKLD), DETAIL
        ACCOUNTING: ENV, ELAP, SQLCNTS, BPOOL, LOCKS, PRLL, PKG, RTN, ACC, DDF
        SUMMARIES: SQL, SCANS, IO/LOCK, SORTS
STOP.....13JUN 12.25.11.63 PLAN...............DSNTEP2 TYPE.........ACCELERATOR
START....13JUN 12.11.37.57 AUTHID............BOLJXO1 CONNECT........BATCH/TSO
ELAPSED...........00:13:34 ORIG PRIM AUTH....BOLJXO1 CORR ID.....BOLJXOAD
TERM........NORMAL/DEALLOC COMMITS..................1 ROLLBACKS..............0
------------------------------------------------------------------------------
RUNTIME ANALYSIS   IN DB2     IN APPL.      TOTAL      %IN DB2(=)     TOTAL(*)
----------------   --------   --------   --------     0 ...25...50...75..100%
ELAPSED TIME       00:11:01   00:02:33   00:13:34     | ================**** |
CPU TIME           00:03:52   00:01:45   00:05:37     |                      |
DB2 WAIT TIME      00:04:46                           | =======              |
- Specialty Engine CPU times - -
  - In DB2 CPU         0 us                  0 us     |                      |
  - STORED PROC        0 us
  - UDF                0 us
  - TRIGGER            0 us
- Accel-elig elpsd     0 us
  - Eligible CPU       0 us
  - Eligible zIIP      0 us
- - - - - - ACTIVITY - - - - - -     - - - - - - KEY INDICATORS - - - - - - -
TOTAL SQL.................22,000K     SQL: SELECT=     0, FETCH=   22M
GETPAGES......................176     ACCEL CONNECTS=     22, REQUESTS=     44
SYNC READS (PRLL=00)............0     SQL: DYNAMIC(PREPARE)=    24
PREFETCH PAGES READ.............0
UPDATES/COMMIT................0.0
BFR HIT RATIOS:...........VP=100%
LOG RECORDS WRITTEN.............0

The base section displays the most critical information. For more information, see Key indicators.

From the base section you can access each section of the STRAC view by hyperlinking on the section name in the view header, or by scrolling the view. For a detail trace, additional summary sections might be available for SQL activity, database scans, database lock and I/O activity, and sort activity.

You can use PF10/22 and PF11/23 to scroll through each trace buffer entry (thread). The current entry number is displayed in the SEQ= field. PF7/19 scrolls the display up and PF8/20 scrolls the display down.

Note

If the target for a historical trace is not equal to the current TGT field, it is displayed in a HIST TGT field.

STRAC comprises the following sections:

Hyperlink	Section name	Description
Accounting sections:
ENV	Environmental Indicators	Environmental indicators
ELAPSED	Elapsed time analysis	Elapsed time data if accounting class 2 or 3 is active
SQLCNTS	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
PKG	Package/DBRM Overview	Package/DBRM overview for accounting classes 7 and 8
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:
SQL	SQL Summary	SQL summary for detail traces
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

Note

All fields are formatted from the DB2 accounting record that is written by DB2 to the specified destination. A record is written for each DB2 thread and is available to the trace facility when the thread terminates. For more information, see the IBM publication, DB2 Administration Guide.

STRAC select code

STRAC

STRAC parameter

The following parameters can be specified:

Parameter	Description
trace id	A unique ID that identifies the trace. A blank can be an identifier. Therefore, 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.
,SEQ=	A unique internal sequence number assigned to each trace entry. The number is assigned sequentially throughout the trace, across multiple log data sets. If this keyword is not entered, the first entry in the trace buffer is displayed. The value after the equals sign can be overtyped to jump directly to any valid trace entry.
,ENV \| ELAPSED \| SQLCOUNTS \| BPOOL \| LOCKS \| PRLL \| DDF \| PKG \| SPAS \| ACC	A request to display a specific STRAC accounting section as described in the Expand section..
,SQL \| SCAN \| IOLOCK \| SORTS	A request to display a specific STRAC summary section as described in the Expand section.
,SORT=	A request to sort a qualified SQL or Database Summary display.

Expand

The STRAC display can be EXPANDed to the following displays:

EXPAND option	Description
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) From this panel you can select the plan or package and find the static SQL statement text to explain it.

EXPAND option	Description	Section	Description
ACCOUNTING	Format data from the DB2 accounting record	ENV	STRAC Environmental Indicators display section
		ELAPSED	STRAC Elapsed Time Analysis display section (accounting classes 2 and 3 only)
		SQLCOUNTS	STRAC SQL Statement Execution Counts display section
		BPOOL	STRAC Buffer Pool Usage Analysis display section
		LOCKS	STRAC Lock Activity display section
		PRLL	STRAC Parallelism display section
		RTN	STRAC Routines display section (available only if the thread has issued stored procedure calls or user-defined functions or has been invoked by a trigger)
		ACC	STRAC Accelerator display section
		DDF	STRAC DDF Summary display section (available only if there is DDF activity for the displayed thread)
		PKG	STRAC Package/DBRM Overview display section (available only when accounting trace 7 is active) From the Package/DBRM Overview section of STRAC, you can expand to Package/DBRM Pop-Up. Place the cursor on a line of data for a package or DBRM in the Package/DBRM Overview section of STRAC and press ENTER to view a pop-up display of detail statistics for that package or DBRM.
SUMMARIES	Format data summarized from detail trace events and are available only if these events are traced	SQL	STRAC SQL Summary display section You can expand to these two displays: SQL Statement Pop-UpPlace the cursor on a line of data for an SQL statement in the SQL Summary section of STRAC and press ENTER to view a pop-up display that summarizes all statistics from the individual occurrences of that statement. For a description of this pop-up display, see 'SQL statement pop-up display' in Event-pop-up-displays . DTRAC PGM=Place the cursor on the PGM line in the SQL Summary section of STRAC and press ENTER to request a DTRAC display of only those events from a given program. The statements displayed by DTRAC are qualified by the program name specified by the PGM field. Only those SQL statements issued by that originating program and all events subordinate to the selected SQL statements are shown. For more information about the DTRAC display, see DTRAC-Detail-Trace-Entry.
		SCANS	STRAC Database Summary display section
		IO/LOCK	STRAC Lock and I/O Summary display section
		SORTS	STRAC Sort Summary display section

You also can move to any one of several defined lines with Tab and press Enter to transfer to the following related displays:

Line identifier	STRAC section transferred to
CURRENT	ENV
ELAPSED TIME	ELAPSED
TOTAL SQL	SQLCOUNTS
GETPAGES	BPOOL
SYNC I/O (PRLL=nn)	PRLL
UPDATES/COMMIT	LOCKS

Any other position transfers to DTRAC.

Key indicators

A maximum of six of the most important key indicators of failures or degradation are displayed in the following order of importance.

Indicator	Description	Action	Field
These indicators show the cause of degradation and are highlighted
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
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 (PTFs BPD4919 and BPD4920 applied)	Indicates the number of read I/Os with DASD cache hits	Not applicable	QBACIOC
zHyperlink used=n (PTFs BPD4919 and BPD4920 applied)	Indicates the number of read I/Os that used zHyperLink	Not applicable	QBACSYI
IAG2 used=n (PTFs BPD4919 and BPD4920 applied)	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 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