The Group Buffer Pools Activity Data section
This report section shows group buffer pool activity data, which is retrieved from IFCID 2 (DB2® statistics — Group Buffer Pool Activity data).
1 OMEGAMON XE FOR DB2 PE (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-9
ORDER: BPID-QPAGESET
SORTBY: BPID,ASYNCPAGE TOP: 17 LEVEL: SUMMARY
GROUP: DSNJ LOCATION: PMODSNJ DB2 VERSION: V11
MEMBER: SGJ1 REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: SGJ1 INTERVAL FROM: 01/24/13 07:21:46 TO: 01/24/13 09:27:13
======= Group Buffer Pools Activity Data =======
Group Buffer Pool GBP0 GBP2 GBP8K0
----------------------------------- ---------- ---------- ----------
Group BP Hit Ratio (%) 28.57 0.00 0.00
GBP-Dependent Getpages 8 2 0
Syn.Read(XI)-Data returned 2 0 0
Syn.Read(XI)-No data return 5 1 0
Syn.Read(NF)-Data returned 0 0 0
Syn.Read(NF)-No data return 0 0 0
Pages written using write-around 0 0 0
Clean pages sync. written 0 0 0
Changes pages sync. written 10 3 0
Clean pages async. written 0 0 0
Changes pages async. written 0 0 0
Reg.Page List(RPL) request 0 0 0
Clean pages after RPL n/p n/p n/p
Pages retrieved from GBP after RPL 0 0 0
Async. read-No data return n/p n/p n/p
Async. read-Data returned n/p n/p n/p
Castout class threshold 0 0 0
Group BP castout threshold 0 0 0
Pages castout 7 1 0
Unlock castout 7 1 0
Read castout class 6 2 0
Read castout statistics 16 16 16
RFCOM requests 0 0 0
RFCO requests 7 1 0
Read directory info 0 0 0
Read storage statistics 224 224 224
WAR requests 10 1 0
WARM requests 0 1 0
Pages written via WARM 0 2 0
Register page 6 2 0
Unregister Page 0 0 0
Delete name 2 1 0
Asynch. GBP requests 16 17 16
Explicit X-invalidations 0 0 0
GBP checkpoints triggered 8 8 8
Participation GBP rebuild n/p n/p n/p
Castout engine not available n/p n/p n/p
Write engine not available n/p n/p n/p
Read failed-no storage n/p n/p n/p
Write failed-no storage 0 0 0
Write to secondary GBP n/p n/p n/p
Write to secondary GBP failed 0 0 0
Delete name list secondary GBP 0 0 0
Delete name from secondary GBP 0 0 0
Read castout stat. secondary GBP 0 0 0
Asynch. secondary GBP requests 0 0 0
=================================== ========== ========== ==========
⋮- Group BP Hit Ratio (%) *
- The group buffer pool hit ratio, expressed as a percentage. This
is the percentage of pages successfully retrieved from the group buffer
pool to those retrieved from a hard disk drive. Derived from the Db2 field
SGBPHITP.Calculated as:
(qbglxd + qbglmd + qbglad + qbglay + qbglaz) -------------------------------------------- * 100 (qbglxr + qbglmr + qbglar) - GBP-Dependent Getpages
- The number of Getpage requests made for GBP-dependent objects. Derived from the Db2 field QBGLGG.
- Syn.Read(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. Derived
from the Db2 field QBGLXD.
When you increase the size of the group buffer pool, 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.
- Syn.Read(XI)-No data return
- The number of requests to read a page from the group buffer pool
that were required because the page was invalidated in the member's
buffer pool. The member did not find the data in the group buffer
pool and had to retrieve the page from a hard disk drive. Derived
from the Db2 field SBGLXR. Normally, when the page in a member's buffer is cross-invalidated, the buffer is refreshed from the group buffer pool. In this instance, the requested page was not found in the group buffer pool though the page set is still GBP-dependent. The page has been removed from the group buffer pool for one of two reasons:
- Shortage of data pages and consequent reclamation of this page
- Shortage of directory entries and consequent removal of the page together with cross-invalidation (XI) of that page in the local buffer pools of all members using that page
- Syn.Read(NF)-Data returned
- 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 (NF = page
not found). The member found the page in the group buffer pool. Derived
from the Db2 field QBGLMD.
The requesting member needs a page from a table space or index space that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on a hard disk drive.
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).
- Syn.Read(NF)-No data return
- 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 (NF = page
not found). The member did not find the required data in the group
buffer pool and had to retrieve the page from a hard disk drive. Derived
from the Db2 field SBGLMR.
The requesting member needs a page from a table space or index space that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on a hard disk drive.
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). - Pages written using write-around
- The total number of pages in write around. This means the pages written by DB2 to DASD directly from the local buffer pools thus eliminating page placement to GBP and associated overhead. Derived from the DB2 field QBGLWA.
- Clean pages sync. written
- The number of clean pages that were synchronously written to the
group buffer pool from the virtual pool. Derived from the Db2 field
QBGLWC.
Only GBPCACHE ALL causes clean (unchanged) pages to be written to the coupling facility. The pages are written to the coupling facility even when the page set is not GBP-dependent. When 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
. - Changed pgs.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 member's virtual buffer pool to the coupling
facility. For duplexed GBPs the counter values reflect writes to both
primary and secondary group buffer pools. Derived from the Db2 field
QBGLSW.
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).
- Clean pages async. written
- Not applicable for versions later than Db2 Version
7. The number of clean pages that were asynchronously written to the
group buffer pool from the virtual pool. Derived from the Db2 field
QBGLAC.
Only GBPCACHE ALL causes clean (unchanged) pages to be written to the group coupling facility. In this instance pages are written even when the page set is not GBP-dependent. Asynchronous write is done under prefetch processing.
When group buffer pool caching works effectively for prefetch, the value in this field should be much smaller than the combined values inSynchronous Read (Not Found) - Data Returned
Asynchronous Reads - Data Returned
andClean pages - Read after Register Page List (RPL)
- 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. For duplexed GBPs the counter values reflect
writes to both primary and secondary group buffer pools. Derived from
the Db2 field QBGLAW.
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).
- Reg.Page List (RPL) request
- 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. Derived from the Db2 field
QBGLAX.
Performance might be improved by enabling RPL.
- Clean pages read after RPL
- Not applicable for versions later than Db2 Version 7. The number of coupling facility reads performed by prefetch to retrieve a clean page from the group buffer pool. Derived from the Db2 field QBGLAZ.
- Castout class threshold
- The number of times group buffer pool castout was initiated because
the group buffer pool class castout threshold was detected. Derived
from the Db2 field QBGLCT.
The class castout threshold is one of two group buffer pool thresholds. In most cases the default value for the class threshold (10 percent) is a good choice. Depending on your workload, altering this value can reduce hard disk drive contention during castout.
- Group BP castout threshold
- The number of times a group buffer pool castout was initiated
because the group buffer pool castout threshold was detected. Derived
from the Db2 field QBGLGT.
The GBP castout threshold, together with the 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 50 percent is a good choice. Use the ALTER GROUPBUFFERPOOL command to tune the group buffer pool thresholds.
- Pages castout
- The number of data pages that were cast out of the member's group
buffer pool. 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. Derived
from the Db2 field QBGLRC.
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 a hard disk drive, the number in this field should always be significantly more than
Unlock castout
. If it is not (for example, whenUnlock castout
is more than half ofPages castout
), the castout write I/O is inefficient; probably because you have random update patterns on the Db2 data or a low castout threshold. - 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 a hard disk drive, 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. Derived from
the Db2 field QBGLUN.
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, whenUnlock castout
is more than half ofPages castout
), the castout write I/O is inefficient; possibly because you have random update patterns on the Db2 data or a low castout threshold. - 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. Derived from the
Db2 field QBGLCC.
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.
- Read castout statistics
- The number of requests issued by the group buffer pool structure owner to determine which
castout classes have changed pages. Derived from the Db2 field QBGLCS.
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.
- RFCOM requests
- The number of Read For Castout Multiple (RFCOM) requests. Derived from the Db2 field QBGLCM.
- RFCO requests
- The number of Read For Castout (RFCO) requests. One page read per request. Derived from the Db2 field QBGLCR.
- 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. Derived from
the Db2 field QBGLRD.
If the value of this counter seems 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.
- Read storage statistics
- 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. Derived from the Db2 field QBGLOS.
- WAR requests
- The number of Write and Register (WAR) requests. Derived from the Db2 field QBGLWS.
- WARM requests
- The number of Write and Register Multiple (WARM) requests. Derived from the Db2 field QBGLWM.
- Pages written via WARM
- Not applicable to Db2 Version 7 and earlier. (With Db2 Version 8, the group coupling facility allows multiple pages to be written and registered with a single write request.) The number of pages written using Write and Register Multiple (WARM) requests. Derived from the Db2 field QBGLWP.
- 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. Derived from the Db2 field QBGLRG. - 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. Derived from the Db2 field
QBGLDG.
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.
- 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. Derived from the Db2 field
QBGLDN. 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 when data sets 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.
- Asynch. GBP requests
- The number of IXLCACHE invocations for the primary group buffer pool. Derived from the Db2 field QBGLHS.
- Explicit X-invalidations
- The number of times an explicit coupling facility cross-invalidation request was issued. Derived from the Db2 field QBGLEX.
- GBP checkpoints triggered
- The number of group buffer pool checkpoints triggered by this
member. Derived from the Db2 field QBGLCK.
The value of this counter depends on the length of the group buffer pool checkpoint interval.
- Write failed-no storage *
- The number of coupling facility write requests that could not complete due to a lack of coupling
facility storage resources. Derived from the Db2 field QBGLWF.
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.
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:- Increase the total size of the group buffer pool
- Adjust the ratio of directory entries to data entries in favor of data entries
- Write to secondary 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. Derived from the Db2 field QBGL2F.
- Delete name list secondary 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. Derived from the Db2 field QBGL2D.
- Delete name from secondary 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. Derived from the Db2 field QBGL2N.
- Read castout statistics secondary 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. Derived from the Db2 field QBGL2R.
- Asynch. secondary GBP requests
- The number of asynchronous IXLCACHE invocations for the secondary group buffer pool. Derived from the Db2 field QBGL2H.