DB2 storage and buffer pool parameters

How DB2 storage settings fit together

A subsystem parameter, often called a ZPARM, gives Db2 a startup value, default, or limit. Installation and migration panels such as DSNTIP1 and DSNTIP2 collect choices and generate the corresponding settings. A runtime command can control a particular resource more directly. These layers are related, but they are not interchangeable.

Before changing anything, capture the active DSNZPARM report, the relevant DISPLAY output, statistics over representative busy periods, and the LPAR's real-storage health. Parameter names, ranges, defaults, and whether a change is online can vary by Db2 release. The values in this lesson explain intent; the IBM documentation for your exact release remains the authority.

Buffer pool parameters

A buffer pool is DBM1 virtual storage divided into equal-size buffers. Table space and index pages are read into those buffers and reused without another disk read while they remain resident. During installation, the DSNTIP1 and DSNTIP2 workflow supplies initial sizes for the pools Db2 starts. Those initial values help create a working subsystem, but production sizing should later follow measured I/O, hit ratios, prefetch activity, page residency, and real storage.

VPSIZE and automatic sizing

VPSIZE is a count, not a number of megabytes. A 100,000-buffer 4 KB pool represents roughly 400 MB of page frames before management overhead, while the same count in a 32 KB pool represents roughly 3.2 GB. That multiplication is why copying a VPSIZE from BP0 to BP32K is dangerous. With AUTOSIZE enabled, Db2 can move between VPSIZEMIN and VPSIZEMAX. Set a minimum large enough for the pool's essential working set and a maximum that remains safe when several pools and threads grow at once.

VPSEQT divides pool capacity between sequential and random-style use. Sequential prefetch can be efficient, yet one scan should not flush every hot index page. VPPSEQT adds protection against a single parallel query taking the whole sequential allocation. These thresholds do not make SQL good or bad; they govern competition after the optimizer chooses an access path.

Deferred writes: DWQT and VDWQT

Changed pages are not necessarily written to disk at each SQL update. They become deferred-write pages. DWQT watches the changed-page level across the pool, while VDWQT watches concentration for an individual page set. Lower thresholds initiate writes earlier and can smooth a burst, but may increase write activity. Higher thresholds allow more accumulation, but leave fewer clean buffers and can create a write surge. Log records still provide recoverability; these thresholds manage data-page write timing, not whether logging occurs.

Choosing PGSTEAL

PGSTEAL(NONE) is not a magic "never perform I/O" switch. It works when a pool is dedicated and sized to contain the intended object or objects. LRU is appropriate for most mixed workloads. FIFO can help streaming workloads whose pages are unlikely to be reused. Validate a change with synchronous reads, prefetch reads, CPU, and residency rather than one hit-ratio number.

PGFIX and FRAMESIZE

PGFIX(YES) keeps the buffer pool's backing frames fixed in real storage. This can save CPU on a high-activity pool because z/OS does less page-fix work. FRAMESIZE chooses ordinary 4 KB frames or large 1 MB and, where the environment and release support it, 2 GB frames. Large frames reduce translation-lookaside-buffer pressure and page-table work. They are performance tools, not free capacity: the z/OS large-frame pools must be configured and monitored.

Estimate the total fixed commitment across every member and every fixed pool. If the LPAR begins paging because too much memory is fixed or resident, response time can collapse. Buffering intended to avoid disk I/O has then caused a more expensive paging workload.

ALTER BUFFERPOOL versus installation defaults

Installation-panel and ZPARM values establish the initial environment. The active pool is managed with the Db2 command ALTER BUFFERPOOL. A site may alter a pool online after startup and keep that operational definition in automation. Therefore, never assume that an old DSNTIP worksheet describes the current VPSIZE.

1
2
3
4
5
6
7
8
9
10
-ALTER BUFFERPOOL(BP8K1) VPSIZE(120000)
  VPSEQT(60) VPPSEQT(40)
  DWQT(30) VDWQT(5)
  PGSTEAL(LRU) PGFIX(YES) FRAMESIZE(1M)
 
-DISPLAY BUFFERPOOL(BP8K1) DETAIL
 
-- Illustrative commands only:
-- validate syntax, ranges, online-change behavior, and memory capacity
-- for your installed DB2 for z/OS release.

TBSBPOOL and IDXBPOOL are different again: they define default buffer pools for newly created table spaces and indexes when object DDL does not name a pool. TBSBPOOL keeps new table data from silently falling into an unsuitable general pool; IDXBPOOL does the same for indexes. Explicit object DDL can override the defaults. Existing objects do not automatically move just because a default changes.

1
2
3
4
5
6
7
8
-- Object-level assignments override subsystem/database defaults
CREATE TABLESPACE ORDERTS
  IN APPDB
  BUFFERPOOL BP8K1;
 
CREATE INDEX APP.IX_ORDER_CUSTOMER
  ON APP.ORDERS (CUSTOMER_ID)
  BUFFERPOOL BP4K2;

EDM pool and cache parameters

EDM means Environmental Descriptor Manager. Db2 needs executable and descriptive structures for SQL statements, plans, packages, and database descriptors. If these structures are cached, Db2 can reuse them. If they are absent or evicted too quickly, Db2 must load, allocate, or prepare them again, increasing CPU and directory activity.

The EDM statement cache is especially important for dynamic SQL. A cached dynamic statement can reuse prepared form and, when valid, its access path. The DBD cache avoids repeatedly retrieving database descriptors. The skeleton pool supports the package and plan structures that lead to executable sections. EDMDBDC is associated with DBD cache sizing in modern Db2 terminology, while EDMPOOL is frequently encountered in older manuals and inherited standards. Do not insert a historical EDMPOOL recommendation into a current release without checking how storage was separated and converted.

Measuring EDM effectiveness

A basic hit ratio is hits ÷ requests × 100. For example, 990,000 cache hits from 1,000,000 requests produce a 99% ratio. That sounds excellent, but the remaining 10,000 misses may still matter if they cluster during peak time or cause expensive prepares. Track requests, hits, misses, not-found conditions, full-cache events, evictions, and prepare or load CPU together. Compare equal workload windows before and after a controlled change.

Also look for causes outside sizing. Frequent package invalidation, constantly changing literal SQL, inconsistent special registers, or an application that never reuses statements can limit cache value. More storage cannot create reuse that the workload does not have.

DBM1 real storage and other storage parameters

Virtual storage gives Db2 a large addressable range, but performance ultimately depends on real memory. Add buffer pools, EDM areas, RID and sort resources, thread storage, compression dictionaries, LOB/XML work areas, and subsystem control blocks. Then consider concurrency: one thread's modest requirement multiplied by thousands of threads is no longer modest.

CONTSTOR, MINSTOR, and threads

CONTSTOR and MINSTOR address storage-management behavior rather than SQL semantics. MINSTOR can reduce storage retained for inactive threads, at the possible cost of allocating structures again when work resumes. CONTSTOR has evolved with Db2 storage architecture and is commonly discussed in the context of fragmentation and contraction. These are release-sensitive controls; use current IBM recommendations and observe both CPU and DBM1 storage before changing them.

Thread storage depends on statement complexity, package characteristics, cursor activity, and whether distributed threads remain inactive. Connection pooling may reduce connection cost but still leave many Db2 threads eligible for storage. Measure high-water marks and peak concurrent active work, not just sign-on counts.

RID pool and MAXRBLK

A RID is a record identifier that points to a row location. Db2 can build RID lists for list prefetch, multiple-index access, and other operations. MAXRBLK limits the RID pool's storage in the units defined by the installed release. If insufficient memory is available, a RID list can overflow to the work file database or the access method can be abandoned. That adds I/O and can explain why an apparently index-driven query still uses temporary space.

SORTPOOL and LOB/XML storage

ORDER BY, GROUP BY, DISTINCT, joins, and materialization can require a sort. SORTPOOL provides in-memory sorting capacity, but it is not simply one permanent sort area for the whole subsystem. Concurrent operations can multiply demand, and larger sorts can spill to work files. Tune using sort counts, records sorted, elapsed time, and work file I/O.

LOB and XML data use specialized storage and processing paths. Their related subsystem controls vary more across Db2 releases, so this lesson keeps them at overview level: account for LOB/XML buffers, locators, parsing or serialization work, and temporary space separately from ordinary 4 KB row pages. Monitor the actual feature counters before adjusting any LOB/XML-specific knob.

Work file database parameters

The Db2 work file database is the subsystem's temporary workshop. It holds intermediate rows that do not belong in an application's permanent table space. Sort runs, RID-list overflow, materialized intermediate results, some join work, and declared global temporary tables can all need work file pages. A fast SQL access path can still fail if temporary capacity is exhausted.

MAXTEMPS protects shared capacity by limiting how much temporary database storage one agent may consume. It is a guardrail, not a substitute for tuning SQL. When a legitimate query reaches the limit, investigate its access path, sort requirements, intermediate result size, and concurrency before merely raising MAXTEMPS.

WFDBSEP governs separation within work file processing, including how declared global temporary table activity is kept apart from other temporary work according to release capabilities. Separation can improve predictability and administration, but it requires properly defined work file table spaces. Check current documentation before changing WFDBSEP because details and migration behavior are version-specific.

Practical work file sizing

• Collect several representative peaks, including month-end reporting, utility windows, and batch overlap. Average daytime use hides the events that exhaust work files.
• Separate the observed contributors: sorts, RID overflow, declared global temporary tables, materialization, and utilities. Each has a different corrective action.
• Provide 4 KB and 32 KB work file table spaces as required by your release and workloads, with adequate disk volumes and sensible secondary allocation.
• Leave headroom for growth and failure scenarios, but alert on rapid consumption. Unlimited temporary capacity lets one accidental Cartesian join threaten everyone.
• Recheck after application releases. A new query plan or report can change temporary demand without any subsystem parameter change.

The danger of oversizing

More cache can reduce I/O only while real memory is available. Oversizing every pool and per-thread limit creates a false sense of safety. If z/OS must page DBM1 memory out and back in, the subsystem pays paging I/O, CPU, and unpredictable delays. Fixed large-frame pools make the capacity decision even more explicit because that real storage cannot be casually reclaimed.

Tune one constrained resource at a time. Record a baseline, predict the expected counter change, apply a reversible adjustment, and measure the same workload window. A lower synchronous-read count with stable paging and CPU is evidence. A prettier configured value is not.

1
2
3
4
5
6
7
A practical review sequence
1. Capture active ZPARMs and buffer pool definitions.
2. Measure peak real storage, paging, I/O, cache misses, and work file use.
3. Identify the constrained resource and the SQL or objects driving it.
4. Estimate total bytes, including page size and concurrency multipliers.
5. Change one bounded setting and verify during a comparable workload.
6. Keep rollback values and document the operational command.

Explain It Like I'm Five

Imagine DB2 runs a busy kitchen. Buffer pools are counters where cooks keep ingredients they use often. The EDM area is the recipe stand, so cooks do not fetch the same recipe every time. The RID pool is a list of shelf locations, SORTPOOL is a table for arranging items, and the work file database is extra temporary counter space. Bigger counters help until they fill the whole kitchen. If there is no room to walk, everyone becomes slower. Good DBAs give each station enough space, watch the busiest dinner service, and expand only the station that is crowded.

Exercises

1. Estimate the page storage for 80,000 buffers in a 4 KB pool and in a 32 KB pool. Explain why equal VPSIZE values do not mean equal memory.
2. A parallel reporting query is displacing random index pages. Which two sequential thresholds would you inspect, and what does each limit?
3. Compare PGSTEAL(LRU), FIFO, and NONE. Give one workload where each option may be reasonable.
4. Write a measurement plan for testing PGFIX(YES) and FRAMESIZE(1M) without risking uncontrolled real-storage pressure.
5. An EDM statement cache reports a 98% hit ratio. List three additional measurements you need before deciding to increase its size.
6. A query suddenly consumes work file space. Identify four possible consumers and two SQL-level investigations to perform before raising MAXTEMPS.
7. Explain why TBSBPOOL and IDXBPOOL affect new objects but do not replace ALTER BUFFERPOOL.