DB2 buffer pools cache table and index pages in the database services address space so getpages avoid synchronous I/O. Configuring pools means sizing them (VPSIZE), setting steal and write thresholds, choosing page-steal and page-fix behavior, assigning objects to the right pool, and verifying hit ratios. This how-to focuses on the ALTER BUFFERPOOL and DISPLAY BUFFERPOOL workflow you use while Db2 is up.
Db2 provides pools for 4 KB, 8 KB, 16 KB, and 32 KB pages (for example BP0–BP49, BP8K0–BP8K9, BP16K0–BP16K9, BP32K–BP32K9). The BUFFERPOOL clause on a table space or index picks both the pool and the page size. VPSIZE is the number of buffers; real storage used is roughly VPSIZE times page size, plus control overhead, and is constrained by available real storage and DBM1 limits.
Thresholds steer behavior: VPSEQT limits how much of the pool sequential pages may occupy; VPPSEQT further limits parallel sequential use as a percent of VPSEQT; DWQT and VDWQT trigger deferred writes. PGSTEAL chooses LRU, FIFO, or NONE. PGFIX and FRAMESIZE affect CPU cost of I/O and large-frame usage. AUTOSIZE can let WLM adjust size within bounds.
Know which objects use which pools today, especially BP0 for catalog and directory. Have real-storage headroom, a change window, and a baseline from -DISPLAY BUFFERPOOL with detail or from statistics/accounting getpage and sync I/O metrics. In data sharing, plan matching group buffer pool changes so local and GBP designs stay aligned.
You need authority to issue ALTER BUFFERPOOL and DISPLAY BUFFERPOOL. Coordinate with capacity management before large VPSIZE increases. Do not shrink a busy pool aggressively without a rollback plan.
Display the pool you will change. Note VPSIZE, thresholds, allocated buffers, and hit-ratio oriented statistics your detail output provides. Decide whether the problem is undersizing (high sync I/O), sequential pollution of a random pool (tune VPSEQT or split pools), or write spikes (review DWQT/VDWQT).
Issue ALTER BUFFERPOOL with the attributes you intend to change. VPSIZE changes take effect as Db2 adjusts allocation; other thresholds apply as described in the Command Reference for your release. Prefer incremental size changes with measurement between steps over one giant jump.
1234-DISPLAY BUFFERPOOL(BP1) DETAIL -ALTER BUFFERPOOL(BP1) VPSIZE(100000) VPSEQT(50) DWQT(30) VDWQT(5) -ALTER BUFFERPOOL(BP2) PGSTEAL(LRU) PGFIX(YES) -ALTER BUFFERPOOL(BP3) AUTOSIZE(YES) VPSIZEMIN(50000) VPSIZEMAX(150000)
Configuration is incomplete if everything shares one pool. Keep catalog/directory in BP0, isolate work files, separate random high-hit indexes from bulk scan table spaces when metrics justify it, and match 8/16/32 KB pools to those page sizes. Use ALTER TABLESPACE/INDEX BUFFERPOOL pending changes and the appropriate online or REORG path your release requires to move objects.
Document every ALTER in the runbook. If you enable AUTOSIZE, set VPSIZEMIN and VPSIZEMAX so WLM cannot starve other pools. Review PGFIX(YES) only when real storage is sufficient; fixed pages reduce I/O CPU but increase real-frame commitment.
Reissue DISPLAY BUFFERPOOL and confirm the new VPSIZE and thresholds. Watch sync read I/O, hit ratio, and prefetch behavior under representative load. Compare accounting getpage and I/O waits for critical packages before and after. In data sharing, DISPLAY GROUPBUFFERPOOL and check for write-fail or shortage conditions after local pool changes that increase sharing traffic.
Keep the previous settings recorded so you can ALTER back if elapsed time or real-storage paging worsens. Verification is metric-based, not just “command completed.”
12-DISPLAY BUFFERPOOL(BP1) DETAIL(*) -DISPLAY GROUPBUFFERPOOL(*)
ALTER failures or no useful effect often come from insufficient authority, invalid threshold combinations, or requesting more storage than the system can give—resulting in truncated allocation versus the VPSIZE you asked for. Always DISPLAY after ALTER to see what was actually allocated.
Tuning mistakes include enlarging a pool while the LPAR is storage-constrained (causing paging that destroys gains), setting VPSEQT to 0 and unintentionally disabling sequential prefetch behavior you still need, fixing pages without real storage, and ignoring GBP sizing in data sharing. Fix one pool at a time and measure.
A buffer pool is Db2’s desk space for pages it recently read. A bigger tidy desk means fewer trips to the filing cabinet (disk). Configuring the desk means choosing how big it is, how much space messy sequential piles may use, and when to put finished papers back in the cabinet. DISPLAY looks at the desk; ALTER changes the desk rules.
1. What does VPSIZE specify?
2. Which command changes buffer pool attributes while Db2 is running?
3. What is VPSEQT?