3. REPORTS › 3.1 Tabular Reports Overview › 3.1.3 DB2 Data Base Services Report › 3.1.3.1 DB2 Database Services Report Format
3.1.3.1 DB2 Database Services Report Format
The DB2 Database Services Report contains the following
sections:
o Identification
o Thread Summary
o SQL Calls
o Resource Limit Facility
o Plan Activity
o Package Activity
o Locking
o Buffer Manager
o Environmental Descriptor Management Pool
o Data Set Activity
Identification Section
RUN DATE Date the report was created.
REPORTING PERIOD Earliest and latest dates and
times found within the data.
SYSID Logical system identifier.
DB2 SYSTEM DB2 subsystem identifier.
DB2 RELEASE DB2 subsystem release identifier.
(DB2RELN)
Thread Summary Section
THREADS Number of threads created.
SQL Calls Section
SELECT Number of SELECT statements.
INSERT Number of INSERT statements.
UPDATE Number of UPDATE statements.
DELETE Number of DELETE statements.
DESCRIBE Number of DESCRIBE statements.
PREPARE Number of PREPARE statements.
OPEN Number of OPEN statements.
CLOSE Number of CLOSE statements.
FETCH Number of FETCH statements.
LABEL Number of LABEL statements.
DESCRIBE TABLE Number of DESCRIBE TABLE
statements.
LOCK TABLE Number of LOCK TABLE statements.
GRANT Number of GRANT statements.
REVOKE Number of REVOKE statements.
INCREMENTAL BIND Number of incremental binds
excluding prepares.
COMMENT Number of COMMENT ON statements.
SET SQLID Number of SET CURRENT SQLID
statements.
SET HOST VARIABLE Number of SET HOST VARIABLE
statements.
CREATE TABLE Number of CREATE TABLE
statements.
CREATE INDEX Number of CREATE INDEX
statements.
CREATE TABLESPACE Number of CREATE TABLESPACE
statements.
CREATE STOGROUP Number of CREATE STOGROUP
statements.
CREATE DATABASE Number of CREATE DATABASE
statements.
CREATE SYNONYM Number of CREATE SYNONYM
statements.
CREATE VIEW Number of CREATE VIEW statements.
DROP TABLE Number of DROP TABLE statements.
DROP INDEX Number of DROP INDEX statements.
DROP TABLESPACE Number of DROP TABLESPACE
statements.
DROP STOGROUP Number of DROP STOGROUP
statements.
DROP DATABASE Number of DROP DATABASE
statements.
DROP SYNONYM Number of DROP SYNONYM
statements.
DROP VIEW Number of DROP VIEW statements.
DROP PACKAGE Number of DROP PACKAGE
statements.
ALTER TABLE Number of ALTER TABLE statements.
ALTER INDEX Number of ALTER INDEX statements.
ALTER TABLESPACE Number of ALTER TABLESPACE
statements.
ALTER STOGROUP Number of ALTER STOGROUP
statements.
ALTER DATABASE Number of ALTER DATABASE
statements.
ALTER SYNONYM Number of ALTER SYNONYM
statements.
ALTER VIEW Number of ALTER VIEW statements.
ALTER PACKAGE Number of ALTER PACKAGE
statements
Resource Limit Facility Section
RESOURCE TABLE ID Nature of resource limit in
effect.
HOW LIMIT WAS DETERMINED Method resource limit was
determined.
SERVICE UNIT TIME LIMIT Number of CPU services units
allowed per dynamic SQL requests.
CPU TIME LIMIT CPU time limit allowed per
dynamic SQL requests.
HIGHEST CPU TIME USED Highest CPU time used for a
dynamic SQL statement.
Locking Section
DEADLOCKS Deadlock count.
SUSPENSIONS Suspension count.
TIMEOUTS Timeout count.
ESCALATED TO SHARED Number of escalations to shared
mode.
ESCALATED TO EXCLUSIVE Number of escalations to
exclusive mode.
MAX. PAGE LOCKS HELD Maximum number of page locks held
by DB2.
LATCH SUSPEND COUNT Number of suspends due to latch
conflict.
OTHER SUSPEND COUNT Number of suspends due to
something other than latch
conflict.
LOCK REQUEST COUNT Total number of IRLM lock
requests.
QUERY REQUEST COUNT Total number of IRLM query
requests.
CHANGE REQUEST COUNT Total number of IRLM change
requests.
OTHER IRLM REQUEST COUNT Total number of other IRLM
requests.
Plan Activity Section
ALLOCATE ATTEMPTS Requests to allocate a bound plan
for an agent. Represents the
number of times DB2 was requested
to create a thread by the
attachment facility for the user.
Does not include allocations for
DB2 system agents.
ALLOCATE SUCCESS Number of successful bound plan
allocations. Represents the
number of allocation attempts
counted in ALLOCATE ATTEMPTS that
completed successfully.
ALLOCATE NON-EXISTENT Requests to allocate a
PLANS non-existent plan ID. Represents
the number of agent allocation
attempts counted in ALLOCATE
ATTEMPTS that did not complete
successfully, because the plan
did not exist.
BIND ATTEMPTS Number of times an automatic bind
was attempted. Occurs when the
plan has been invalidated by
modifications to the declarations
of the data referenced by the
programs bound as part of the
plan.
BIND SUCCESS Number of successful automatic
binds.
BIND ADD Number of BIND ACTION (ADD)
subcommands issued.
BIND REPLACE Number of BIND ACTION (REPLACE)
subcommands issued.
BIND TEST Number of bind subcommands issued
without a plan ID.
BIND BOUND AND Number of plans successfully
KEPT bound and kept for future agent
allocations. This count does not
include the test binds.
REBIND COMMANDS Number of REBIND subcommands
issued.
REBIND ATTEMPTS Number of attempts to rebind an
individual plan.
REBIND SUCCESS Number of times a plan was
successfully rebound.
FREE COMMANDS Number of FREE subcommands
issued.
FREE ATTEMPTS Number of attempts to free an
individual plan.
FREE SUCCESS Number of times a plan was
successfully freed.
Package Activity Section
ALLOCATE ATTEMPTS Requests to allocate a bound
package for an agent.
Represents the number of times
DB2 was requested to create a
thread by the attachment
facility for the user. Does not
include allocations for DB2
system agents.
ALLOCATE SUCCESS Number of successful bound
package allocations. Represents
the number of allocation
attempts counted in ALLOCATE
ATTEMPTS that completed
successfully.
BIND ATTEMPTS Number of times an automatic bind
was attempted. Occurs when the
package has been invalidated by
modifications to the declarations
of the data referenced by the
programs bound as part of the
package.
BIND SUCCESS Number of successful automatic
binds.
BIND ADD Number of BIND ACTION (ADD)
subcommands issued.
BIND REPLACE Number of BIND ACTION (REPLACE)
subcommands issued.
BIND BOUND AND Number of packages successfully
KEPT bound and kept for future agent
allocations. This count does not
include the test binds.
REBIND COMMANDS Number of REBIND subcommands
issued.
REBIND ATTEMPTS Number of attempts to rebind an
individual package.
REBIND SUCCESS Number of times a package was
successfully rebound.
FREE COMMANDS Number of FREE subcommands
issued.
FREE ATTEMPTS Number of attempts to free an
individual package.
FREE SUCCESS Number of times a package was
successfully freed.
Buffer Manager Section (listed in alphabetical order)
ACTIVE BUFFERS IN Total number of active buffers,
POOL summarized across all active DB2
buffer pools. (DSDSABBP)
BUFFER POOL DATA Total number of buffer pool data
SET OPENED sets that were opened by the DB2
system for all active DB2 buffer
pools. (DSDSBPSO)
BUFFERS ALLOCATED Total number of buffers
FOR HPOOL allocated for all active virtual
buffer pools. (DSDSHPL)
BUFFERS ALLOCATED Total number of buffers allocated
FOR VPOOL for a virtual buffer pool,
summarized across all active DB2
buffer pools. (DSDSVPL)
DATA MANAGER Total number of times the WPHFWT
THRESHOLD REACHED deferred write threshold was
reached in the DB2 system during
an interval for all active
buffer pools. (DSDSDMCT)
DATASET RECALL Total number of times a recall
TIMEOUTS time out of a migrated data set
was encountered by the DB2 system
during the interval, summarized
across all active DB2 buffer
pools. (DSDSTRTO)
DEFERRED WRITE Total number of times the
THRESHOLD REACHED deferred write threshold was
reached in the DB2 system during
an interval for all active DB2
buffer pools. (DSDSDWRT)
DYNAMIC PREFETCH Total number of asynchronous
READ I/OS read I/Os due to dynamic prefetch
for all active DB2 buffer pools.
(DSDSDIO)
DYNAMIC PREFETCH Total number of dynamic
REQUESTED prefetch requests for all active
DB2 buffer pools. Dynamic
prefetch is the process that is
triggered because of sequential
detection. (DSDSDPF)
GETPAGE FAILURES DUE Total number of times a usable
TO VPOOL FULL buffer could not be located in
the active virtual buffer pools.
(DSDSXFL)
GETPAGES ISSUED BY Total number of GETPAGE
SEQUENTIAL REQUEST requests issued by sequential
access requesters, summarized
across all active DB2 buffer
pools. (DSDSSGT)
HIGHEST PREFETCH Total number of concurrent
I/O STREAMS ALLOCATED prefetch I/O streams that were
allocated for supporting queries
processed in parallel for all
active DB2 buffer pools.
(DSDSXIS)
HPOOL BUFFERS BACKED Total number of hiperpool
BY EXPANDED STORAGE buffers currently backed by
expanded storage for all active
DB2 buffer pools. (DSDSHBE)
I/O PARALLELISM Total number of times that DB2
DOWNGRADED SHORT ON could not allocate the requested
STORAGE number of buffer pages to allow
a parallel group to run to the
planned degree, summarized across
all active DB2 buffer pools.
(DSDSPQF)
IMMEDIATE WRITES Total number of immediate writes
SYSTEM PAGES for system pages by the DB2
system during the interval for
all active DB2 buffer pools.
(DSDSIMWS)
INEFFICIENT MERGE DUE Total number of times that the
TO NO BUFFERS merge pass could not be
efficiently performed, summarized
for all active DB2 buffer pools,
due to the shortage of buffer
space. (DSDSWFF)
LIST PREFETCH READ Total number of asynchronous read
I/OS I/Os due to list prefetch for all
active DB2 buffer pools.
(DSDSLIO)
LIST PREFETCH REQUESTS Total number of list prefetch
requests for all active DB2
buffer pools. (DSDSLPF)
MAX. WORKFILES Total number of work files (runs)
CONCURRENTLY USED that have ever been concurrently
FOR MERGE used during merge processing
within this statistics period,
summarized for all active DB2
buffer pools. (DSDSWFM)
MEDIA MANAGER READ Total number of media manager
REQUESTS read requests for all active DB2
buffer pools. (DSDSMMRR)
MERGE PASSES FOR Total number of merge passes DB2
SORT sort activities summarized for
all active DB2 buffer pool.
(DSDSWFR)
MIGRATED DATASET Total of the number of times
ENCOUNTERED migrated data sets were
encountered, summarized across
all active DB2 buffer pools.
(DSDSTMIG)
PAGES DEQUEUED FOR Total number of pages dequeued
DESTRUCTIVE READ from VDWQ for destructive read,
summarized across all active DB2
buffer pools. (DSDSWBVQ)
PAGES DESTRUCTIVELY Total number of pages for which
READ destructive read was requested,
summarized across all active DB2
buffer pools. (DSDSWDRP)
PAGES READ ASYNC FROM Total number of pages moved from
HPOOL TO VPOOL all active hiperpools to all
active virtual buffer pools
asynchronously. (DSDSHRA)
PAGES READ ASYNC HPOOL Total number of pages moved from
TO VPOOL VIA ADMF a hiperpool to a virtual buffer
pool via ADMF for all active DB2
buffer pools. (DSDSARA)
PAGES READ DUE TO Number of pages read by DB2 after
PREFETCH it anticipated the need to read
ahead due to a large volume of
sequential processing, which can
be caused either by a utility or
an SQL statement performing a
large tablespace scan.
(DSDSSPPF)
PAGES READ DYNAMIC Total number of pages read
PREFETCH asynchronously due to dynamic
prefetch for all active DB2
buffer pools. (DSDSDPP)
PAGES READ LIST Total number of pages read
PREFETCH asynchronously due to list
prefetch for all active DB2
buffer pools. (DSDSLPP)
PAGES READ SYNC FROM Total number of successful
HPOOL TO VPOOL synchronous requests to move
pages from all active hiperpools
to all active virtual buffer
pools. (DSDSHRE)
PAGES WRITTEN ASYNC Total number of pages moved from
VPOOL TO HPOOL VIA a virtual buffer pool to a
ADMF hiperpool via ADMF for all active
DB2 buffer pools. (DSDSAWA)
PAGES WRITTEN SYNC Total number of pages moved from
FROM HPOOL TO VPOOL all active hiperpools to all
active virtual buffer pool
asynchronously. (DSDSHRA)
PHYSICAL DATABASE Count of physical database page
PAGE REQUESTS requests, summarized across all
active DB2 buffer pools.
(DSDSDBPR)
PREFETCH DISABLED DUE Total number of sequential
TO NO BUFFERS prefetch requests disabled due to
an unavailable buffer resource in
the DB2 system during an
interval, summarized across all
active DB2 buffer pools.
(DSDSSPDF)
PREFETCH DISABLED DUE Total number of sequential
TO NO READ ENGINE prefetch requests disabled due to
an unavailable read engine in the
DB2 system during an interval,
summarized across all active DB2
buffer pools. (DSDSREEP)
PREFETCH I/O STREAMS Total number of requested
DENIED SHORT ON prefetch I/O streams that were
STORAGE denied due to a storage shortage
for all active DB2 buffer pools.
(DSDSJIS)
PREFETCH QUANTITY Total number of times that the
REDUCED TO 25% NORMAL prefetch quantity was reduced
from normal to 1/4 normal,
summarized across all active DB2
buffer pools. (DSDSPL2)
PREFETCH QUANTITY Total number of times that the
REDUCED TO 50% NORMAL prefetch quantity was reduced
from normal to half normal,
summarized across all active DB2
buffer pools. (DSDSPL1)
READ PAGEFIXED WITHOUT Total number of times that a
REAL STORAGE buffer being page-fixed for read
operations did not have real
storage frame backing, summarized
across all active DB2 buffer
pools. (DSDSRFXS)
SEQUENTIAL PREFETCH Total number of sequential
REQUESTS prefetch requests of the DB2
system that occurred during the
interval, summarized across all
active DB2 buffer pools.
(DSDSSQPF)
SUCCESSFUL HPOOL Total number of successful
EXPANSION/CONTRACTION hiperpool expansions or
contractions because of the ALTER
BUFFERPOOL command for all active
buffer pools. (DSDSHPA)
SUCCESSFUL VPOOL Total number of successful
EXPANSION/CONTRACTION virtual buffer pool expansions or
contractions because of the ALTER
BUFFERPOOL command, summarized
across all active DB2 buffer
pools. (DSDSVPA)
SYNC READ I/OS Total number of synchronous read
PERFORMED BY I/Os issued by sequential access
SEQUENTIAL REQ requesters, summarized across all
active DB2 buffer pools.
(DSDSSIO)
SYSTEM PAGES WRITTEN Total number of system pages that
TO DASD were written to DASD, summarized
across all active DB2 buffer
pools. (DSDSSPWR)
TOTAL PARALLEL QUERIES Total number of requests made for
processing queries in parallel,
summarized across all active DB2
buffer pools. (DSDSPQO)
UNSUCCESSFUL GETPAGE Total number of times conditional
CONDITIONAL REQUEST GETPAGE requests could not be
satisfied for all active DB2
buffer pools. (DSDSNGT)
UNSUCCESSFUL HPOOL TO Total number of pages for which a
VPOOL READS synchronous or asynchronous read
request failed because the
backing-expanded storage page was
stolen by the system, summarized
for all active DB2 buffer pools.
(DSDSHRF)
UNSUCCESSFUL HPOOL TO Total number of pages for which a
VPOOL READS VIA ADMF read request failed because the
backing-expanded storage was
stolen for active DB2 buffer
pools. (DSDSARF)
UNSUCCESSFUL VPOOL TO Total number of pages for which
HPOOL WRITES a synchronous or asynchronous
write request failed because of a
shortage of expanded storage for
all active DB2 buffer pools.
(DSDSHWF)
UNSUCCESSFUL VPOOL TO Total number of pages for which a
HPOOL WRITES VIA ADMF write request failed because the
backing-expanded storage was
stolen for active DB2 buffer
pools. (DSDSAWF)
UPDATES OF DATABASE Total count (for all active
SYSTEM PAGES buffer pools) of the updates that
were performed against database
system pages during the interval.
(DSDSDBPU)
USABLE BUFFER NOT Total number of times a usable
LOCATED BECAUSE VPOOL buffer could not be located in
FULL the active virtual buffer pools.
(DSDSXFL)
VERTICAL DEFERRED WRITE Number of times the vertical
THRESHOLD REACHED deferred write threshold was
reached for all active DB2 buffer
pools. When the number of
updated pages for a data set
exceeds the vertical deferred
write threshold, deferred writes
are initiated for that data set.
(DSDSDWV)
VIRTUAL STORAGE Total of buffer pool expansion
EXPANSION FAILURES failures caused by virtual
storage shortage, summarized
across all active DB2 buffer
pools. (DSDSSSEF)
WORKFILES FOR MERGE Total number of work files that
PASSES have been requested for all of
the merge passes, summarized
across all active DB2 buffer
pools. (DSDSWFT)
WORKFILES REJECTED DUE Total number of work files
TO NO BUFFERS (runs), summarized for all active
DB2 buffer pools that were
rejected during all merge passes
due to insufficient buffer
resources. (DSDSWFD)
WRITE ENGINE Number of times a write engine
UNAVAILABLE was unavailable for I/O in the
DB2 system during an interval,
summarized across all active DB2
buffer pools. (DSDSWREE)
WRITE I/OS BY MEDIA Total number of write I/Os
MANAGER performed by the DB2 Media
Manager, summarized across all
active DB2 buffer pools.
(DSDSWRMM)
WRITE PAGEFIXED Number of times the active DB2
WITHOUT REAL buffer pools have been page-fixed
STORAGE to perform write I/O without real
storage frame backing. The
page-fix process must perform an
I/O operation to retrieve the
page's content from DASD storage
to a new real page frame.
(DSDSWFXS)
Environmental Descriptor Management Pool Section
NUMBER OF FAILURES DUE Number of DB2 transaction
TO FULL EDM POOL failures due to insufficient
virtual storage for EDM pool.
NUMBER OF PAGES IN EDM Number of pages in the EDM pool
POOL used to contain the skeleton
cursor tables and database
descriptors for a DB2 system.
NUMBER OF REQUESTS FOR Number of requests for cursor
CURSOR TABLE SECTIONS table sections or number of times
DB2 application programs
requested execution time.
NUMBER OF LOADED CURSOR Number of DB2 application
TABLE SECTIONS programs that were loaded for
execution.
NUMBER OF PAGES USED Number of EDM pool pages used to
FOR CURSOR TABLE load DB2 application programs.
NUMBER OF REQUESTS FOR Number of requests for package
PACKAGE TABLE SECTIONS table sections.
NUMBER OF LOADED Number of DB2 packages that were
PACKAGE TABLE SECTIONS loaded into the EDM pool.
NUMBER OF PAGES USED Number of EDM pool pages used to
FOR PACKAGE TABLE load packages.
NUMBER OF PAGES IN Number of unused pages in the EDM
FREE CHAIN pool.
NUMBER OF PAGES USED Number of EDM pool pages used for
FOR DATABASE database descriptors (DBDs) in
DESCRIPTORS the DB2 system.
NUMBER OF PAGES USED Number of pages used for skeleton
SKELETON CURSOR TABLE cursor tables.
NUMBER OF PAGES USED Number of pages used for skeleton
SKELETON PACKAGE TABLE package tables.
NUMBER OF REQUESTS FOR Number of active DBDs in the DB2
DATABASE DESCRIPTORS system.
NUMBER OF LOADS OF Number of DBDs the EDM pool was
DATABASE DESCRIPTORS able to actually process.
Data Set Activity Section
NUMBER OF DATASETS OPEN Number of data sets currently
open when SMF type 100 record is
cut.
NUMBER OF DATASETS Number of data sets not currently
AVAILABLE TO DRAIN in use that were specified with
CLOSE(YES), but have not been
physically closed.
NUMBER OF DATASETS Number of data sets closed via
CLOSED VIA DRAIN the drain process.
NUMBER OF SUCCESSFUL Frequency that data sets that had
REOPENS not previously been used became
used. The data set remained open
even though no process was using
it. This counter indicates that
the data set was eventually used
again. The larger the number in
this field, the better your
system is tuned for deferred
close.
