Thread Buffer Pool Activity
This panel provides detailed information about buffer manager activity at the buffer pool level for an individual thread.
________________ ZBUF VTM O2 V540.#P DA41 11/04/13 17:07:41 2
> Help PF1 Back PF3 Up PF7 Down PF8
> THREAD INFORMATION: Enter a selection letter on the top line.
> A-THREAD DETAIL B-LOCK COUNTS C-LOCK WAITS D-LOCKS OWNED E-GLOBAL LOCKS
> F-CURRENT SQL G-SQL COUNTS H-DISTRIBUTED *-BUFFER POOL J-GROUP BP
> K-PACKAGES L-RES LIMIT M-PARALLEL TASKS N-UTILITY O-OBJECTS
> P-CANCEL THREAD Q-DB2 CONSOLE R-DSN ACTIVITY S-APPL TRACE T-ENCLAVE
> U-LONG NAMES W-ACCEL ACTIVITY
===============================================================================
> THREAD BUFFER POOL ACTIVITY
PLAN
+ Thread: Plan=DISTSERV Connid=SERVER Corrid=javaw Authid=MIS
+ Dist : Type=DATABASE ACCESS, Luwid=G9987A90.PDBB.CC36C64A80B3=1503
+ Location : ::FFFF:9.152.122.144
+ Host Name:polk.boeblingen.de.ibm.com
buf
+ Buffer Pool: ALL
+
+ Getpage Requests = 69320 Failed Getpage Requests = 0
+ Synchronous Read I/O = 5 Getpage/Read I/O = 13864.00
+ Page Updates = 21853 Seq Prefetch Requests = 1938
+ List Prefetch Requests = 1 Dynamic Prefetch Requests = 0
+ Prefetch Pages Read = 62014
+ Immediate Writes = 0 BP Hit Percentage = 97.1%
+
+ Buffer Pool: BP0
+
+ Getpage Requests = 142 Failed Getpage Requests = 0
+ Synchronous Read I/O = 2 Getpage/Read I/O = 71.00
+ Page Updates = 0 Seq Prefetch Requests = 0
+ List Prefetch Requests = 1 Dynamic Prefetch Requests = 0
+ Prefetch Pages Read = 1
+ Immediate Writes = 0 BP Hit Percentage = 97.8%
+
+ Buffer Pool: BP2
+
+ Getpage Requests = 61974 Failed Getpage Requests = 0
+ Synchronous Read I/O = 3 Getpage/Read I/O = 20658.00
+ Page Updates = 0 Seq Prefetch Requests = 1938
+ List Prefetch Requests = 0 Dynamic Prefetch Requests = 0
+ Prefetch Pages Read = 62013
+ Immediate Writes = 0 BP Hit Percentage = 96.8%
+
+ Buffer Pool: BP32K
+
+ Getpage Requests = 7202 Failed Getpage Requests = 0
+ Synchronous Read I/O = 0 Getpage/Read I/O = .00
+ Page Updates = 21853 Seq Prefetch Requests = 0
+ List Prefetch Requests = 0 Dynamic Prefetch Requests = 0
+ Prefetch Pages Read = 0
+ Immediate Writes = 0 BP Hit Percentage = 100.0%
+
+ Buffer Pool: BP8K0
+
+ Getpage Requests = 2 Failed Getpage Requests = 0
+ Synchronous Read I/O = 0 Getpage/Read I/O = .00
+ Page Updates = 0 Seq Prefetch Requests = 0
+ List Prefetch Requests = 0 Dynamic Prefetch Requests = 0
+ Prefetch Pages Read = 0
+ Immediate Writes = 0 BP Hit Percentage = 100.0%
===============================================================================Highlighting
The following table shows
the fields that might be highlighted in the panel above to indicate
that an exception that is related to this field exceeded its threshold
value:
| Field | Exception | Reason |
|---|---|---|
| Plan | Any | Same as other thread detail panels. |
| Getpage Requests | GETP | The Getpage to Read I/O ratio indicates poor read efficiency. |
| Getpage/Read I/O | GETP | The Getpage to Read I/O ratio indicates poor read efficiency. |
| Synchronous Reads | RIO | The synchronous Read I/O rate is high. |
| Page Updates | PGUP | The rate for system page updates is high. |
| Sequential Prefetch Reqs | PREF | The thread Sequential Prefetch rate is high. |
Fields
Thread identifier: This information identifies the thread to which
the information in this panel applies.
- Plan
- The Db2 plan name of the active thread.
- Connid
- The Db2 connection identifier of the active thread.
- Corrid
- The Db2 correlation identifier of the active thread. If the correlation is not set, N/A is displayed.
- Authid
- The Db2 authorization identifier of the active thread.
- Attach
- Depending on the type of connection, the appropriate information is displayed.
- Connection Type
-
- Batch
- The MVS jobname and ASID.Note: For threads from remote Db2, the MVS job name is N/A.
- CICS®
- The CICS jobname, task name, task number, terminal ID,
and thread type. The thread type is:
- Pool
- The thread in use is a pool thread.
- Entry
- The thread in use is a nonprotected entry thread.
- Prot
- The thread in use is a protected thread. Protected threads are defined in an RCT entry definition using the THRDS operand.
- IMS
- The IMS region number, transaction name, region name, and terminal ID (LTERM).
- RRSAF
- The MVS job name and ASID.Note: For threads from remote Db2, the MVS job name is N/A.
- System
- The originating Db2 job name and the resource manager that is the source of the thread. An additional line below the Attach line identifies the user thread, if any, being served by the system thread.
- TSO
- The TSO user ID and region ASID.
- Utility
- No additional information.
- Db2
- The Db2 subsystem identifier.
- MVS
- The MVS system identifier.
- ORIGAUTH
- The original (primary) Db2 authorization identifier of the thread. This field displays only when the original identifier is different from the Authid.
Package identifier: This information identifies the package to
which the information in this panel applies.
Distributed
thread identifier: The following fields are displayed when the
thread has a distributed relationship with a remote DB2 subsystem.
- Type
- The distributed thread type.
- Distributed Allied
- A requesting thread; one that has issued an SQL call to a remote DB2 location.
- Database Access
- A responding thread; one that is serving a remote DB2 location by responding to an SQL call.
- Luwid
- This value consists of two parts: the logical unit-of-work
ID (luw-id) and a token. The token can be used in place of the luw-id
in any Db2 command that accepts
luw-id as input. Format:
luw-id=tokenThe luw-id consists of the network name, the originating VTAM® LUNAME, and a unique identifier (separated by periods). Thus, the Luwid field displays data such as the following:
USCACO01.O2D22A.A1FE8E04B9D4=8 - System
- The originating Db2 job name and the resource manager that is the source of the thread.
An additional line below the attachment identifier displays the user thread, if any, that is served by the system thread.
Thread status: The totals for all of the buffer pools in use by the thread are displayed, followed by the values for each individual buffer pool in use.
- Getpage Requests
- The number of thread Getpage requests. The value includes conditional, unconditional, successful, and unsuccessful requests. This logical read request might not actually result in physical I/O of the page request currently in the buffer pool.
- Failed Getpage Requests
- The number of times a conditional Getpage request could not be satisfied. Conditional Getpage, used only with queries being processed in parallel, will not wait for a page that is not currently in the buffer pool.
- Synchronous Read I/O
- The number of synchronous Read I/O requests issued by the thread.
- Getpage/Read I/O
- The number of Getpage requests divided by the number of synchronous Read I/Os. This value does not include Prefetch requests, because each prefetch request can return a variable number of pages (from one to 16).
- Page Updates
- The number of DB2 page updates made by the thread since thread creation. This value is incremented each time a row in a page is updated, not just once for each page updated. DB2 might update pages when it creates intermediate result tables because of a qualified SELECT statement, even though SELECT statements are generally thought of as read only operations. Pages that have been updated are written asynchronously by DB2, according to DB2's internal Deferred Write algorithm, not immediately after update or commit. DB2 resets the page update count at Create Thread and Signon. If Signon is not driven, the page update count is cumulative.
- Seq Prefetch Requests
- The number of Prefetch requests issued by the thread.
Unlike normal Read I/O, Sequential Prefetch Read I/O is performed asynchronously with the user's request. It provides a read-ahead capability. A single sequential prefetch I/O results in multiple pages being read. Threads with excessive Sequential Prefetch rates can cause reduced overall Db2 performance.
- List Prefetch Requests
- The number of List Prefetch requests issued by the thread.
- Dynamic Prefetch Requests
- The number of Dynamic Prefetch requests made.
- Prefetch Pages Read
- The number of pages read asynchronously for prefetch.
- Immediate Writes
- The number of immediate (synchronous) writes to DASD.
- BP Hit Percentage
- The percentage of Getpage requests issued by the thread for which the data was already in the buffer pool.