IFCID 002 - Group Buffer Pools Activity Data

This topic shows detailed information about "Record Trace - IFCID 002 - Group Buffer Pools Activity Data".

                                            GROUP BUFFER POOLS ACTIVITY DATA                                         
GROUP BUFFER POOL ID ..................:          0         FLAGS .......................:                 X'80'     
SYN.READS(XI)-DATA RETURNED .:                16941         SYN.READS(NF)-DATA RETURNED .:                   83      
SYN.READS(XI)-NO DATA RET ...:                25589         SYN.READS(NF)-NO DATA RET ...:                  111      
READ FOR CASTOUT MULT .......:                 1242         WRITE AND REGISTER MULT .....:                 1179      
READ FOR CASTOUT ............:                 6688         WRITE AND REGISTER ..........:                10548      
CLEAN PAGES WRITTEN .........:                    0         PAGES WRITE & REG MULT ......:                 7409      
CHANGED PAGES SYNC.WRITTEN ..:                17880         PAGES CASTOUT ...............:                11386      
CHANGED PAGES ASYNC.WRITTEN .:                   77         CASTOUT CLASS THRESHOLD .....:                   65      
WRITE FAILED-NO STORAGE .....:                    0         GROUP BP CASTOUT THRESHOLD ..:                    0      
REG.PAGE LIST (RPL) REQ .....:                  607         DELETE NAME LIST SEC-GBP ....:                    0      
GBP CHECKPOINTS TRIGGERED ...:                    0         DELETE PAGE FROM SEC-GBP ....:                    0      
PAGES RETRIEVED FROM GBP ....:                   78         READ CASTOUT STATS SEC-GBP ..:                    0      
READ STORAGE STATS ..........:                66803         UNLOCK CASTOUT ..............:                 7804      
DELETE NAME .................:                  333         READ CASTOUT CLASS ..........:                 6150      
UNREGISTER PAGE .............:                    0         NR.OF READ FOR CASTOUT REQ ..:                 7343      
REGISTER PAGE ...............:                   47         READ DIRECTORY INFO .........:                    0      
EXPLICIT X-INVALID ..........:                    0         GBP-DEPENDENT GETPAGES ......:              2730629      
PG P-LOCK UNLOCK REQ ........:              2095465         ASYNCH GBP REQUESTS .........:                63434      
PG P-LOCK LOCK REQ SP MAP PG :              2078633         ASYNCH SEC-GBP REQUESTS .....:                    0      
PG P-LOCK LOCK SUSP SP MAP PG:                39439         WRITE SEC-GBP FAILED ........:                    0
PG P-LOCK LOCK NEG SP MAP PG :                    0         PG P-LOCK LOCK REQ DATA PG ..:                10866      
PG P-LOCK LOCK REQ IX LEAF PG:                 3718         PG P-LOCK LOCK SUSP DATA PG  :                   67      
PG P-LOCK LOCK SUSP IX LEAF PG                   15         PG P-LOCK LOCK NEG DATA PG ..:                    4      
PG P-LOCK LOCK NEG IX LEAF PG:                    0         PAGES IN WRITE AROUND .......:                   16      

GROUP BUFFER POOL ID

The group buffer pool identifier.

Field Name: QBGLGN

FLAGS

The flag byte shows if more QBGL data is following or if this is the last of the QBGL repeating groups.

Field Name: QBGLFLG

SYN.READS(XI)-DATA RETURNED

The number of requests made to read a page from the group buffer pool because the page was invalidated in the member's buffer pool. The member found the required page in the group buffer pool.

Background and Tuning Information

When you increase the size of the group buffer pool (GBP), the number of pages returned from the GBP can increase. Conversely, decreasing the size of the GBP can cause DB2 to return fewer pages because the GBP cannot hold pages long enough to allow them to be retrieved again.

Field Name: QBGLXD

This is an exception field.

SYN.READS(NF)-DATA RETURNED

The number of requests made to read a page from the group buffer pool because the page was not in the buffer pool of the member. The member found the page in the group buffer pool.

Background and Tuning Information

The requesting member needs a page from a table space or index that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on DASD.

If the group buffer pool is used to cache both clean and changed pages (GBPCACHE ALL is used for all data), you can try to get more pages returned from the group buffer pool by increasing the size of the group buffer pool. Do not tune the GBP based on this counter if it is used for caching changed pages only (GBPCACHE CHANGED).

Field Name: QBGLMD

This is an exception field.

SYN.READS(XI)-NO DATA RET.

The number of requests to read a page from the group buffer pool made because the page was invalidated in the member's buffer pool. The page was not found in the GBP and the page was recovered from DASD.

Field Name: QBGLGXR

SYN.READS(NF)-NO DATA RET.

The number of requests made to read a page from the group buffer pool because the page was not in the member's buffer pool. The member did not find the required data in the group buffer pool and had to retrieve the page from DASD.

Background and Tuning Information

The requesting member needs a page from a table space or index that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on DASD.

You can compare the value in this counter with the number of pages that were returned from the group buffer pool, see Sync.Read (Not Found) - Data Returned. If the group buffer pool is used to cache both clean and changed pages (GBPCACHE ALL is used for all data), you can try to get more pages returned from the group buffer pool by increasing the size of the group buffer pool. Do not tune the GBP based on this counter if it is used for caching changed pages only (GBPCACHE CHANGED).

Field Name: QBGLMR

READ FOR CASTOUT MULT

The number of Read For Castout Multiple requests.

Field Name: QBGLCM

WRITE AND REGISTER MULT

The number of Write and Register Multiple requests.

Field Name: QBGLWM

READ FOR CASTOUT

The number of Read For Castout requests. One page read per request.

Field Name: QBGLCR

WRITE AND REGISTER

The number of Write and Register requests.

Field Name: QBGLWS

CLEAN PAGES WRITTEN

The number of clean pages that were synchronously written to the group buffer pool from the virtual pool.

Background and Tuning Information

Only GBPCACHE ALL causes clean (unchanged) pages to be written to the coupling facility. The pages are written to the coupling facility even if the page set is not GBP-dependent. If group buffer pool caching works effectively for prefetch, the value in this field should be much smaller than the value in Synchronous Read (Not Found) - Data Returned.

Field Name: QBGLWC

This is an exception field.

PAGES WRITE & REG MULT

The number of pages written using Write and Register Multiple (WARM) requests.

Field Name: QBGLWP

CHANGED PAGES SYNC.WRITTEN

The number of changed pages written synchronously to the group buffer pool.

Pages are written with Write and Register (WAR) requests or Write and Register Multiple (WARM) requests.

At commit time changed pages are forced from the virtual buffer pool of the member to the coupling facility.

Background and Tuning Information

In data sharing, changed pages must have been written to the group buffer pool by the time a transaction commits. The pages are written either synchronously (force at commit) or asynchronously, for example, when a local buffer pool threshold is reached or at a member's checkpoint. The number of pages that have to be forced out synchronously (in "burst mode") at commit time can be reduced if asynchronous writes are triggered more frequently.

You can use the vertical deferred write threshold (VDWQT) to reduce the number of pages that have to be forced out synchronously and to increase the number of pages that are asynchronously written before the transaction commits. For GBP-dependent page sets, writes triggered by the vertical deferred write threshold go to the coupling facility. You can cause changed pages to be written out quicker and in smaller increments, by reducing the vertical deferred write threshold (VDWQT).

Field Name: QBGLSW

This is an exception field.

PAGES CASTOUT

The number of data pages that were cast out of the group buffer pool of the member.

Castout to a page set or partition is done by the castout owner of the page set or partition. This is normally the DB2 subsystem that had the first update intent on the page set or partition.

Background and Tuning Information

The number of pages written per I/O is normally close to the value of this field divided by the value in Unlock Castout.

For example, if an average of four pages is written per castout write I/O, the number of pages cast out should be four times the number in this field.

Because DB2 usually includes more than one page in the request to write pages to DASD, the number in this field should always be significantly more than Unlock Castout. If it is not (for example, when "unlock castout" is more than half of "pages castout"), the castout write I/O is inefficient; probably because you have random update patterns on the DB2 data or a low castout threshold.

Field Name: QBGLRC

This is an exception field.

CHANGED PAGES ASYNC.WRITTEN

The number of changed pages written asynchronously to the group buffer pool.

Pages are written in response to Write and Register (WAR) and Write and Register Multiple (WARM) requests.

Changed pages can be written from the member's virtual buffer pool to the group coupling facility before the application commits. This happens when, for example, a local buffer pool threshold is reached, or when P-lock negotiation forces the pages on the vertical deferred write queue to be written to the group buffer pool.

Background and Tuning Information

In data sharing, changed pages must have been written to the group buffer pool before a transaction commits. The pages are written either synchronously during commit processing or asynchronously before the transaction commits when, for example, a local buffer pool threshold is reached or at a member's checkpoint. See Changed Pages - Written Synchronously for the number of changed pages synchronously written to the group buffer pool.

The vertical deferred write threshold (VDWQT) can be used to reduce the number of pages that have to be forced out synchronously and to increase the number of pages that are asynchronously written before the transaction commits. For GBP-dependent page sets, writes triggered by the vertical deferred write threshold go to the coupling facility. If you want changed pages to be written out quicker and in smaller increments, you can lower the vertical deferred write threshold (VDWQT).

Field Name: QBGLAW

This is an exception field.

CASTOUT CLASS THRESHOLD

The number of times group buffer pool castout was initiated because the group buffer pool class castout threshold was detected.

Background and Tuning Information

The class castout threshold is one of two group buffer pool thresholds. In most cases the default value for the class threshold (5 percent) is a good choice. Depending on your workload, altering this value can reduce DASD contention during castout.

Field Name: QBGLCT

This is an exception field.

WRITE FAILED-NO STORAGE

The number of coupling facility write requests that could not complete due to a lack of coupling facility storage resources.

Background and Tuning Information

A value greater than zero indicates that the data page resources of the coupling facility are being consumed faster than the DB2 castout processes can free them.

On write failure, the affected DB2 member initiates castout and retries several times, and finally, if it is a changed page, it will be added to the logical page list (LPL) requiring recovery.

If the problem is not simply due to a momentary surge in activity, you need either to decrease the group buffer pool castout thresholds, or to increase the number of data entries in the group buffer pool. To increase the number of data entries, you can do one of the following:

• Increase the total size of the group buffer pool.
• Adjust the ratio of directory entries to data entries in favor of data entries.

Field Name: QBGLWF

This is an exception field.

GROUP BP CASTOUT THRESHOLD

The number of times a group buffer pool castout was initiated because the group buffer pool castout threshold was detected.

Background and Tuning Information

The GBP castout threshold, GBP class castout threshold, and the length of the GBP checkpoint interval determine the castout characteristics of the group buffer pool.

You can consider this threshold a safety margin to protect the group buffer pool from being accidentally flooded by overactive applications.

In most situations, the default value for the group buffer pool castout threshold of 30 percent is a good choice. Use the ALTER GROUPBUFFERPOOL command to tune the group buffer pool thresholds.

Field Name: QBGLGT

This is an exception field.

REG.PAGE LIST (RPL) REQ.

The number of register page list (RPL) requests made by prefetch. The group buffer pool must be allocated in a group coupling facility with CFLEVEL=2 or higher.

Background and Tuning Information

Performance might be improved by enabling RPL.

Field Name: QBGLAX

This is an exception field.

DELETE NAME LIST SEC-GBP

The number of DELETE NAME LIST requests to delete pages from the secondary group buffer pool that have just been cast out from the primary.

Field Name: QBGL2D

This is an exception field.

GBP CHECKPOINTS TRIGGERED

The number of group buffer pool checkpoints triggered by this member.

Background and Tuning Information

The value of this counter depends on the length of the group buffer pool checkpoint interval.

Field Name: QBGLCK

DELETE PAGE FROM SEC-GBP

The number of group buffer pool requests to delete a page from the secondary group buffer pool. These requests are issued by the group buffer pool structure owner to delete orphaned data entries in the secondary GBP as part of the garbage collection logic.

Field Name: QBGL2N

PAGES RETRIEVED FROM GBP

The number of coupling facility reads performed by prefetch to retrieve a changed page from the group buffer pool.

Field Name: QBGLAY

This is an exception field.

READ CASTOUT STATS SEC-GBP

The number of coupling facility requests to read the castout statistics for the secondary group buffer pool. These requests are issued by the group buffer pool structure owner to check for orphaned data entries in the secondary group buffer pool.

Field Name: QBGL2R

This is an exception field.

READ STORAGE STATS

The number of times DB2 requested statistics information from the group buffer pool. It is issued by the group buffer pool structure owner at timed intervals to determine whether the group buffer pool castout threshold (GBPOOLT) has been reached.

Field Name: QBGLOS

UNLOCK CASTOUT

The number of times DB2 issued an unlock request to the coupling facility for completed castout I/Os.

When pages are cast out to DASD, they are locked for castout in the coupling facility. This castout lock is not an IRLM lock; it is to ensure that only one system can cast out a given page at a time.

Background and Tuning Information

The number of pages written per I/O is normally close to the value of pages castout divided by the value of this field.

For example, if an average of four pages is written per castout write I/O, the number of pages cast out should be four times the value in this field.

Because DB2 usually includes more than one page in a write request, the number in this field should always be significantly less than pages castout. If it is not (for example, when "unlock castout" is more than half of "pages castout"), the castout write I/O is inefficient; possibly because you have random update patterns on the DB2 data or a low castout threshold.

Field Name: QBGLUN

DELETE NAME

The number of requests made by DB2 to delete directory and data entries associated with a particular page set or partition from the group buffer pool.

DB2 issues this request when it changes a page set or partition from GBP-dependent to non GBP-dependent. DB2 also issues this request for objects that are defined with GBPCACHE ALL when those objects are first opened.

Background and Tuning Information

This counter is a measure of how often page sets or partitions change between being and not being dependent on the group buffer pool.

You can prevent DB2 going in and out of GBP dependency too often by tuning the following subsystem parameters that affect data sets when they are switched to a different state:

PCLOSEN

Pseudoclose frequency. The number of checkpoints required before a data set that was not updated can be a pseudoclose candidate.

If the PCLOSEN condition is met, the page set or partition is converted from read-write to read-only state. Depending on other concurrent users, this could raise the chance for the page set or partition to go out of GBP dependency.

PCLOSET

Pseudoclose time. The amount of time (in minutes) that must elapse before a data set can be a pseudoclose candidate.

If the PCLOSEN or PCLOSET condition is met, the page set or partition is converted from read-write to read-only state. Depending on other concurrent users, this could raise the chance for the page set or partition to go out of GBP dependency.

LOGLOAD

The number of log records that DB2 writes between successive checkpoints.

These parameters are specified in the CHECKPOINT FREQ field in panel DSNTIPN.

Field Name: QBGLDN

READ CASTOUT CLASS

The number of requests made to the group buffer pool to determine which pages, from a particular page set or partition, must be cast out because they are cached as changed pages.

This request is issued either by the page set or partition castout owner, or, when the group buffer pool castout threshold is reached, by the group buffer pool structure owner.

Field Name: QBGLCC

UNREGISTER PAGE

The number of times DB2 unregistered interest for a single page. This happens when DB2 steals pages from the member's buffer pool that belong to GBP-dependent page sets or partitions.

Background and Tuning Information

A large value here indicates that the local buffer pool contains a mixture of GBP-dependent data and non-GBP-dependent data.

The page stolen from the local buffer pool is replaced by a new one. This counter makes a distinction on whether the new page depends on the group buffer pool or not.

Usually a page of a GBP-dependent page set or partition is replaced by a page that is also GBP-dependent. In this instance, the unregister request for the page being stolen is combined with the read and register request for the new page. These combined requests do not contribute to this counter.

If, however, a page of a GBP-dependent page set or partition is replaced by a page that is not GBP-dependent, then only an unregister request is sent to the coupling facility. These separate requests are counted here.

Field Name: QBGLDG

NR.OF READ FOR CASTOUT REQ.

The number of requests issued by the group buffer pool structure owner to determine which castout classes have changed pages.

This request is made by the group buffer pool structure owner when the group buffer pool threshold is reached. Normally, you would expect only one or two requests each time the group buffer pool threshold is reached.

Field Name: QBGLCS

REGISTER PAGE

The number of times DB2 registered interest in a single page.

These are "register-only" requests, which means that DB2 is not requesting any data back from the request.

This request is made only to create a directory entry for the page to be used for cross-invalidation when the page set or partition P-lock is downgraded from S to IS mode, or from SIX to IX mode.

Field Name: QBGLRG

READ DIRECTORY INFO

The number of requests issued by the group buffer pool structure owner to read the directory entries of all changed pages in the group buffer pool.

This request is issued at group buffer pool checkpoints to record the oldest recovery log record sequence number (LRSN). It is used as a basis for recovery if the group buffer pool fails.

Such requests might have to be issued several times for each group buffer pool checkpoint to read the directory entries for all changed pages.

Background and Tuning Information

If the value of this counter appears to be abnormally high, consider upgrading the coupling facility to CFLEVEL=2 or higher to raise the number of directory entries that can be read with one request. You can also increase the group buffer pool checkpoint interval, but this can lengthen the recovery for the group buffer pool.

Field Name: QBGLRD

EXPLICIT X-INVALID

The number of times an explicit coupling facility cross-invalidation request was issued.

Field Name: QBGLEX

GBP-DEPENDENT GETPAGES

The number of Getpages made for GBP-dependent objects.

Field Name: QBGLGG

PG P-LOCK UNLOCK REQ

The number of page P-lock unlock requests.

Field Name: QBGLU1

ASYNCH GBP REQUESTS

The number of asynchronous IXLCACHE invocations for the primary group buffer pool.

Field Name: QBGAHS

PG P-LOCK LOCK REQ SP MAP PG

The number of page P-lock lock requests for space map pages.

Field Name: QBGLP1

ASYNCH SEC-GBP REQUESTS

The number of IXLCACHE invocations for the secondary group buffer pool.

Field Name: QBGA2H

PG P-LOCK LOCK SUSP SP MAP PG

The number of page P-lock suspensions for space map pages.

Field Name: QBGLS1

WRITE SEC-GBP FAILED

The number of coupling facility requests to write changed pages to the secondary group buffer pool for duplexing that failed because of a lack of storage in the coupling facility.

Field Name: QBGL2F

This is an exception field.

PG P-LOCK LOCK NEG SP MAP PG

The number of page P-lock negotiations for space map pages.

Field Name: QBGLN1

PG P-LOCK LOCK REQ DATA PG

The number of page P-lock requests for data pages.

Field Name: QBGLP2

PG P-LOCK LOCK REQ IX LEAF PG

The number of page P-lock requests for index leaf pages.

Field Name: QBGLP3

PG P-LOCK LOCK SUSP DATA PG

The number of page P-lock suspensions for data pages.

Field Name: QBGLS2

PG P-LOCK LOCK SUSP IX LEAF PG

The number of page P-lock suspensions for index leaf pages.

Field Name: QBGLS3

PG P-LOCK LOCK NEG DATA PG

The number of page P-lock negotiations for data pages.

Field Name: QBGLN2

PG P-LOCK LOCK NEG IX LEAF PG

The number of page P-lock negotiations for index leaf pages.

Field Name: QBGLN3

PAGES IN WRITE AROUND

The number of changed pages that were written to disk through group buffer pool write-around due to condition write failures to the group buffer pool.

Field Name: QBGLWA