Further Reading — Chapter 11: DB2 Performance Diagnosis

IBM Official Documentation

DB2 for z/OS Performance Monitoring and Tuning Guide

  • Reference: SC27-8850 (version varies by DB2 release)
  • Relevance: The authoritative reference for EXPLAIN tables, accounting trace fields, buffer pool management, and performance monitoring. Chapter 6 (Managing the Performance of Your Queries) and Chapter 8 (Monitoring DB2 Performance) directly support this chapter's content.
  • Read this when: You need the precise column definitions for PLAN_TABLE, DSN_STATEMNT_TABLE, or DSN_FUNCTION_TABLE. The manual has complete field-by-field descriptions that we condensed in Section 11.2.

DB2 for z/OS Administration Guide

  • Reference: SC27-8844
  • Relevance: Covers RUNSTATS options in detail, including FREQVAL, KEYCARD, and HISTOGRAM specifications. Essential for understanding how statistics affect optimizer decisions (Section 11.4, Problem 5: Data Skew).
  • Read this when: You are designing a RUNSTATS strategy or troubleshooting access path changes related to statistics.

DB2 for z/OS Utility Guide and Reference

  • Reference: SC27-8855
  • Relevance: Complete reference for RUNSTATS utility syntax, options, and behavior. Includes the REPORT option for generating human-readable statistics reports.
  • Read this when: You need to understand exactly what RUNSTATS captures and how to specify distribution statistics.

DB2 for z/OS Managing Performance

  • Reference: SC27-4820 (DB2 12 for z/OS)
  • Relevance: Comprehensive performance management guide covering subsystem tuning, application performance, batch optimization, and buffer pool management. Sections on EXPLAIN interpretation are particularly relevant.
  • Read this when: You are designing an end-to-end performance management strategy (Section 11.7).

Books

DB2 Developer's Guide (6th Edition)

  • Author: Craig S. Mullins
  • Publisher: IBM Press / Pearson
  • Relevance: The most comprehensive single reference for DB2 application development and performance. Chapters on EXPLAIN, optimization, and performance monitoring are excellent. Mullins writes from a practitioner's perspective with real-world examples.
  • Read this when: You need a thorough treatment of any DB2 performance topic beyond what this chapter covers.

DB2 for z/OS and OS/390: Ready for Java

  • Authors: Various IBM authors
  • Relevance: While focused on Java, the performance monitoring chapters cover universal DB2 concepts applicable to COBOL. The discussion of dynamic SQL caching and KEEPDYNAMIC is relevant to Section 11.4, Problem 3.

DB2 SQL Tuning Tips for z/OS Developers

  • Author: Tony Andrews
  • Publisher: MC Press
  • Relevance: Practical SQL tuning techniques organized by problem type. The chapter on predicate analysis (which predicates are indexable, which are stage 1 vs. stage 2) directly supports the MATCHCOLS discussion in Section 11.2.
  • Read this when: You are working on specific SQL optimization problems and need a quick-reference guide to predicate coding.

Database Performance Tuning and Optimization: Using Oracle

  • Author: Sara Morrow (older edition); various authors for newer editions
  • Note: While Oracle-specific, the methodology chapters on systematic diagnosis, baseline establishment, and performance budgeting are platform-independent and reinforced in Section 11.1.

IBM Redbooks

DB2 11 for z/OS Performance Topics

  • Reference: SG24-8222
  • Relevance: Covers performance-related features introduced in DB2 11, including enhancements to EXPLAIN, new monitoring capabilities, and improved optimization. The real-world performance comparison data is valuable.

DB2 12 for z/OS Performance Topics

  • Reference: SG24-8404
  • Relevance: The latest performance Redbook covering DB2 12 features. Includes discussion of profile monitoring, plan management, and new EXPLAIN columns. Essential reading if your shop runs DB2 12.

DB2 for z/OS: Diagnosis Guide and Reference

  • Reference: GC27-8847
  • Relevance: The deep technical reference for DB2 internals, including buffer pool management, EDM pool behavior, and latch contention. Required reading for understanding the "unaccounted time" phenomenon described in Case Study 2.

Tools for DB2 Performance Monitoring

  • Reference: SG24-6596 (older but still relevant for architecture)
  • Relevance: Covers the architecture and use of OMEGAMON for DB2 and other monitoring tools. Helps understand what the tools are actually doing under the covers.

Technical Articles and Papers

"Understanding DB2 EXPLAIN" — IBM Developer

  • URL: Available on IBM Developer website (search for "DB2 z/OS EXPLAIN")
  • Relevance: A practical guide to reading EXPLAIN output, with examples of common access path patterns. Good supplementary reference for Section 11.2.

"DB2 Buffer Pool Tuning" — IBM Technical Note

  • Relevance: Detailed guidance on buffer pool sizing, monitoring, and tuning. Covers the relationship between buffer pool size, hit ratio, and I/O wait time discussed in Section 11.5.

"Lock Avoidance in DB2 for z/OS" — IBM Technical Paper

  • Relevance: Explains DB2's lock avoidance techniques and how to design applications that take advantage of them. Directly relevant to Section 11.4, Problem 2.

Online Resources

IBM DB2 for z/OS Knowledge Center

  • URL: ibm.com/docs/en/db2-for-zos
  • Relevance: The official online documentation, updated with each DB2 PTF level. Bookmark the EXPLAIN section and the Accounting Trace field reference.

Planet Mainframe (planetmainframe.com)

  • Relevance: Community site with practitioner articles on DB2 performance, including real-world case studies similar to those in this chapter.

IBM Z and LinuxONE Community (community.ibm.com)

  • Relevance: Active forums where mainframe DBAs and developers discuss performance issues. Useful for researching unusual problems or getting peer review on tuning approaches.

IDUG (International DB2 Users Group)

  • URL: idug.org
  • Relevance: Conference proceedings and technical library contain hundreds of DB2 performance presentations from practitioners. Annual conferences (North America and Europe) feature deep-dive performance sessions. Many presentations are available to members.

Tools Documentation

IBM OMEGAMON for DB2 Performance Expert on z/OS

  • Reference: Product documentation available on IBM Knowledge Center
  • Relevance: Complete reference for OMEGAMON configuration, alert setup, and reporting. Essential if your shop uses OMEGAMON (Section 11.5).

BMC MainView for DB2

  • Reference: BMC product documentation (requires BMC support access)
  • Relevance: Configuration and usage guide for BMC's DB2 monitoring solution. Reference if your shop uses BMC tools.

For a developer new to DB2 performance: 1. Start with this chapter (overview and methodology) 2. Read the IBM EXPLAIN documentation (column definitions) 3. Read Mullins' DB2 Developer's Guide performance chapters (depth) 4. Read the DB2 12 Performance Topics Redbook (current features) 5. Practice with EXPLAIN output from your own shop's programs

For an experienced DBA expanding COBOL-specific knowledge: 1. Review this chapter's Section 11.4 (five common problems in COBOL context) 2. Read Andrews' SQL Tuning Tips (COBOL-specific predicate issues) 3. Review the Lock Avoidance technical paper 4. Study the accounting trace field reference for fields you have not used

For a manager establishing performance governance: 1. Read this chapter's Sections 11.1, 11.6, and 11.7 2. Review Case Study 1 (quarterly review process) 3. Review Case Study 2 (crisis response and after-action) 4. Read the IBM Performance Monitoring and Tuning Guide's management chapters