Chapter 22: Further Reading

IBM Official Documentation

z/OS

  • DB2 12 for z/OS: Managing Performance (SC27-8846). The definitive IBM reference for DB2 z/OS performance. Chapters on access path selection, optimization, and the cost model are essential reading.
  • DB2 12 for z/OS: Administration Guide (SC27-8844). Covers RUNSTATS utility in detail, including frequency values, histograms, and column group statistics.
  • DB2 12 for z/OS: Codes (GC27-8847). Reference for EXPLAIN output interpretation, including ACCESSTYPE, MATCHCOLS, PREFETCH, and METHOD columns.
  • DB2 12 for z/OS: SQL Reference (SC27-8855). Formal specification of OPTIMIZE FOR, FETCH FIRST, and related clauses.

LUW

  • DB2 11.5 Knowledge Center: Query Optimization. IBM's online documentation for the LUW optimizer, including optimization levels, MQT routing, and statistical views. Available at https://www.ibm.com/docs/en/db2/11.5
  • DB2 11.5 Knowledge Center: RUNSTATS Command. Complete reference for LUW RUNSTATS options, including distribution statistics and column groups.
  • DB2 11.5 Knowledge Center: Query Tuning. Covers EXPLAIN tools, optimization profiles, and the db2advis index advisor.

IBM Redbooks

  • DB2 12 for z/OS Technical Overview (SG24-8482). Includes coverage of DB2 12 optimizer enhancements, including hash join on z/OS, improved cost models, and plan management features.
  • DB2 11 for z/OS Performance Topics (SG24-8222). Deep dive into performance topics including optimizer internals, access path selection, and real-time statistics.
  • DB2 10 for z/OS: Optimizer and Statistics (REDP-4901). A focused Redpaper on the optimizer's use of statistics. Although written for DB2 10, the fundamental concepts remain relevant.
  • DB2 Performance Expert for Multiplatforms (SG24-7473). Covers performance monitoring and tuning on LUW, including EXPLAIN analysis and optimizer diagnostics.

Books

  • Zikopoulos, Baklarz, and Scott. DB2 pureScale: Risk Free Agile Scaling. Contains sections on optimizer behavior in partitioned and pureScale environments.
  • Mullins, Craig S. DB2 Developer's Guide, 6th Edition. Comprehensive reference with excellent chapters on the optimizer, access paths, and SQL tuning. The filter factor discussion is particularly well done.
  • Lukasiak, Chris, and Padgett, Paul. Advanced DBA Certification Guide and Reference for DB2 Universal Database for Linux, UNIX and Windows. Includes detailed optimizer coverage relevant to the DB2 certification exams.
  • Zikopoulos, Baklarz, Eaton, and Melnyk. DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS. Covers how stored procedures and SQL PL interact with the optimizer.

IBM Technical Articles and Blogs

  • "Understanding DB2 Optimizer Behavior" — IBM developerWorks article series covering filter factors, join methods, and access path selection with worked examples.
  • "DB2 for z/OS: Best Practices for RUNSTATS" — IBM technical paper on when and how to collect statistics, including real-time statistics and profile-based RUNSTATS.
  • "Optimization Profiles and Guidelines in DB2 LUW" — Technical article on using optimization profiles as an alternative to hints on LUW.

Conference Presentations and Technical Papers

  • IDUG (International DB2 Users Group) proceedings. IDUG North America and EMEA conferences regularly feature sessions on optimizer internals and access path tuning. Search the IDUG content library at https://www.idug.org for presentations on "optimizer," "access path," and "filter factor."
  • IBM SHARE proceedings. The SHARE conference includes deep-dive sessions on DB2 z/OS optimizer internals, often presented by IBM Lab Services engineers.
  • Selinger, P.G., Astrahan, M.M., et al. "Access Path Selection in a Relational Database Management System." Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data. The foundational paper on cost-based optimization, written by the team that built the System R optimizer — the direct ancestor of DB2's optimizer. Still relevant and remarkably readable.

The optimizer is covered extensively in these IBM DB2 certifications:

  • C2090-600: DB2 11.1 Fundamentals — Basic optimizer concepts, access path types.
  • C2090-621: DB2 11.1 DBA for LUW — RUNSTATS, EXPLAIN, optimization levels, optimizer configuration.
  • C2090-312: DB2 12 for z/OS DBA — Access path selection, Stage 1/Stage 2, PLANMGMT, REOPT, RUNSTATS strategies.
  • Chapter 23: EXPLAIN Tools — Learn to read the optimizer's decisions using EXPLAIN output, Visual Explain, and the PLAN_TABLE.
  • Chapter 24: SQL Tuning — Apply your understanding of the optimizer to systematically improve query performance.
  • Chapter 25: Buffer Pool Tuning — Understand how buffer pool configuration affects the optimizer's I/O cost calculations.