Chapter 23: Further Reading

IBM Official Documentation

z/OS

  • DB2 13 for z/OS: Performance Monitoring and Tuning Guide (SC28-1728) The authoritative reference for EXPLAIN on z/OS. Chapters on access path selection, PLAN_TABLE interpretation, and performance diagnosis. Required reading for any z/OS DBA.

  • DB2 13 for z/OS: Managing Performance (SC28-1727) Covers EXPLAIN table structures, all PLAN_TABLE columns, and the supplementary EXPLAIN tables (DSN_STATEMNT_TABLE, DSN_PREDICAT_TABLE, DSN_FILTER_TABLE).

  • DB2 13 for z/OS: SQL Reference (SC28-1714) Formal syntax for the EXPLAIN statement and the SET CURRENT EXPLAIN MODE statement.

  • DB2 12 for z/OS: What's New? — Plan Management Describes PLANMGMT(BASIC), PLANMGMT(EXTENDED), and the SWITCH option for reverting to previous access plans after regression.

LUW

  • DB2 11.5 LUW: Troubleshooting and Tuning Database Performance Covers EXPLAIN table setup, db2expln usage, Visual Explain, and operator-by-operator interpretation of access plans.

  • DB2 11.5 LUW: SQL Reference — EXPLAIN Statement Formal syntax and semantics for the EXPLAIN PLAN, EXPLAIN PLAN SELECTION, and SET CURRENT EXPLAIN MODE statements.

  • DB2 11.5 LUW: Administrative Routines and Views — EXPLAIN Tables Complete DDL and column descriptions for all EXPLAIN tables: EXPLAIN_INSTANCE, EXPLAIN_STATEMENT, EXPLAIN_OPERATOR, EXPLAIN_ARGUMENT, EXPLAIN_PREDICATE, EXPLAIN_OBJECT, EXPLAIN_STREAM.

IBM Redbooks and Technical Articles

  • DB2 for z/OS: Diagnosis Guide and Reference (SG24-6864) Practical worked examples of EXPLAIN analysis, including complex join scenarios, subquery transformation, and parallel query plans.

  • DB2 11 for z/OS Technical Overview (SG24-8180) Covers optimizer enhancements including extended EXPLAIN output, new ACCESSTYPE values, and hash join improvements.

  • DB2 Essentials: Understanding DB2 in a Big Data World, 5th Edition by Raul F. Chong and Xiaomei Wang Accessible introduction to DB2 architecture including a solid chapter on query optimization and EXPLAIN.

  • IBM developerWorks: "Interpreting DB2 EXPLAIN Information" A classic article series that walks through EXPLAIN output interpretation with annotated examples.

Books

  • DB2 Developer's Guide, 6th Edition by Craig S. Mullins Chapter on performance tuning includes extensive coverage of PLAN_TABLE interpretation, predicate analysis, and access path troubleshooting. One of the best third-party references for z/OS DBAs.

  • DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews Focused entirely on SQL performance tuning with heavy emphasis on EXPLAIN analysis. Includes dozens of real-world before-and-after examples.

  • DB2 Performance Expert for Multiplatforms — IBM Knowledge Center Documentation for IBM's performance monitoring tool, which includes visual access plan analysis, historical plan comparison, and automated regression detection.

Online Resources

  • IDUG (International DB2 Users Group) — idug.org Conference presentations and technical library with hundreds of sessions on EXPLAIN analysis and access path optimization from experienced practitioners.

  • DB2 for z/OS Optimizer Team Blog Technical blog posts from the IBM DB2 optimizer development team explaining optimizer behavior, new features, and access path selection logic.

  • Planet DB2 (planetdb2.com) Aggregator of DB2 blog posts from the community. Search for "EXPLAIN" or "access path" for practitioner perspectives.

  • Chapter 17: RUNSTATS and Catalog Statistics — The quality of EXPLAIN output depends directly on the quality of your statistics. Stale statistics produce misleading access plans.

  • Chapter 22: Index Design and Management — Index design is the primary tool for influencing access plans. Understanding EXPLAIN output tells you which indexes are needed and how they are being used.

  • Chapter 24: Advanced Query Optimization — Builds on EXPLAIN skills with query rewriting techniques, optimization profiles, and workload-level performance management.

  • Chapter 25: Monitoring and Diagnosing Performance — Covers runtime performance monitoring that complements EXPLAIN's compile-time analysis.

Practice Environments

  • IBM Db2 Community Edition (LUW) Free download for development and learning. Includes full EXPLAIN support and db2expln utility. Ideal for practicing the exercises in this chapter. Download: ibm.com/products/db2-database

  • IBM Z Development and Test Environment (ZD&T) Provides a z/OS environment on x86 hardware for development and testing, including DB2 for z/OS with full EXPLAIN support.