Chapter 3: Key Takeaways

  1. DB2 is not a single program — it is an ecosystem of cooperating components. On z/OS, these are separate address spaces (MSTR, DBM1, DIST, IRLM). On LUW, these are separate operating system processes (db2sysc, db2agent, db2pfchr, db2pclnr, db2loggr, etc.). This separation provides isolation, scalability, and resilience.

  2. Buffer pools are the single most important performance feature in DB2. They cache data and index pages in memory, avoiding expensive disk I/O. A well-tuned OLTP system targets a buffer pool hit ratio of 98% or higher. The difference between a memory read (nanoseconds) and a disk read (milliseconds) is roughly a factor of one million.

  3. Use multiple buffer pools to isolate workloads. Separating OLTP data from batch scan data, and indexes from table data, prevents one workload from evicting another's cached pages (buffer pool pollution). Each pool can be sized, monitored, and tuned independently.

  4. Buffer pool sizing is a cliff, not a slope. When the buffer pool is larger than the working set, hit ratios approach 100% and performance is excellent. When it drops below the working set size, thrashing occurs and performance degrades dramatically and non-linearly. Small reductions in pool size can cause catastrophic performance impacts.

  5. Write-ahead logging (WAL) is what makes DB2 both fast and reliable. Log records are written to disk before dirty data pages, guaranteeing that committed transactions survive crashes without requiring data pages to be forced to disk at commit time. This resolves the apparent paradox of speed and durability.

  6. The z/OS and LUW platforms share the same architectural concepts but differ significantly in implementation. Buffer pools, logging, locking, catalogs, and the SQL lifecycle work the same way conceptually. But the address-space model (z/OS) versus the process model (LUW), manual tuning versus STMM, SYSIBM catalog tables versus SYSCAT views, and VSAM data sets versus filesystem containers all require platform-specific knowledge.

  7. The DB2 catalog is both a documentation resource and a performance-critical component. It stores metadata about every object and the statistics the optimizer uses to choose access paths. Running RUNSTATS regularly is not optional — stale statistics lead to poor optimizer decisions and degraded performance.

  8. Every SQL statement follows the same lifecycle: connect, parse, optimize, lock, access, return. Understanding this lifecycle is essential for troubleshooting. Slow parsing points to EDM pool or catalog cache issues. Slow optimization points to complex SQL or missing statistics. Slow access points to buffer pool misses or lock contention. Diagnosing performance requires knowing which phase is the bottleneck.

  9. Logging architecture directly determines your recovery capabilities. Circular logging (LUW default) supports only crash recovery and restore from backup. Archive logging enables point-in-time recovery. For any production system — especially banking — archive logging is mandatory. Dual logging (z/OS) and HADR (LUW) add additional layers of protection.

  10. Storage architecture decisions have long-lasting consequences. The choice of tablespace type (PBR vs. PBG on z/OS, automatic storage on LUW), page size, partitioning strategy, and separation of logs from data all affect performance, manageability, and recovery for the life of the database. These are among the hardest decisions to change after deployment.

  11. On LUW, the Self-Tuning Memory Manager (STMM) can significantly reduce the manual tuning burden by automatically redistributing memory among buffer pools, sort heaps, lock lists, and package caches based on workload demand. However, it requires a properly set memory ceiling and does not eliminate the need to understand what the memory areas do.

  12. Architecture knowledge is the foundation for every other DB2 skill. You cannot effectively tune what you do not understand. You cannot troubleshoot performance without knowing the SQL lifecycle. You cannot plan for recovery without understanding the logging system. Every chapter that follows in this book builds on the architectural foundation established here.