Chapter 24 Further Reading: SQL Tuning

IBM Official Documentation

  • DB2 for z/OS Performance Monitoring and Tuning Guide (SC27-8850) Chapters on SQL optimization and predicate analysis. Includes the complete Stage 1/Stage 2 predicate classification tables and detailed examples of predicate rewriting.

  • DB2 for z/OS Managing Performance (SC27-4820) Comprehensive coverage of access path selection, including how the optimizer evaluates sargable predicates, join methods, and sort avoidance.

  • DB2 11.5 LUW Performance Tuning and Monitoring IBM Knowledge Center section covering query optimization, EXPLAIN facilities, and the self-tuning memory manager. URL: https://www.ibm.com/docs/en/db2/11.5?topic=tuning-query-performance

  • DB2 for z/OS EXPLAIN Reference (SC27-8844) Detailed reference for PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_FILTER_TABLE, and related EXPLAIN tables. Essential for verifying Stage 1/Stage 2 classification.

Books

  • DB2 Developer's Guide by Craig S. Mullins (6th Edition) Chapter 20 covers SQL tuning in depth with dozens of before/after examples. The predicate analysis sections are particularly strong for z/OS.

  • DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews A focused treatment of SQL tuning specifically for z/OS, with extensive coverage of Stage 1/Stage 2 predicates and join optimization.

  • SQL Performance Explained by Markus Winand While not DB2-specific, this book provides the best conceptual treatment of indexing, sargability, and join optimization across all major databases. The visual explanations of B-tree traversal are exceptionally clear. Companion website: https://use-the-index-luke.com

  • DB2 12 for z/OS Database Administration (Certification Study Guide) by Robert Collins and others Chapters on performance tuning cover MQTs, query optimization hints, and access path analysis.

IBM Redbooks

  • DB2 12 for z/OS Technical Overview (SG24-8482) Covers new optimization features in DB2 12, including improved predicate handling and hash join support.

  • Optimizing DB2 Queries with IBM Data Studio (SG24-8414) Practical guide to using IBM Data Studio's visual EXPLAIN and tuning advisor capabilities.

  • DB2 11 for z/OS Performance Topics (SG24-8222) Deep dive into performance improvements including enhanced predicate pushdown, improved MQT routing, and new optimization statistics.

Technical Articles and Papers

  • "The Art of SQL Tuning in DB2 for z/OS" — IDUG Technical Conference proceedings Annual conference presentations from IBM and customer DBAs sharing real-world tuning case studies.

  • "Sargable Predicates and Index Design" — IBM developerWorks (archived) Classic article explaining the relationship between predicate form and index usage.

  • "Pagination Done the Right Way" by Markus Winand https://use-the-index-luke.com/sql/partial-results/fetch-next-page Comprehensive treatment of keyset pagination vs offset pagination with DB2-specific examples.

Online Resources

  • IDUG (International DB2 Users Group) — https://www.idug.org Presentations and papers from annual conferences. The performance track consistently includes SQL tuning case studies with measured before/after results.

  • DB2 SQL Cookbook by Graeme Birchall Free PDF covering advanced SQL patterns, many of which have direct tuning implications. Updated through DB2 11.

  • Planet DB2 — https://planetdb2.com Aggregated blog posts from DB2 professionals worldwide. Search for "SQL tuning" or "predicate analysis" for practical tips.

Practice Environments

  • IBM Db2 Community Edition Free download for Linux, limited to 4 cores and 16 GB RAM. Sufficient for practicing all LUW-specific tuning techniques from this chapter, including MQTs, expression indexes, and EXPLAIN analysis.

  • IBM Z Development and Test Environment (ZD&T) Licensed product that provides a z/OS environment on x86 hardware. Enables practicing z/OS-specific techniques including Stage 1/Stage 2 predicate analysis and EXPLAIN table queries.

  • Chapter 8: Subqueries and CTEs — Foundation for understanding correlated vs non-correlated subqueries and when to rewrite them.
  • Chapter 15: Index Design — Index strategies that complement SQL tuning (covering indexes, composite key order).
  • Chapter 22: The DB2 Optimizer — How the optimizer chooses access paths, which explains why certain rewrites improve plans.
  • Chapter 23: Reading EXPLAIN Output — The practical skill of interpreting EXPLAIN output to verify your tuning hypotheses.
  • Chapter 25: Buffer Pool and Memory Tuning — System-level tuning that complements SQL-level tuning.