Db2 buffer pools overview

Buffer pools

Buffer pools are areas of virtual storage that temporarily store pages of table spaces or indexes. When an application accesses a row, Db2 places the page that contains that row in a buffer. Access to data in this temporary storage is faster than accessing data on disk. If the page is already buffered, the program does not wait for a retrieve from disk—time and cost drop.

Buffer pools live in the database services address space (ssnmDBM1). IBM documents that a buffer pool can be sized up to a very large maximum (on the order of terabytes in current materials). Sizes are not “set and forget”: pools require monitoring and tuning because they are critical to the performance of applications that touch data in those pools.

Defaults on CREATE DATABASE

You can specify default buffer pools for user data and for indexes when defining a database. Objects can override those defaults. That is why CREATE DATABASE often shows BUFFERPOOL and INDEXBP clauses next to STOGROUP.

1
2
3
4
CREATE DATABASE APPDB
  STOGROUP MYSTOGRP
  BUFFERPOOL BP1
  INDEXBP BP2;

BP1 might hold table data pages; BP2 might hold index pages—common separation so index traffic and data traffic do not thrash one shared pool, though real standards vary by site.

Page size and pool assignment

Table spaces are divided into pages. The page size is controlled by the buffer pool assigned to the table space: 4 KB, 8 KB, 16 KB, or 32 KB. Default is 4 KB. Wider rows or certain designs may use larger pages—chosen carefully with DBA guidance.

Indexes also use buffer pools. Mixing everything into one tiny pool can force constant steal of buffers (evicting pages to make room), which shows up as I/O wait and busy DBM1 memory behavior. Separating hot indexes, large scanned tables, and LOB-related spaces into appropriate pools is a classic tuning pattern—you will see pool names in object DDL long before you tune thresholds yourself.

Why beginners should care

• EXPLAIN and performance — slow queries are not only “missing index”; they may also drive huge page reads through undersized pools
• DDL literacy — BUFFERPOOL clauses on databases and table spaces are not decoration
• Ops language — “hit ratio,” “prefetch,” and “VPSEQT” appear in tuning reports; this overview is the map legend
• Design humility — SELECT * over wide tables pulls more page data into pools than a narrow column list

Group buffer pools (data sharing awareness)

In Parallel Sysplex data sharing, a special group buffer pool resides in the coupling facility. It helps multiple Db2 members share information and keep data coherent when the same pages might be cached in more than one subsystem. If you are not on data sharing yet, remember the name; local buffer pools in DBM1 remain the day-one concept.

What this overview is not

It is not a full guide to ALTER BUFFERPOOL thresholds, automatic sizing, or interpreting every statistics trace. Those belong in performance deep-dives. Here, fix the mental model: disk holds pages persistently; buffer pools hold pages for fast reuse; SQL still names tables.

1
2
3
4
5
6
7
-- Application SQL (unchanged by pool internals)
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
 
-- Behind the scenes: index/data pages checked in their buffer pools,
-- with disk reads on misses

Explain It Like I'm Five

Imagine library books on far-away shelves (disk). A buffer pool is a small cart of books kept next to your reading chair. If the book is already on the cart, you read immediately. If not, someone fetches it from the shelf onto the cart. A bigger cart helps when many friends read the same popular books. Grown-ups decide how big each cart is so the library stays quick.

Exercises

1. Describe a buffer pool hit vs miss in one sentence each.
2. Why might a DBA put indexes in a different buffer pool from a large fact table?
3. Which address space holds local buffer pools?
4. How does buffer pool choice affect table space page size?
5. Name one reason SELECT * can put more pressure on buffer pools than a short column list.