3. REPORTS › 3.1 Tabular Reports Overview › 3.1.7 DB2 Plan Performance Report › 3.1.7.1 DB2 Plan Performance Report Format
3.1.7.1 DB2 Plan Performance Report Format
The DB2 Plan Performance Report contains the following
sections:
o Identification
o CPU Time
o Accounting Class 3 Events
o Thread Status
o SQL Calls
o Multiple Index Activity
o Locking
o Buffer Manager
o DDF Performance
o Page End
Identification Section
RUN DATE Date the report was created.
SYSID Logical system identifier.
DB2 SYSTEM DB2 subsystem identifier. (DB2ID)
RELEASE DB2 subsystem release level.
(DB2RELN)
PLAN DB2 plan(s) included in this
report. (DB2PLAN)
CORREL Job name of requesting
environment. (DB2CORR)
FACILITY Facility identifier of requesting
environment. (IMS, CICS, BATCH,
etc.) (DB2CONN)
THREADS The number of threads initiated
or the number of parent tasks
used to build this entry, when
query CP parallelism is in use.
(DSPNTHRD)
NOTE: In the calculation of
averages, a calculated
field DB2NTRAN is used.
DB2NTRAN is the greater of
DSPNTHRD or (DSPCOMM2 +
DSPABORT).
CPU Time Section
CLASS 1 ELAPSED TIME Elapsed time for this plan.
(DSPELPTM)
AVERAGE Average elapsed time for this DB2
PLAN. This average is calculated
as follows:
- If DB2NTRAN is equal to
DSPNTHRD and any of the PLAN
executions used query CP
parallelism:
<AVERAGE> = DSPELPTM/DB2NTRAN
- Otherwise:
<AVERAGE> = DSPTELPT/DB2NTRAN
MAXIMUM Maximum elapsed time for this DB2
PLAN. (DSPMELPT)
CLASS 2 ELAPSED TIME Total elapsed time spent in DB2
for this PLAN. (DSPET2TM)
AVERAGE Average elapsed time spent in DB2
for this DB2 PLAN. This average
is calculated as follows:
- If DB2NTRAN is equal to
DSPNTHRD and any of the PLAN
executions used query CP
parallelism:
<AVERAGE> = DSPET2TM/DB2NTRAN
- Otherwise:
<AVERAGE> = DSPTET2T/DB2NTRAN
MAXIMUM Maximum elapsed time spent in DB2
for this DB2 PLAN. (DSPMEL2T)
CLASS 1 TCB TIME Total TCB time for this PLAN.
This may include non-DB2 time for
facilities other than CICS.
(DSPTCBTM)
AVERAGE Average TCB time for this DB2
PLAN. This average is calculated
as DSPTCBTM/DB2NTRAN.
MAXIMUM Maximum TCB time for this DB2
PLAN. This may include non-DB2
time for facilities other than
CICS. (DSPMTCBT)
CLASS 2 TCB TIME Total TCB time spent in DB2
for this PLAN. (DSPTC2TM)
AVERAGE Average TCB time spent in DB2
for this DB2 PLAN. This average
is calculated as DSPTC2TM/
DB2NTRAN.
MAXIMUM Maximum TCB time spent in DB2 for
this DB2 PLAN. (DSPMTC2T)
CLASS 1 zIIP TIME Total CPU time executing on a
zIIP processor for this DB2 PLAN.
(DSPCPUZ1)
AVERAGE Average zIIP CPU time for this
DB2 PLAN. This average is
calculated as DSPCPUZ1/DB2NTRAN.
MAXIMUM Maximum zIIP CPU time for this
DB2 PLAN. (DSPMXZP1)
CLASS 2 zIIP TIME Total CPU time spent in DB2
executing on a zIIP processor
for this DB2 PLAN. (DSPCPUZ2)
AVERAGE Average zIIP CPU time spent in
DB2 for this DB2 PLAN.
This average is calculated as
DSPCPUZ2/DB2NTRAN.
MAXIMUM Maximum zIIP CPU time spent in
DB2 for this DB2 PLAN.
(DSPMXZP2)
zIIP ELIGIBLE TIME Total CPU time that executed on a
standard CP for zIIP-eligible
work for this DB2 PLAN.
(DSPELGZP)
AVERAGE Average CPU time that executed on
a standard CP for zIIP-eligible
work for this DB2 PLAN. This
average is calculated as
DSPELGZP/DB2NTRAN.
MAXIMUM Maximum CPU time that executed on
a standard CP for zIIP-eligible
work for this DB2 PLAN.
(DSPMXZET)
Accounting Class 3 Events
TOTAL I/O TIME Total time the IMS, CICS, or TSO
user's address space waited for
I/O in DB2. (DSPIOETM)
AVERAGE I/O TIME Average time spent waiting on DB2
I/O for this PLAN. This average
is calculated as DSPIOETM/
DB2NTRAN.
MAXIMUM I/O TIME Maximum time this DB2 PLAN waited
for I/O in DB2. (DSPMIOTM)
TOTAL LOCK AND LATCH Total time this DB2 PLAN waited
TIME for locks and latches in DB2.
(DSPALLTM)
AVERAGE LOCK AND LATCH Average time this DB2 PLAN
TIME waited for locks and latches in
DB2. This average is calculated
as DSPALLTM/DB2NTRAN.
MAXIMUM LOCK AND LATCH Maximum time this DB2 PLAN waited
TIME for locks and latches in DB2.
(DSPMLLTM)
TOTAL OTHER READ TIME Total time this DB2 PLAN waited
for read I/O under another
thread. (DSPAWRTM)
AVERAGE OTHER READ TIME Average time this DB2 PLAN waited
for read I/O under another
thread. (DSPAWRTM/DB2NTRAN)
MAXIMUM OTHER READ TIME Maximum time this DB2 PLAN waited
for read I/O under another
thread. (DSPMWRTM)
TOTAL OTHER WRITE TIME Total time this DB2 PLAN waited
for write I/O under another
thread. (DSPAWWTM)
AVERAGE OTHER WRITE TIME Average time this DB2 PLAN waited
for write I/O under another
thread. (DSPAWWTM/DB2NTRAN)
MAXIMUM OTHER WRITE TIME Maximum time this DB2 PLAN waited
for write I/O under another
thread. (DSPMWWTM)
TOTAL SERVICE TASK TIME Total time this DB2 PLAN waited
due to synchronous execution
switch to DB2 services from a
thread. (DSPAWETM)
AVERAGE SERVICE TASK Average time this DB2 PLAN waited
TIME due to synchronous execution
switch to DB2 services from a
thread. (DSPAWETM/DB2NTRAN)
MAXIMUM SERVICE TASK Maximum time this DB2 PLAN waited
TIME due to synchronous execution
switch to DB2 services from a
thread. (DSPMWETM)
TOTAL ARCHIVE LOG TIME Total time this DB2 PLAN waited
due to processing of ARCHIVE LOG
MODE(QUIESCE) commands.
(DSPALGTM)
AVERAGE ARCHIVE LOG Average time this DB2 PLAN waited
TIME due to processing of ARCHIVE LOG
MODE(QUIESCE) commands.
(DSPALGTM/DB2NTRAN)
MAXIMUM ARCHIVE LOG Maximum time this DB2 PLAN waited
TIME due to processing of ARCHIVE LOG
MODE(QUIESCE) commands.
(DSPMLGTM)
TOTAL ENTRY/EXIT EVENTS Number of entry and exit events
processed to calculate the
accounting class 2 elapsed times
in DB2. (DSPENTEX)
AVERAGE ENTRY/EXIT Average number of entry and exit
EVENTS events processed to calculate the
accounting class 2 elapsed times
in DB2. This average is
calculated as DSPENTEX/DB2NTRAN.
MAXIMUM ENTRY/EXIT Maximum number of entry and exit
EVENTS events processed to calculate the
accounting class 2 elapsed times
in DB2. (DSPMENTX)
TOTAL WAIT TRACE EVENTS Number of entry and exit
events processed to calculate the
I/O, lock, and latch elapsed wait
times. (DSPWATTR)
AVERAGE WAIT TRACE Average number of entry and exit
EVENTS events processed to calculate the
I/O, lock, and latch elapsed wait
times. This average is
calculated as DSPWATTR/DB2NTRAN.
MAXIMUM WAIT/TRACE Maximum number of entry and exit
EVENTS events processed to calculate the
I/O, lock, and latch elapsed wait
times. (DSPMWATR)
Thread Status Section
NORMAL End of task, application program
END OF TASK terminated. (DSPNTEOT)
NORMAL End of task, application program
APPLICATION ENDED termination. (DSPNTEND)
NORMAL Deallocation, normal program
DEALLOCATION termination. (DSPNTDEL)
NORMAL New user, authorization ID
NEW USER changed. (DSPNTNUS)
COMMIT PHASE 2 COMMIT phase 2 successful
completion for IMS or CICS
environments. (DSPCOMM2)
ABNORMAL End of task, application program
APPLICATION ABENDED ABEND. (DSPABABD)
ABNORMAL End of memory, abnormal
END OF MEMORY termination. (DSPWUEOM)
ABNORMAL Resolve indoubt. (DSPABRIN)
RESOLVE INDOUBT
ABNORMAL STOP DB2 MODE (FORCE) command
FORCE COMMAND issued. (DSPABFRC)
ABORT REQUESTS Abort Plan activity request
count. (DSPABORT)
WORK UNIT IN DOUBT End of task, application program
APPLICATION ABENDED ABEND. (DSPWUABD)
WORK UNIT IN DOUBT End of memory, abnormal
END OF MEMORY termination. (DSPWUEOM)
WORK UNIT IN DOUBT Resolve indoubt. (DSPWURIN)
RESOLVE INDOUBT
WORK UNIT IN DOUBT STOP DB2 MODE (FORCE) command
FORCE COMMAND issued. (DSPWUFRC)
SQL Calls Section
SELECT Number of SELECT statements.
(DB2SELECT)
INSERT Number of INSERT statements.
(DSPINSRT)
UPDATE Number of UPDATE statements.
(DSPUPDTE)
DELETE Number of DELETE statements.
(DSPDELET)
DESCRIBE Number of DESCRIBE statements.
(DSPDESC)
PREPARE Number of PREPARE statements.
(DSPPREP)
OPEN Number of OPEN statements.
(DSPOPEN)
CLOSE Number of CLOSE statements.
(DSPCLOSE)
FETCH Number of FETCH statements.
(DSPFETCH)
LABEL Number of LABEL statements.
(DSPLABEL)
DESCRIBE TABLE Number of DESCRIBE TABLE
statements.
(DSPDSCRT)
LOCK TABLE Number of LOCK TABLE statements.
(DSPLOCK)
GRANT Number of GRANT statements.
(DSPGRANT)
REVOKE Number of REVOKE statements.
(DSPREVOK)
INCREMENTAL BIND Number of INCREMENTAL BINDs
excluding prepares. (DSPINCRB)
COMMENT Number of COMMENT ON statements.
(DSPCMTON)
SET SQLID Number of SET CURRENT SQLID
statements. (DSPSQLID)
SET HOST VARIABLE Number of SET HOST VARIABLE
statements. (DSPSETHV)
CREATE TABLE Number of CREATE TABLE
statements. (DSPCRTAB)
CREATE INDEX Number of CREATE INDEX
statements. (DSPCRINX)
CREATE TABLESPACE Number of CREATE TABLESPACE
statements. (DSPCTABS)
CREATE STOGROUP Number of CREATE STOGROUP
statements. (DSPCRSTG)
CREATE DATABASE Number of CREATE DATABASE
statements. (DSPCRDAB)
CREATE SYNONYM Number of CREATE SYNONYM
statements. (DSPCRSYN)
CREATE VIEW Number of CREATE VIEW statements.
(DSPDEFVU)
CREATE ALIAS Number of CREATE ALIAS
statements. (DSPCRAL)
DROP TABLE Number of DROP TABLE statements.
(DSPDRPTA)
DROP INDEX Number of DROP INDEX statements.
(DSPDRPIX)
DROP TABLESPACE Number of DROP TABLESPACE
statements. (DSPDRPTS)
DROP STOGROUP Number of DROP STOGROUP
statements. (DSPDRPST)
DROP DATABASE Number of DROP DATABASE
statements. (DSPDRPDB)
DROP SYNONYM Number of DROP SYNONYM
statements. (DSPDRPSY)
DROP VIEW Number of DROP VIEW statements.
(DSPDRPVU)
DROP ALIAS Number of DROP ALIAS statements.
(DSPDRAL)
DROP PACKAGE Number of DROP PACKAGE
statements. (DSPDRPKG)
ALTER TABLE Number of ALTER TABLE statements.
(DSPALTTA)
ALTER INDEX Number of ALTER INDEX statements.
(DSPALTIX)
ALTER TABLESPACE Number of ALTER TABLESPACE
statements. (DSPALTTS)
ALTER STOGROUP Number of ALTER STOGROUP
statements. (DSPALTST)
ALTER DATABASE Number of ALTER DATABASE
statements. (DSPALDAB)
Multiple Index Activity Section
MIX INDEX USED Number of times a multiple index
access path was used. (DSPMIXU)
MIX INDEX NOT USED Number of times multiple index
STORAGE LIMIT EXCEEDED access was not used because there
was not enough storage for RIDs.
(DSPMIXNU)
MIX INDEX NOT USED Number of times multiple index
RID LIMIT EXCEEDED access was not used because the
RID limit was exceeded.
(DSPMIXNL)
Locking Section
DEADLOCKS Total deadlock count for all
PLANs included in this summation.
(DSPDEAD)
AVERAGE Average deadlock count.
(AVGDEAD)
MAXIMUM Maximum deadlock count for any
PLAN in this summation.
(DSPMDEAD)
SUSPENSIONS Total suspension count for all
PLANs included in this summation.
(DSPSUSP)
AVERAGE Average suspend count. (AVGSUSP)
MAXIMUM Maximum suspend count for any
PLAN in this summation.
(DSPMSUSP)
TIMEOUTS Total Timeout count for all PLANs
included in this summation.
(DSPTIMO)
AVERAGE Average timeout count. (AVGTIMO)
MAXIMUM Maximum timeout count for any
PLAN in this summation.
(DSPMTIMO)
MAX. CONCUR. PAGELOCK Total maximum number of page
locks held by a DB2 user for all
PLANs included in this summation.
(DSPMXPL)
AVERAGE Average maximum-page-locks count.
(AVGMXPL)
MAXIMUM Maximum maximum-page-locks count
for any PLAN in this summation.
(DSPMMXPL)
LOCK/LATCH TIME Total Time spent waiting on IRLM
LOCKS or DB2 LATCHes for all
PLANs included in this summation.
(DSPALLTM)
AVERAGE Average LOCK/LATCH time.
(AVGLLTM)
MAXIMUM Maximum LOCK/LATCH time for any
PLAN in this summation.
(DSPMLLTM)
LATCH SUSPEND COUNT Total count of suspend requests
due to DB2 latch contention for
all PLANs included in this
summation. (DSPASLAT)
AVERAGE Average LATCH suspend request
count. (AVGSLAT)
MAXIMUM Maximum LATCH suspend requests
for any PLAN in this summation.
(DSPMSLAT)
OTHER SUSPEND COUNT Total count of suspend requests
for something other than
LATCH contention for all PLANs
included in this summation.
(DSPASOTH)
AVERAGE Average other suspend request
count. (AVGASOTH)
MAXIMUM Maximum other suspend requests
for any PLAN in this summation.
(DSPMSOTH)
ESCALATE TO SHARED Total number of lock escalations
to shared mode (DSPLESM) for all
PLANs included in this summation.
(DSPASOTH)
AVERAGE Average other suspend request
count. (AVGASOTH)
MAXIMUM Maximum other suspend requests
for any PLAN in this summation.
(DSPMSOTH)
ESCALATE TO EXCLUSIVE Total number of locks escalated
to exclusive mode for all PLANs
included in this summation.
(DSPLEEM)
AVERAGE Average count of locks escalated
to exclusive mode. (AVGLEEM)
MAXIMUM Maximum count of locks escalated
to exclusive mode for any PLAN in
this summation. (DSPMLEEM)
LOCK REQUEST COUNT Total IRLM lock requests for all
PLANs included in this summation.
(DSPALOCK)
AVERAGE Average lock requests count.
(AVGLOCK)
MAXIMUM Maximum count of lock requests
for any PLAN in this summation.
(DSPMLOCK)
QUERY REQUEST COUNT Total IRLM query requests for
lock status for all PLANs
included in this summation.
(DSPAQRY)
AVERAGE Average IRLM query requests
count. (AVGQRY)
MAXIMUM Maximum count of IRLM query
requests for any PLAN in this
summation. (DSPMQRY)
CHANGE LOCK REQUESTS Total IRLM change lock requests
for all PLANs included in this
summation. (DSPACHG)
AVERAGE Average IRLM change lock requests
count. (AVGACHG)
MAXIMUM Maximum count of IRLM change lock
requests for any PLAN in this
summation. (DSPMCHG)
IRLM LOCK REQUEST Total lock requests processed by
(OTHER) IRLM other than the DB2 LOCKS
already listed for all PLANs
included in this summation.
(DSPAIRLM)
AVERAGE Average IRLM other lock requests
count. (AVGAIRLM)
MAXIMUM Maximum count of IRLM other lock
requests for any PLAN in this
summation. (DSPMIRLM)
RESOURCE TABLE ID Table id of resource limit table
used to determine CPU limits for
dynamic PLAN execution.
(DSPARLID)
HOW LIMIT WAS DETERMINED Method resource limit was
determined. (LIMITFLG)
SERVICE UNIT TIME LIMIT Number of CPU services units
allowed per dynamic SQL requests.
(DSPASLMT)
CPU TIME LIMIT CPU time limit allowed per
dynamic SQL requests. (DSPACLMT)
HIGHEST CPU TIME USED Highest CPU time used for a
dynamic SQL statement. (DSPACHUS)
Buffer Manager Section
GETPAGE REQUESTS Total number of GETPAGE requests
for all PLANs included in this
TOTAL summation. (DSPSGPGR)
AVERAGE Average GETPAGE requests count.
(AVGSGPGR)
MAXIMUM Maximum GETPAGE requests for any
PLAN in this summation.
(DSPMSGPG)
RECORD IN SYSTEM Total number of records residing
PAGE UPDATED in a system page that were
updated for all PLANs included in
TOTAL this summation. (DSPSSPRU)
AVERAGE Average count of system page
updates. (AVGSSPRU)
MAXIMUM Maximum system page updates for
any PLAN in this summation.
(DSPMSSPU)
SYNCHRONOUS READ I/O Total number of synchronous read
I/Os for all PLANs included in
TOTAL this summation. (DSPSSRIO)
AVERAGE Average synchronous read I/O
count. (AVGSSRIO)
MAXIMUM Maximum synchronous read I/O
count for any PLAN in this
summation. (DSPMSSRD)
SYNCHRONOUS WRITE I/O Number of synchronous write I/Os
for all PLANs included in this
TOTAL summation. (DSPSCIMW)
AVERAGE Average synchronous write I/O
count. (AVGSCIMW)
MAXIMUM Maximum synchronous write I/O
count for any PLAN in this
summation. (DSPMSSWR)
SEQUENTIAL PREFETCH Total number of sequential
REQUESTS prefetch read requests for all
PLANs included in this summation.
(DSPSPRFH)
AVERAGE Average sequential prefetch read
request count. (AVGSPRFH)
MAXIMUM Maximum sequential prefetch read
request count for any PLAN in
this summation. (DSPMSPFR)
LIST PREFETCH REQUESTS Total number of list read
requests for all PLANs included
in this summation. (DSPSLPF)
AVERAGE Average list prefetch read
request count. (AVGSLPF)
MAXIMUM Maximum list prefetch read
request count for any PLAN in
this summation. (DSPMSLPF)
DYNAMIC PREFETCH Total number of Dynamic Prefetch
REQUESTS Requests for all active DB2
buffer pools. Dynamic prefetch
is the process that is triggered
because of sequential detection.
(DSPSDPF)
AVERAGE Average number of Dynamic
Prefetch Requests, summarized
across all active DB2 buffer
pools. (AVGSDPF)
MAXIMUM Maximum number of Dynamic
Prefetch Requests, summarized
across all active DB2 buffer
pools. (DSPMSDPF)
UNSUCCESSFUL GET PAGE Total number of times conditional
OPERATIONS getpage requests could not be
satisfied for all active DB2
buffer pools. (DSPSNGT)
AVERAGE Average number of times
conditional getpage requests
could not be satisfied for all
active DB2 buffer pools.
(AVGSNGT)
MAXIMUM Maximum number of times
conditional getpage requests
could not be satisfied for all
active DB2 buffer pools.
(DSPMSNGT)
ASYNC PAGES READ BY Total number of synchronous read
PREFETCH I/Os issued by sequential access
requesters, summarized across all
active DB2 buffer pools.
(DSPSSIO)
AVERAGE Average number of synchronous
read I/Os issued by sequential
access requesters, summarized
across all active DB2 buffer
pools. (AVGSSIO)
MAXIMUM Maximum number of synchronous
read I/Os issued by sequential
access requesters, summarized
across all active DB2 buffer
pools. (DSPMSSIO)
DDF Performance Section
LOCATION Location Identifier for a Remote
DB2 Subsystem. (DB2RLOCN)
DDF THREADS Count of SMF 101 DDF Performance
segments included in this entry.
The second and third lines of
this column indicate average and
maximum reporting for each
location. (DDPNTHRD)
LOCAL ELAPSED Total time spent waiting at the
local DB2 for remote DB2
processing of a distributed
request. (DDPELPL)
REMOTE ELAPSED Total time spent processing
distributed request as measured
from the remote serving DB2
subsystem. (DDPELPR)
REMOTE CPU Total CPU time (SRB at remote
site) spent processing SQL
requests. (DDPCPUR)
CONV. VTAM Conversations Sent or
Received. Sent - (DDPCNVS),
Received - (DDPCNVR)
CONVERS. QUEUED VTAM Conversations Queued
(DDPCNVQ) to be sent to remote
site.
ABORTS (SENT/RECEIVED) Abort Messages Sent or Received.
Sent - (DDPABRS), Received -
(DDPABRR)
COMMITS (SENT/RECEIVED) COMMIT Messages Sent or Received.
Sent - (DDPCOMS), Received -
(DDPCOMR)
MESSAGES SENT TO VTAM Messages sent to the remote
site. (DDPMSGS)
MESSAGES RECEIVED FROM VTAM Messages received from the
remote site. (DDPMSGR)
SQL SENT/RECEIVED SQL Statements Sent or Received.
Sent - (DDPSQLS), Received -
(DDPSQLR)
DATA ROWS SENT Data rows sent to the remote
site. (DDPROWS)
DATA ROWS FROM Data rows received from the
remote site. (DDPROWR)
BYTES/1000 SENT TO Number of bytes sent to the
remote site (DDPROWS) divided by
1000. If the total byte count is
less than 10000 then the division
by 1000 is not performed and the
report variable is flagged.
BYTES/1000 RECEIVED Number of bytes received from
FROM remote (DDPROWR) divided by 1000.
If the total byte count is less
than 10000 then the division by
1000 is not performed and the
report variable is flagged.
Page End Section
REPORTING PERIOD Earliest and latest dates and
times found within the data.
