Chapter 25 Further Reading: Buffer Pool and Memory Tuning

IBM Official Documentation

z/OS

  • DB2 13 for z/OS: Performance Monitoring and Tuning Guide (SC28-A006) Chapters on buffer pool monitoring, memory tuning, and the performance statistics trace. The definitive reference for z/OS buffer pool parameters and their interactions. Covers VPSIZE, VPSEQT, DWQT, VDWQT, PGSTEAL, and PGFIX in full detail.

  • DB2 13 for z/OS: Administration Guide (SC28-A003) Covers buffer pool creation, ALTER BUFFERPOOL syntax, table space-to-buffer-pool assignment, and EDM/RID/sort pool configuration.

  • DB2 13 for z/OS: Managing Performance (SC28-A005) Practical guidance on buffer pool sizing strategies, VPSEQT tuning, deferred write threshold management, and hiperpool migration.

  • DB2 13 for z/OS: Command Reference (SC28-A008) Complete syntax and output field descriptions for -ALTER BUFFERPOOL, -DISPLAY BUFFERPOOL, and -DISPLAY RESOURCE MANAGER DETAIL.

  • DB2 13 for z/OS: Codes (GC28-A011) Interpretation of IFCID records (0001, 0002, 0003) that contain buffer pool and memory statistics. Essential for building custom monitoring based on the statistics trace.

LUW

  • DB2 11.5 Knowledge Center: Memory Model Overview https://www.ibm.com/docs/en/db2/11.5?topic=memory Describes the instance memory, database memory, and application memory hierarchy on LUW.

  • DB2 11.5 Knowledge Center: Buffer Pool Management https://www.ibm.com/docs/en/db2/11.5?topic=pools-buffer CREATE BUFFERPOOL and ALTER BUFFERPOOL syntax, NUMBLOCKPAGES, page size considerations, and STMM integration.

  • DB2 11.5 Knowledge Center: Self-Tuning Memory Manager https://www.ibm.com/docs/en/db2/11.5?topic=memory-self-tuning How STMM works, which memory consumers it manages, and how to enable and monitor it.

  • DB2 11.5 Knowledge Center: Monitoring Table Functions https://www.ibm.com/docs/en/db2/11.5?topic=functions-mon-get-bufferpool Reference for MON_GET_BUFFERPOOL, MON_GET_DATABASE, MON_GET_TABLESPACE, MON_GET_MEMORY_POOL, and other monitoring functions used throughout this chapter.

  • DB2 11.5 Knowledge Center: Database Configuration Parameters https://www.ibm.com/docs/en/db2/11.5?topic=parameters-database-configuration Complete reference for DATABASE_MEMORY, INSTANCE_MEMORY, SORTHEAP, SHEAPTHRES_SHR, PCKCACHESZ, LOCKLIST, and all memory-related parameters.

IBM Redbooks

  • DB2 for z/OS: Buffer Pool Monitoring and Tuning (SG24-6498) Dedicated Redbook on z/OS buffer pool tuning. Covers pool sizing methodology, VPSEQT tuning with real examples, deferred write threshold analysis, and hiperpool management. Includes sample JCL and monitoring scripts.

  • DB2 11 for z/OS Performance Topics (SG24-8222) Performance improvements in DB2 11 including buffer pool enhancements, expanded buffer pool limits, and memory management changes. Real-world tuning scenarios with before/after metrics.

  • DB2 12 for z/OS Technical Overview (SG24-8435) Coverage of buffer pool and memory changes in DB2 12, including hiperpool deprecation guidance and 64-bit virtual storage improvements.

  • DB2 Best Practices: Performance Tuning for LUW (REDP-4471) Practical buffer pool and memory tuning advice for DB2 LUW, including STMM configuration, page size selection, and monitoring strategies.

  • Optimizing DB2 UDB Using Self-Tuning Memory Manager (SG24-7254) Dedicated Redbook on STMM internals: how it evaluates benefit-per-megabyte across memory consumers, best practices for configuration, and scenarios where manual override is appropriate.

Books

  • DB2 Developer's Guide by Craig S. Mullins (6th Edition) Comprehensive reference covering both z/OS and LUW buffer pool management, memory architecture, and tuning methodology. Chapters 22-24 provide detailed, practical coverage.

  • DB2 for z/OS: DBA Certification Study Guide by Susan Lawson and Daniel Luksetich Focused sections on buffer pool parameter tuning with exam-style scenarios that test understanding of VPSEQT, DWQT, and threshold interactions.

  • DB2 Performance Handbook for OS/390 and z/OS by Zamil Janmohamed et al. An older but still relevant deep dive into z/OS buffer pool tuning. The chapter on page stealing algorithms and threshold interactions remains one of the most detailed treatments available.

  • DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews While SQL-focused, includes excellent chapters on how buffer pool configuration affects SQL performance and how to interpret EXPLAIN output in the context of buffer pool caching behavior.

  • Getting Started with DB2 Express-C by Raul Chong et al. While targeted at the free edition, the memory management and STMM chapters apply to all LUW editions. Good practical introduction to automated memory tuning.

Technical Articles and Papers

  • "Buffer Pool Tuning Best Practices for DB2 for z/OS" — IDUG Technical Conference Recurring presentations from IBM's DB2 performance team with current best practices. Post-2020 presentations include guidance on disabling hiperpool on modern hardware.

  • "Understanding DB2 Memory Allocation on Linux" — IBM developerWorks (archived) Detailed article on how DB2 LUW interacts with Linux memory management, including the impact of large pages, memory overcommit settings, and transparent huge pages.

  • "Self-Tuning Memory Manager: Under the Hood" — IBM Data Magazine An insider's view of how STMM makes redistribution decisions, including the benefit functions for each memory consumer type.

  • "The Case Against Hiperpool on Modern z/OS" — IBM Support Technical Exchange IBM's own analysis of why hiperpool should be disabled on modern z/OS systems, with performance data comparing hiperpool configurations vs. enlarged virtual pools on z14 and z15 hardware.

  • "DB2 z/OS Memory Tuning: EDM Pool, RID Pool, and Sort Pool" — IDUG Conference Proceedings Conference paper covering the non-buffer-pool memory areas on z/OS with real-world sizing examples and monitoring techniques.

Monitoring Tools

  • IBM OMEGAMON for DB2 on z/OS Real-time and historical monitoring of buffer pool statistics, EDM pool usage, RID pool failures, and sort activity. Provides threshold-based alerting and exception reporting. The standard enterprise monitoring tool for z/OS DB2.

  • IBM Data Server Manager (DSM) Web-based monitoring and administration for DB2 LUW. Includes buffer pool dashboards, memory allocation visualizations, and STMM activity tracking.

  • db2top Interactive command-line monitoring tool for DB2 LUW (similar to Unix top). The "Bufferpools" screen (press 'b') shows real-time hit ratios, physical reads, and page cleaner activity. Essential for live troubleshooting.

  • db2pd DB2 LUW problem determination tool. Key flags for memory tuning:

  • db2pd -bufferpools — buffer pool sizes, hit ratios, page statistics
  • db2pd -mempools — memory pool allocation details
  • db2pd -stmm — STMM tuning decisions and history

  • db2pd -sort — sort heap usage and overflow information

  • db2mtrk DB2 LUW memory tracker. Shows every memory allocation by category (buffer pools, lock list, package cache, sort heap, etc.). Use db2mtrk -d -v for a detailed breakdown. Essential for understanding where database memory is actually being consumed.

  • RMF (Resource Measurement Facility) for z/OS System-wide monitoring that includes DB2 memory usage within the broader z/OS resource context. Useful for detecting OS-level paging that affects DB2 buffer pools.

Online Resources

  • IDUG (International DB2 Users Group) — https://www.idug.org Annual conferences (North America and Europe) regularly feature sessions on buffer pool tuning, memory management, and performance optimization. Presentation slides are available to members. The performance tuning track consistently features buffer pool case studies from large enterprises.

  • Planet DB2 — https://planetdb2.com Aggregator of DB2 blog posts from practitioners worldwide. Search for "buffer pool" or "memory tuning" for real-world experiences and tips.

  • IBM Community — DB2 Forum Active forums where DB2 DBAs discuss buffer pool sizing, STMM behavior, and memory tuning strategies for specific workload patterns.

  • Chapter 3: DB2 Architecture — Foundational understanding of table spaces, buffer pools, and the I/O subsystem that underlies this chapter.
  • Chapter 14: Physical Database Design — Partitioning, page sizes, and extent sizing decisions that directly affect buffer pool strategy.
  • Chapter 22: The DB2 Optimizer — How the optimizer uses buffer pool assumptions in cost estimation and access path selection.
  • Chapter 24: SQL Tuning Fundamentals — SQL-level optimizations that complement buffer pool tuning by reducing I/O demand.
  • Chapter 26: Access Path Analysis — Reading EXPLAIN output and understanding how buffer pool configuration influences the optimizer's choices between index access and table scans.
  • Chapter 27: Statistics and RUNSTATS — How accurate statistics help the optimizer make better I/O predictions.
  • Chapter 30: Monitoring and Diagnostics — Comprehensive monitoring strategies that extend the memory-focused monitoring covered in this chapter.