DB2 buffer pool design and tuning

Pool names and page sizes

Db2 provides families of pools by page size. You assign a table space or index to a pool of the matching page size (BUFFERPOOL clause on CREATE/ALTER). Common names you will see in shops:

There are fifty 4 KB pools (BP0–BP49), ten 8 KB (BP8K0–BP8K9), ten 16 KB (BP16K0–BP16K9), and ten 32 KB (BP32K–BP32K9). You do not have to allocate every name; unused pools can stay at VPSIZE 0.

BP0, BP1, BP2

BP0 is the installation default 4 KB pool. Keep catalog and directory (and usually other system 4 KB objects) here so user tablespace scans cannot flush catalog pages. BP1 and BP2 are the next 4 KB pools people carve out—for example BP1 for 4 KB table spaces and BP2 for 4 KB indexes, or BP1 for OLTP random access and BP2 for sequential history. Isolation is the point: mixed sequential and random traffic in one pool fights over VPSEQT.

BP8K0, BP8K1, BP16K0, BP16K1, BP32K

Wider pages hold longer rows and fewer rows per I/O. BP32K is required for 32 KB table spaces and is important for work file databases (declared global temporary tables need a 32 KB work file table space). Sort records longer than 100 bytes prefer 32 KB work files. Do not dump all user 4 KB data into BP32K “for headroom”—page size is a table-space design choice, not a tuning cheat.

Buffer pool sizing (VPSIZE)

VPSIZE is the number of buffers. Memory used is roughly VPSIZE × page size. IBM’s performance book: specify the largest sizes you can back with real storage. Undersize and you do extra I/O. Oversize without real storage and z/OS pages the pool—often worse than a smaller pool that stays resident.

• PGSTEAL(LRU) — IBM recommends buffer pool simulation to pick size
• PGSTEAL(NONE) — VPSIZE must hold every page of assigned objects that might be open, plus overflow (about 10% of VPSIZE, between 50 and 6400 buffers)
• Index root pages tend to stay resident while the page set is open—budget a little extra

1
2
3
-DISPLAY BUFFERPOOL(BP0) DETAIL
-ALTER BUFFERPOOL(BP0) VPSIZE(20000)
-ALTER BUFFERPOOL(BP1) VPSIZE(50000) VPSEQT(10) DWQT(30) VDWQT(5)

Buffer pool thresholds

Sequential steal (VPSEQT): when sequentially accessed pages exceed this percent, Db2 prefers to steal sequential buffers so random (often OLTP) pages survive. Default 80 is high—fine for mostly sequential pools, too generous for a pool that must protect random GETPAGEs. Lower VPSEQT (for example 10–20) on OLTP data/index pools.

DWQT (deferred write threshold, historically ~30%): when unavailable pages (updated or in-use) exceed the percent, Db2 schedules writes for enough data sets (up to 128 pages each) to get about 10% below the threshold. VDWQT (vertical deferred write) is per data set so one hot page set cannot dirty the whole pool before writes start. Lower DWQT/VDWQT for smoother trickle writes; very high values make checkpoint write storms.

DISPLAY BUFFERPOOL DETAIL shows DWT HIT and VERTICAL DWT HIT. Frequent hits are normal on busy pools; zero hits with huge checkpoint I/O often means thresholds are too high.

PGSTEAL: LRU, FIFO, sequential steal

LRU steal tracks recency. FIFO is cheaper to maintain and fits work files and one-time scans where reuse is rare—IBM and Redbooks often suggest FIFO for DSNDB07-style pools. Sequential steal is not a separate PGSTEAL value; it is the VPSEQT behavior that prefers stealing sequential pages first under LRU.

PGSTEAL(NONE) is an in-memory pool, not “infinite LRU.” If objects grow past VPSIZE, overflow and failures appear. Revisit size after LOAD growth.

PGFIX, FRAMESIZE, and AUTOSIZE

PGFIX(YES) fixes buffers in real storage when they are used, avoiding the disaster of z/OS paging out a page Db2 believes is cached (long response time, counters that still look like hits). Production with enough real memory should use PGFIX(YES). Db2 will not honor PGFIX if real storage is too tight (documented internal threshold around 80% of real storage for fixed pools).

FRAMESIZE selects 4 KB, 1 MB, or 2 GB frames for the pool. Large frames reduce TLB pressure for big pools. Pair large frames with PGFIX(YES) and enough real storage. You cannot always change FRAMESIZE without draining the pool.

AUTOSIZE(YES) lets WLM adjust VPSIZE between VPSIZE MINIMUM and VPSIZE MAXIMUM. Use it when you want Db2 to grow/shrink with workload, not as a substitute for a thought-out starting size. DISPLAY shows AUTOSIZE, MIN, MAX, and ALLOCATED.

1
2
-ALTER BUFFERPOOL(BP1) PGFIX(YES) FRAMESIZE(1M) PGSTEAL(LRU)
-ALTER BUFFERPOOL(BP32K) PGSTEAL(FIFO) VPSEQT(90) AUTOSIZE(NO)

Buffer pool monitoring and tuning

The hit ratio is (GETPAGEs − pages read from DASD) / GETPAGEs. Also read:

• Synchronous random reads — OLTP pain; increase VPSIZE or isolate the object
• Prefetch (sequential/list/dynamic) — sequential I/O; watch VPSEQT
• PAGE-INS REQUIRED — z/OS paging of the pool; shrink VPSIZE or add real storage; consider PGFIX
• DMTH HIT — data management threshold; pool is in trouble
• Statistics/accounting traces and OMEGAMON for longer trends

Tuning loop: DISPLAY → change one attribute → measure. Do not raise VPSIZE if PAGE-INS are already high. Separate sequential work files from random indexes. After you move objects (ALTER TABLESPACE BUFFERPOOL), rebound plans are not required for the buffer assignment itself, but access path cost can change because I/O cost assumptions move.

Group buffer pools

In data sharing, local pools still live in each member’s DBM1. Group buffer pools (GBP0 for BP0, GBP1 for BP1, …) live in the Coupling Facility. When a page is of group interest, members use the GBP for coherency (directory entries and optionally cached copies). Castout writes changed GBP pages back to DASD. GBP tuning (GBPCACHE, class castout, GBP checkpoint) is related but not the same as VPSIZE. A huge local pool with a tiny GBP still causes GBP misses and XI (cross-invalidation).

DISPLAY GROUPBUFFERPOOL and ALTER GROUPBUFFERPOOL are the GBP counterparts of the local commands. Failed or full GBPs put pages on LPL/GRECP—recovery topics for the data sharing section.

Practical design pattern

• BP0 — catalog/directory, modest VPSIZE, PGFIX YES, LRU
• BP1 — 4 KB random table spaces, lower VPSEQT, LRU, PGFIX YES
• BP2 — 4 KB indexes (or the reverse split), same idea
• BP8K0 / BP16K0 / BP32K — match object page sizes; FIFO and high VPSEQT for work files
• Small code/reference tables — dedicated PGSTEAL(NONE) pool sized to fit

IBM: largest sizes you can back with real storage; if paging rises, add memory or shrink the pool. Simulation (IBM Buffer Pool Analyzer / DISPLAY simulation options where licensed) beats guessing VPSIZE from hit ratio alone.

Explain It Like I'm Five

A buffer pool is a desk. Disk is the toy closet. VPSIZE is how big the desk is. If the toy is already on the desk (hit), play starts immediately. If not, you walk to the closet (I/O). VPSEQT is “don’t let the big pile of books you only skim once shove the favorite toy off the desk.” PGSTEAL(NONE) is gluing the favorite toys to the desk. PGFIX is nailing the desk to the floor so someone else (z/OS) cannot hide the toys under the bed. Group buffer pools are a shared tray in the hallway so kids in different rooms do not paint two different faces on the same doll.

Exercises

1. DISPLAY BUFFERPOOL(*) SUMMARY and list which pools have VPSIZE > 0 and their page sizes.
2. Compute the hit ratio for BP0 and a busy user pool from DETAIL counters.
3. Identify one pool with high SYNC READ I/O (R) and propose VPSIZE vs VPSEQT vs object placement as the first change.
4. Check PGFIX and PAGE-INS REQUIRED. Explain whether PGFIX(YES) is safe on that LPAR.
5. Map each open table space in a test database to BP0/BP1/BP8K0/BP32K and justify the assignment.