Chapter 6 Further Reading: DB2 Optimizer Internals

IBM Official Documentation

DB2 for z/OS Knowledge Center — Performance

  • "DB2 12 for z/OS: Managing Performance" (SC27-8849) The definitive reference for DB2 performance on z/OS. Part 3 covers access path selection in detail, including filter factor formulas, join method selection criteria, and EXPLAIN table formats. Keep this bookmarked — you'll reference it weekly.

  • "DB2 12 for z/OS: SQL Reference" (SC27-8855) Chapter on EXPLAIN provides the complete PLAN_TABLE column definitions, DSN_STATEMNT_TABLE structure, and EXPLAIN syntax variants. Essential for correctly interpreting non-obvious columns like MIXOPSEQ, QBLOCK_TYPE, and PRIMARY_ACCESSTYPE.

  • "DB2 12 for z/OS: Administration Guide" (SC27-8844) Part 5 covers RUNSTATS utility in exhaustive detail — all options, distribution statistics collection, statistics profiles, and inline RUNSTATS with REORG. Part 6 covers BIND options including PLANMGMT, APREUSE, and OPTHINT.

  • "DB2 13 for z/OS: What's New" If your shop is on or migrating to DB2 13, review the optimizer enhancements: improved literal replacement, enhanced predicate indexability, and AI-assisted access path recommendations (function level 500+).

IBM Redbooks

  • "DB2 12 for z/OS Technical Overview" (SG24-8383) Chapter 5 provides an excellent high-level overview of DB2 12 optimizer enhancements including improved query rewrite, enhanced hash join, and APREUSE improvements. Good for understanding the trajectory of optimizer development across DB2 versions.

  • "DB2 11 for z/OS Performance Topics" (SG24-8222) Though one version back, the performance topics Redbook remains one of the best practical guides to optimizer behavior. Chapter 3 (Access Path Improvements) and Chapter 7 (Monitoring and Tuning) contain production-tested techniques.

  • "DB2 9 for z/OS Performance Monitoring and Tuning Guide" (SG24-7421) An older but foundational Redbook. The chapters on EXPLAIN interpretation and index design are timeless — the optimizer's core algorithms haven't changed in principle since DB2 V8, only refined.

Books

Essential

  • Bonnie Baker, "DB2 SQL Performance Tuning Tips and Techniques" (McGraw-Hill) Baker spent decades as an IBM DB2 consultant. This book walks through optimizer behavior with the clarity of someone who has explained it a thousand times. The chapters on filter factors and EXPLAIN interpretation are the best published treatment of these topics. Every DBA and COBOL/DB2 developer should own this book.

  • Craig S. Mullins, "DB2 Developer's Guide" (IBM Press, 6th Edition) The comprehensive reference for DB2 application development. Part IV covers performance and optimization with detailed EXPLAIN analysis examples and index design strategies. Mullins covers the COBOL/DB2 interface in more depth than most DB2 books.

Advanced

  • Suresh Sane and Robert Catterall, "Advanced DBA Certification Guide and Reference for DB2 Universal Database for z/OS" (IBM Press) Goes deep into access path internals, including the cost model's treatment of parallel operations, star joins, and MQTs (materialized query tables). The certification guide format means every topic includes practice questions with detailed explanations.

  • Paolo Bruni et al., "DB2 for z/OS Stored Procedures: Through the CALL and Beyond" (IBM Redbook SG24-7083) Relevant to Chapter 6 because stored procedures introduce nested optimization contexts — the optimizer must plan for SQL inside stored procedures differently than standalone SQL. Understanding optimizer behavior in stored procedure context is critical for Chapter 8.

IBM Technical Articles and Presentations

  • "How the DB2 Optimizer Estimates Filter Factors" — IBM developerWorks technical article by Terry Purcell. Provides the exact formulas DB2 uses for filter factor calculation, including edge cases for LIKE, IS NULL, and BETWEEN with host variables. The most precise public documentation of DB2's filter factor arithmetic.

  • "Understanding DB2 Access Paths" — IDUG (International DB2 Users Group) presentation series. IDUG's annual conferences include sessions from IBM optimizer developers explaining recent changes. The proceedings are available to IDUG members and contain information not available in standard documentation.

  • "DB2 for z/OS RUNSTATS: Best Practices" — IBM Support technical note. Covers statistics profile design, frequency statistics selection criteria, and column group recommendations. Updated periodically as new DB2 function levels add RUNSTATS capabilities.

Conference Presentations and Community Resources

IDUG (International DB2 Users Group)

  • idug.org — IDUG's technical library contains thousands of presentations from DB2 practitioners. Search for "optimizer," "access path," and "EXPLAIN" for directly relevant content. The annual North American and European conferences include hands-on labs for EXPLAIN interpretation.

SHARE

  • share.org — SHARE sessions cover DB2 optimizer topics in the context of z/OS system-level performance. Presentations on zparm tuning, buffer pool sizing, and I/O subsystem configuration provide the system-level context that affects optimizer cost calculations.

Planet Mainframe

  • planetmainframe.com — Technical articles on DB2 performance, including practical guides to RUNSTATS scheduling, EXPLAIN interpretation, and plan regression prevention. Written by practitioners for practitioners.

Tools

IBM Tools

  • IBM DB2 Administration Tool for z/OS Includes EXPLAIN formatting, access path comparison, and plan history tracking. The Plan History feature provides functionality similar to CNB's custom EXPLAIN Baseline System described in this chapter.

  • IBM Optim Query Workload Tuner for DB2 for z/OS Automated access path analysis, index recommendation, and workload-level optimization. Can analyze a set of SQL statements together and recommend indexes that benefit the entire workload rather than optimizing one query at the expense of others.

  • IBM OMEGAMON for DB2 Performance Expert Real-time monitoring of DB2 performance including access path activity, buffer pool hit ratios, and lock contention. The "Explain" integration allows you to EXPLAIN a running query and compare it to historical baselines.

Third-Party Tools

  • BMC MainView for DB2 Real-time DB2 monitoring with EXPLAIN integration and access path history tracking. Includes automated alerting for access path changes and CPU consumption spikes.

  • Broadcom DB2 Tools (formerly CA Technologies) Suite including RC/Query for access path analysis and Plan Analyzer for EXPLAIN interpretation. The "What If" capability lets you simulate index changes without creating them.

Topics for Deeper Investigation

The following topics build on Chapter 6 concepts and will be explored in subsequent chapters:

  1. Lock avoidance and optimizer interaction (Chapter 7) — How the optimizer's access path choice affects lock granularity and duration, and how lock contention feeds back into performance.

  2. Stored procedure optimization (Chapter 8) — Nested optimization contexts, REOPT(ALWAYS) vs. REOPT(ONCE) for dynamic SQL within stored procedures, and cross-package optimization boundaries.

  3. Partition-level access paths (Chapter 9) — How partitioned tablespaces extend the optimizer's access path taxonomy with partition scan, partition-level index access, and data-partitioned secondary indexes.

  4. Parallel query optimization (Chapter 11) — How the optimizer decides to parallelize queries, the interaction between ACCESS_DEGREE and system resources, and CP vs. I/O parallelism.

  5. DB2 and CICS interaction (Chapter 12) — Thread reuse, dynamic plan selection, and how the CICS/DB2 attachment facility affects optimizer behavior for high-volume online transactions.

Citation Standards

When referencing DB2 optimizer behavior in production documentation or incident reports, cite: - The specific DB2 version and function level (e.g., "DB2 12 FL 508") - The APAR number if behavior changed due to a fix (e.g., "per APAR PH12345") - The IBM Knowledge Center article number for zparm or utility behavior - PLAN_TABLE output with BIND_TIME timestamp for reproducibility

IBM's DB2 optimizer behavior is version-specific and can change with individual APARs. Always verify behavior against your specific DB2 version.