Sequential prefetch has been a part of DB2 since just about its beginning, going back to Version 1.2. When you think about what it can do for an SQL statement, it’s hard to imagine getting along without it. A few releases after V1.2, we gained a variation on sequential prefetch called list prefetch. Eventually, something called dynamic prefetch was added. Prefetch, or some form of it, always seems to be changing. If it isn’t increasing the number of prefetch engines used by sequential, list and dynamic prefetch (currently set to 600), it’s removing the VSAM Control Interval (CI) restriction from list prefetch. Regardless, prefetch can often be useful; it can improve your performance because it lets CPU and I/O processing overlap. This article discusses the latest enhancements to prefetch and the different types of prefetch function.

Sequential Prefetch

Usually, DB2 can process sequentially accessed pages in the buffer pool faster than the I/O subsystem can satisfy DB2’s request for another page. When DB2 is aware that this sequential access could happen, it can choose sequential prefetch as the access path. DB2’s sequential prefetch is an asynchronous process that reads some number of pages into the buffer pools before DB2 actually needs to use them. The number of pages read is usually based on the buffer pool size.

Each prefetch read occurs when a “trigger page” is reached. The trigger page also is determined by the number of pages read in each prefetch operation. The idea is to stage the pages that DB2 (actually DB2’s buffer manager component) may possibly need in the pool before DB2 actually requires them. Remember that DB2 thinks it’s sequentially reading these pages. This readahead processing should reduce the chances of DB2 having to wait for a page I/O to complete before satisfying a getpage request.

Although the number of pages in a prefetch varies based on the buffer pool size, for this example let’s assume we have an adequate buffer pool size defined to support a 32-page prefetch. When prefetch is first requested, DB2 will immediately perform two prefetch operations, each reading 32 pages into the buffer pool. This will give DB2 a total of 64 pages to work with. When the first trigger page is reached, the next 32 pages are read.

This technique of reading a full prefetch ahead is sometimes referred to as double buffering. For now, sticking with our 32-page example, let’s assume DB2 starts its first prefetch operation at page 1. The first prefetch will read pages 1 through 32, and the second prefetch, starting immediately after the first begins, will read in pages 33 through 64. When both prefetches complete, DB2 should have 64 pages in the buffer pool waiting on getpage requests.

When the trigger page (in this case, page 32) is reached, DB2 initiates another prefetch for pages 65 to 96. From this point forward, any subsequent prefetch is triggered on the next 32-page boundary (in this example, pages 64, 96, 128, 160, etc.). Again, this assumes DB2 is performing a 32-page prefetch. As DB2 is reading in the next 32 pages, DB2 is processing getpage requests against the 32 pages DB2 brought into the pool on the previous prefetch. Although our example always refers to 32 pages being read in, it doesn’t always mean that DB2 will actually read in 32 pages. If DB2 attempts to read in a page via prefetch that’s already in the buffer pool, DB2 won’t try to reread the page (see Figure 1).

What exactly is meant by the term “adequate buffers” that was used earlier? The prefetch quantity is determined by your buffer pool size (VPSIZE) and table space (or index space) page size. If you remember when prefetch was new, we made a huge deal over buffer pool sizes because the buffer pool size directly affected the number of pages prefetched. You needed a pool size greater than or equal to 1,000 pages to get prefetch to fetch 32 pages (the maximum at that time).

This long-standing value has finally changed. In DB2 9, all we previously knew is still true for buffer pools less than 40,000 pages. However, when your pool sizes exceed 40,000 pages, you’ll now get larger prefetch quantities. If your buffer pool is greater than 40,000 and less than or equal to 80,000 pages, you can get a sequential prefetch quantity of 64 pages and 64 pages for utilities. The utility page quantity jumps to 128 pages when you get to 80,000 pages less than or equal to VPSIZE * VPSEQT (although the sequential prefetch value remains at 64).