Buffer Pools

Buffer pools are areas of virtual storage that temporarily store pages of tablespaces or indexes. Buffer pools are maintained by subsystem and, in some cases, also by application.

When a program accesses a row of a table, DB2 places the page containing that row in a buffer. When a program changes a row of a table, DB2 writes the data in the buffer back to disk at a DB2 system checkpoint or a write threshold. The write thresholds are a vertical threshold at the page set level or a horizontal threshold at the buffer pool level.

The proper tuning of the buffer pool operations increases application performance. The data manager issues GETPAGE requests to the buffer manager. The buffer manager uses the data from the buffer pool instead of having to retrieve the page from the disk. CPU is often traded for I/O to manage buffer pools efficiently.

DB2 buffer pool management lets the subsystem alter and display buffer pool information dynamically without requiring a bounce of the DB2 subsystem. This ability improves availability by letting you dynamically create new buffer pools when necessary and dynamically modify or delete buffer pools. You have to perform an ALTER of buffer pools several times a day because of varying workload characteristics.

Initial buffer pool definitions are set at installation and migration, but they are hard to configure during installation and migration because the application process against the objects is not detailed at that time. Use ALTER to add and delete new buffer pools, resize the buffer pools, or change any of the thresholds. The buffer pool definitions are stored in BSDS Boot Strap Dataset and you can move objects between buffer pools using an ALTER INDEX/TABLESPACE and a subsequent START/STOP command of the object.