Choosing a page-stealing algorithm

When Db2 must remove a page from the buffer pool to make room for a newer page, the action is called stealing the page from the buffer pool.

About this task

By default, Db2 uses a least-recently-used (LRU) algorithm for managing pages in storage. This algorithm removes pages that have not been recently used and retains recently used pages in the buffer pool. However, Db2 can use different page-stealing algorithms to manage buffer pools more efficiently.

To specify the page-stealing algorithm:

1. Determine which page-stealing algorithm is the most efficient for the buffer pool.

   Option	Description
   PGSTEAL(LRU)	Use this option in most cases. Db2 uses a least recently used algorithm to determine when to make buffer pool pages available for stealing. This option keeps pages in the buffer pool that are being used frequently and removes unused pages. It ensures that the most frequently accessed pages are always in the buffer pool. This option has extra costs for LRU chain maintenance and might cause extra latch contention.
   PGSTEAL(FIFO)	Db2 uses a first in first out algorithm to determine when to make buffer pool pages available for stealing. This option removes the oldest pages in the buffer pool, no matter how frequently they are referenced. This approach to page stealing reduces the cost of determining which pages can be removed, and reduces internal latch contention which result from the LRU algorithm. Specify this option for buffer pools that have no I/O, that is buffer pools that have table space or index entries that always remain in memory.
   PGSTEAL(NONE)	Db2 pre-loads the pages into the buffer pool when an object is opened and tries to keep all pages for an object resident in the buffer pool while the object is open. Db2 implicitly creates an overflow area for pages that do not fit in the buffer pool. The size of the overflow areas is based on the VPSIZE value for the buffer pool. Generally, the overflow area is 10 percent of the VPSIZE value in the range of 50–6400 buffers. Page stealing can occur in the overflow area. LRU chains are maintained for the overflow area, but buffer stealing in the overflow area uses the FIFO page-stealing algorithm. Specify this option only for buffer pools for frequently accessed objects of stable and predictable size. You can use the following real-time statistics values to identify candidate objects: GETPAGES NACTIVE REORGINSERTS To realize the benefits of this option, you must ensure that the buffer pool is large enough to contain both the overflow area and all of the pages for all assigned objects that might be open at the same time. Pages the are allocated in to the overflow area cannot be returned to the main part of the buffer pool until the object is closed. A DSNB604I message is issued when the overflow area is used. PGSTEAL(NONE) and FRAMESIZE(2G) are not compatible in Db2 12. If you specify these options together, Db2 issues message DSNB549I and uses the PGSTEAL(LRU) algorithm until the next allocation of the buffer pool. However, PGSTEAL(NONE) is recorded in the BSDS. To use PGSTEAL(NONE), specify FRAMESIZE(1M) or FRAMESIZE(4K). For more information, see Changed behavior for PGSTEAL(NONE) buffer pools in Storage release incompatibilities in Db2 12.

2. Issue an ALTER BUFFERPOOL command, and specify the PGSTEAL option.

What to do next

Monitor buffer pools that are defined with the PGSTEAL(NONE) option to verify that the size is large enough to contain the object. Db2 issues a DSNB604I message whenever the overflow area for a PGSTEAL(NONE) buffer pool is used. The output of the DISPLAY BUFFERPOOL DETAIL command contains statistics about the use of overflow areas in the DSNB416I message.