IFCID 003 - Buffer Manager Accounting Data

This topic shows detailed information about Record Trace - IFCID 003 - Buffer Manager Accounting Data.

Record trace - IFCID 003 - Buffer Manager Accounting Data

The field labels shown in the following sample layout of Record Trace - IFCID 003 - Buffer Manager Accounting Data are described in the following section.

                                            BUFFER MANAGER ACCOUNTING DATA     
BUFFER POOL ID                       0  SYNCHRON. READ                     261 
GETPAGES                         31016  SEQ. PREFETCH                     1913 
GETPAGES FAILED                      0  LIST PREFETCH                        0 
BUFFER UPDATES                       0  DYNAMIC PREFETCH                     0 
SYNCHRON.WRITE                       0  PAGES READ ASYN-PAR              26991 
ZHYPERLINK READ                    243  ZHYPERLINK CPU TIME              10167 
DASD CACHE READ HITS                18  

The buffer pool ID used by this thread.

Field Name: QBACPID


The number of synchronous read I/O operations. DB2 increments this counter for each media manager synchronous physical read. Asynchronous I/O requests are not counted.

Field Name: QBACRIO


The number of Getpage requests. This counter is incremented by successful Getpage requests for queries processed in parallel for each thread and for all successful and unsuccessful Getpage requests for queries that are not processed in parallel.

Background and Tuning Information

Reducing the number of Getpages can improve DB2 performance by reducing the number of synchronous page reads. With fewer Getpages, the requested page is more likely to be returned from the buffer pool. CPU usage is also reduced.

Check the ratio of Getpages to SQL DML statements, as a rule of thumb, try and keep this ratio below six for a typical online transaction SQL.

You might need to modify the database and query design, for example:

  • Add indexes to tables to reduce the number of pages scanned.
  • Reassess the number of tables used and denormalize them, if necessary.

    As an example, a large table with many columns can result in several pages being fetched to satisfy a simple query requesting just a few columns. Splitting such a table into several tables with fewer columns, tailored to queries, will result in fewer pages returned for each query.

  • Use correlated rather than non-correlated queries to force the use of an index.

Field Name: QBACGET

This is an exception field.


The number of SEQUENTIAL PREFETCH requests. This is incremented for each PREFETCH request. Each request can result in an I/O read. If it does, up to 64 pages can be read for SQL and up to 128 pages for utilities. For SQL, depending on the buffer pool size, a request does not result in an I/O if all the requested pages are already in the buffer pool.

DB2 can use sequential prefetch if the data is accessed in sequential order even though sequential prefetch was not requested at bind time. This is known as sequential detection and is not included in the sequential prefetch count. Sequential detection is included in dynamic prefetch requests field.

Background and Tuning Information

Table space scans and nonmatching index scans generally use sequential prefetch.

Field Name: QBACSEQ

This is an exception field.


The number of times that 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. The agent does not wait, but control returns to the agent.

This counter is used only when queries are processed in parallel.

Background and Tuning Information

If this value is close to zero, most pages are already in the buffer pool, and wait time for synchronous I/O is small.

This counter can be high when, for example, there is a cluster index scan and the data is not truly clustered by the index key. In this instance, data pages are not accessed in their true order and the cluster ratio is not valid. Use the Runstats utility to update it.

The value of this field is also used to determine how many sequential prefetches of one page were scheduled.

Field Name: QBACNGT


The number of LIST PREFETCH requests.

Special Considerations:
  1. List prefetch allows DB2 to access data pages efficiently even if the needed data pages are not contiguous. It can be used with single index access and is always used with multiple index access.
  2. List prefetch is always used to access data from the inner table during a hybrid join.
  3. Data pages are read in quantities equal to the sequential prefetch quantity, which depends on the buffer pool size and is usually 64 pages.
  4. During bind time DB2 does not use list prefetch if the estimated number of RIDs to be processed would take 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, it is indicated in IFCID 125.

Field Name: QBACLPF

This is an exception field.


The number of times a buffer update occurs. This 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 number is incremented by 2.

This number is kept for all types of pages including data pages and work-file pages.

Background and Tuning Information

A nonzero value indicates any of the following activities:
  • Merge scan join
  • Internal sort activity on the work files
Check the access path to determine whether sort activity can be minimized or avoided.

Field Name: QBACSWS

This is an exception field.


The number of (dynamic) PREFETCH requests. This is triggered by sequential detection. This includes prefetches for segmented table spaces.

Background and Tuning Information

Dynamic prefetch is typically used for a SELECT or UPDATE that is run repeatedly, accessing the index for each access.

If sequential prefetch, list prefetch, and dynamic prefetch reads have large values, check whether the access path can be improved.

Field Name: QBACDPF

This is an exception field.


The number of immediate (synchronous) write I/O operations.

Background and Tuning Information

Although an immediate write is rare, a small nonzero value is acceptable. A large value indicates that the system needs tuning.

Field Name: QBACIMW

This is an exception field.


The number of asynchronous pages read by prefetch that the agent triggered.

Background and Tuning Information

This is used to determine the buffer pool hit ratio: (Getpage requests - Synchronous reads - Asynchronous pages read) / Getpage requests.

Field Name: QBACSIO

This is an exception field.

The amount of CPU time used for successful zHyperLink reads. zHyperLink I/O is synchronous with respect to the CPU, thus CPU time accumulates from the beginning of the I/O until it completes.

Field Name: QBACSYIT

The number of DASD reads done using zHyperLink.

Field Name: QBACSYI

The number of I/Os where the requested pages were found in the DASD subsystem cache. These I/Os could have potentially been successful if zHyperLink was used to do the I/O.

Field Name: QBACIOC