Db2 Thread Detail Buffer Pool Attributes

Use these attributes to view Db2 Thread Detail Buffer Pool information.

ACE Address The ACE (see QWHSACE) of the thread being reported on. The value in QWHSACE is the ACE of the IFI application that requested this trace record. (Field name: QW0148AC).

Authorization ID The primary authorization ID from connection or signon. (Field name: QWHCAID). For z/OS operator commands and Db2 system internal agents, the value is SYSOPR. Secondary authorization IDs might be the RACF groups associated with this primary authorization ID. The SQL ID is initially set to this primary authorization ID. The connection authorization exit and the signon authorization exit can change the primary authorization ID so that it is not the same as the original primary authorization ID (QWHCOPID). Distributed authorization ID translation can also change the primary authorization ID. If QWHCAID_OFF is not 0, this value is truncated.

Buffer Pool ID Identifies which buffer pool the information in this section refers to. (Field name: QBACPID).

0 through 49 are identifiers for 4KB buffer pools
100 through 109 are identifiers for 8KB buffer pools
120 through 129 are identifiers for 16KB buffer pools
80 through 89 are identifiers for 32KB buffer pools

Collection Name The collection name. If QW0148CI_OFF is not 0, this value is truncated. (Field name: QW0148CI).

Connection ID The connection name. Not valid on end of memory and reflects the z/OS home ASID connection name. (Field name: QWHCCN).

For batch: BATCH
For TSO: TSO
For QMF: DB2CALL
For UTILITY: UTILITY
For DB2 INTERNAL: DB2 SUBSYSTEM ID
For IMS: IMS-ID
For CICS: CICS-ID
For RRSAF: RRSAF

For distributed database access threads:

For THREADS from a Db2 requester, this field contains the connection name of the thread at the requesting location.
For THREADS using the DRDA protocol from a non-Db2 requester, this field contains the constant 'SERVER'.

Correlation ID The correlation ID value. (Field name: QWHCCV).

For batch: JOBNAME
For TSO: LOGON-ID
For IMS/VS: PST#.PSBNAME
For CICS: CONNECTION_TYPE.THREAD_TYPE.THREAD_#.TRAN-ID
For RRSAF: CORRELATION-ID value from signon function
For threads from a Db2 requester, this field contains the CORRELATION-ID name of the thread at the requesting location.
For threads using the DRDA protocol from a non-Db2 requester, this field contains the first 12 characters in the DDM external name (EXTNAM) parameter of the DDM EXCSAT command received as part of the SQL CONNECT.

Data Sharing Group The Data Sharing Group name. (Field name: GROUP).

Db2 Subsystem The Db2 subsystem ID. (Field name: DB2ID).

Db2 Version The DB2 Version. (Field name: DB2VER).

DSG Member Name The Data Sharing Group member name. (Field name: MEMBER).

Dynamic Prefetch Request The number of (dynamic) prefetch requests. (Field name: QBACDPF). This is the process that is triggered because of sequential detection. This field also counts prefetches for segmented table spaces.

Elapsed_Time\read_zHL Amount of elapsed time, in microseconds, that was used for successful read operations that used zHyperlink processing. Because processing is not suspended while a zHyperlink I/O operation is performed, the elapsed time is not reported under class 3 database I/O suspension time. If processing is interrupted for some reason, such as low dispatch priority, the reported elapsed time for zHyperlink I/O might be greater. (Field name: QBACSYIT).

End User ID The end user's work station user ID. (Field name: QWHCEUID). This can be different from the authorization ID used to connect to Db2. This field contains blanks of the client did not supply this information.

Get Page The number of GETPAGE requests. (Field name: QBACGET). This field counts successful requests for pages for queries that are processed in parallel and both successful and unsuccessful requests for pages that are not processed in parallel.

Getpages Per Read I/O The getpage per read I/O. (Field name: T8ACGRIO). The ratio of getpage requests to read I/Os for the database. The ratio is calculated by dividing the number of getpage requests by the sum of the following values:

Number of synchronous reads
Number of prefetch read I/Os

Hit Ratio The buffer pool hit ratio (%). (Field name: T8ACHIT). The percentage of Getpage operations that were satisfied by a page already in the buffer pool. The value is calculated as the ratio of number of successful Getpage operations minus the number of pages read from DASD (both synchronously and using prefetch), to the number of successful Getpage operations, expressed as a percentage. Background and Tuning Information: The highest possible hit ratio is 100%, that is, when every page requested is always in the buffer pool. If the requested page is not in the buffer pool, the hit ratio is 0% or less. If the hit ratio is negative, this means that prefetch brought pages into the buffer pool that are not subsequently referenced, either because the query stops before it reaches the end of the table space, or because the prefetched pages are stolen by DB2 for reuse before the query can access them. A low buffer pool hit ratio is not necessarily bad. The hit ratio is a relative value, based on the type of application. For example, an application that browses large data might have a buffer pool hit ratio of 0. Watch for those cases where the hit ratio drops significantly for the same application. Here are some suggestions to increase the buffer hit ratio:

Run the REORG utility for indexes or table spaces associated with the virtual buffer pool.
Reserve more pages for random I/O by setting the SEQUENTIAL STEAL THRESHOLD (VPSEQT) to a lower value.
Increase the buffer pool as long as the cost of paging do not outweigh the benefit of I/O avoidance.
Establish more separate buffer pools, perhaps to isolate different applications.
Place the objects that are only accessed sequentially in a separate buffer pool. The hit ratio measurement becomes less meaningful if the buffer pool is used by additional processes, such as utilities or work files.

Immediate Writes The number of immediate (synchronous) write I/Os. (Field name: QBACIMW). See field QBSTIMW for a description of the reasons for an immediate write.

Interval Start The start time of this interval. (Field name: TIMESTAMP).

List Prefetches The number of list prefetch requests. (Field name: QBACLPF). List prefetch allows Db2 to access data pages efficiently even when the needed data pages are not contiguous. List prefetch can be used with single index access an is always used with multiple index access. It is always used to access data from the inner table during a hybrid join. Data pages are read in quantities equal to the sequential prefetch quantity (see QBACSEQ) which depends on buffer pool size and is usually 32 pages. During bind time Db2 does not use list prefetch if the estimated number of RIDs to be processed would use more than 50% of the RID pool. During execution time, list prefetch processing terminates if Db2 detects that more than 25% of the rows in the table need to be accessed. If list prefetch is terminated, IFCID 0125 indicates this.

Location Name The location name. (Field name: QW0148LN). The location name contains the name of the local location or remote location, depending on where the thread executes a package. The following statements execute locally, regardless of the current package name:

COMMIT
ROLLBACK
SET <HOST VARIABLE> = CURRENT PACKAGE SET
SET <HOST VARIABLE> = CURRENT SERVER

For these statements, this field contains the location where the package executes, not the location where the statement executes. If QW0148LN_OFF is not 0, this value is truncated.

Logical Unit of work ID The logical unit of work ID (LUWID) as defined for the LU 6.2 Interface. (Field name: QW0148LU). The LUWID uniquely identifies the thread within the network and consists of the following: a fully qualified network name (QW0148LM), an LUW instance number (QW0148UV) and an LUW sequence number. In length, consisting of two 8-character fields, QW0148NI and QW0148LM, that together uniquely identify a client system.

MVS System ID The MVS identifier. (Field name: MVSID).

Originating System The managed system name of the agent. Valid format is alphanumeric, with a maximum of 32 characters. For example, DB91:SYS1:DB2. (Field name: ORIGINNODE).

Package ID The package ID. If QW0148PN_OFF is not 0, this value is truncated. (Field name: QW0148PN).

Parallel Getpage Unavail The number of times a page requested for a query processed in parallel was unavailable because an I/O was in progress or the page was not found in the buffer pool. (Field name: QBACNGT). The agent does not wait. Instead, control returns to the agent. If the value is close to zero, most pages are already prefetched into the buffer pool and wait time for synchronous I/O is small. This counter can be high if for example there is a cluster index scan and the data is not truly clustered by the index key, so the data pages are not accessed in their true order. Hence, the cluster ratio is not valid. Use the RUNSTATS utility to update it. This number is also used to determine how many sequential prefetches of one page were scheduled.

Plan Name The plan name. (Field name: QWHCPLAN). This field is blank for Db2 Command thread. Otherwise:

For SPUFI with cursor stability: DSNESPCS
For SPUFI with repeatable read: DSNESPRR
For TSO: APPLICATION PLAN NAME
For IMS: APPLICATION PLAN NAME
For CICS: APPLICATION PLAN NAME
IMS and CICS commands have a blank plan name.
For RRSAF CREATE THREAD with the COLLECTION parameter: ?RRSAF
For QMF: DSQPLAN

For distributed database access threads:

For threads using the DRDA protocol from a requester, this field contains the plan name being executed at the requested location.
For threads using the DDRA protocol from a non-Db2 requester or from a Db2 2.3 requester, this field contains DISTSERV.
For BINDING: DSNBIND (SYSTEM PLAN)
For UTILITY: DSNUTIL (SYSTEM PLAN)
For AUTHORIZATION: ACT + X'0000000000' (SYSTEM PLAN)
For UNALLOCATED THREADS AND MISCELLANEOUS DB2 SYSTEM SERVICE TASKS: BCT + X'0000000000' (SYSTEM PLAN)
For STARTUP: STARTCT + X'00' (SYSTEM PLAN)

Read I/O Sequential The number of asynchronous pages read by prefetch that the agent triggered. (Field name: QBACSIO).

Reads w cache hit The number of read I/O operations that resulted in disk cache hits. (Field name: QBACIOC).

Sequential Prefetch The number of sequential prefetch requests. (Field name: QBACSEQ). This counter is incremented for each prefetch request. Each request might result in an I/O read. If it results in an I/O read, up to 32 pages can be read for SQL and up to 64 pages for utilities. A request does not result in an I/O if all pages to be prefetched are already in the buffer pool. Sequential detection is not included in QBACSEQ and is separately recorded in QBACDPF.

Sync Read I/O The number of synchronous read I/Os. (Field name: QBACRIO). This counter is incremented for each media manager synchronous physical read request. Asynchronous I/O requests are not counted.

SyncIO read zHL The number of synchronous read I/O operations that used zHyperlink processing. (Field name: QBACSYI).

Thread Type The connecting system type code (in hexadecimal). This field can have a null value. (Field name: QWHCATYP). For example, this field contains a null value for some utilities:

1 = TSO FOREGROUND AND BACKGROUND
2 = DB2 CALL ATTACH
3 = DL/I BATCH
4 = CICS ATTACH
5 = IMS ATTACH BMP
6 = IMS ATTACH MPP
8 = DRDA PROTOCOL
9 = IMS CONTROL REGION
A = IMS TRANSACTION BMP
B = DB2 UTILITIES
C = RRSAF

Total Prfetch The number of sequential, dynamic, and list prefetch requests. (Field name: ABCLSPR).

Transaction name The transaction or application name that the end user is running. (Field name: QWHCEUTX). This identifies the application that is currently running, not the product that is used to run the application. This field contains blanks if the client did not supply this information.

Update Intents The number of times a buffer update occurs for the agent. (Field name: QBACSWS). This counter is incremented every time a page is updated and is ready to be written to DASD. If the same page is updated twice, for example, the count is incremented by 2. This count is kept for all types of pages including data pages, work file pages, and so on.

Workstation name The end user's workstation name. This field contains blanks if the client did not supply this information. (Field name: QWHCEUWN).