DB2 Buffer Pools

Reading from disk (DASD) is slow compared to reading from memory. DB2 buffer pools are areas of virtual storage where DB2 keeps copies of table and index pages, so most reads never touch the disk. Tuning buffer pools is one of the DBA's most important jobs.

How buffer pools work

• When your SQL needs a row, DB2 looks for its page in the buffer pool first. A "hit" means it was in memory; a "miss" means DB2 must read it from DASD.
• Changed pages stay in the pool and are written back to disk later (deferred write). The log always records the change first, so no data is lost.
• Each pool has a fixed size measured in 4K pages. DB2 provides pools BP0 to BP49 for 4K pages, plus BP8K, BP16K and BP32K pools for larger page sizes.
• BP0 is special: it holds the DB2 catalog and directory pages.

Sizing and monitoring a pool

• The size is set with VPSIZE (number of buffers). A bigger pool usually gives a better hit ratio, but it uses more storage.
• You can change the size while DB2 is running - no restart needed.
• Example:-
  ALTER BUFFERPOOL BP1 VPSIZE(4000);
• This sets pool BP1 to 4000 buffers (about 16 MB). Use -DISPLAY BUFFERPOOL(BP1) DETAIL to see the hit ratio before and after.

Which pool to use

• Tables and indexes are assigned to a pool when the tablespace or index is created (BUFFERPOOL BPn clause). The default is BP0.
• Keep hot, frequently-read tables in their own pool so batch scans do not push their pages out.
• Sequential scans can use sequential prefetch, which reads many pages at once into the pool - good for batch, but it can flood a small pool.
• VPSEQT sets the percentage of the pool that sequential prefetch may use, protecting random-access pages from being pushed out.