Chapter 15: Further Reading and Resources

This reading list supports and extends the material in Chapter 15. Resources are organized into two tiers based on verifiability, followed by categorized recommendations for deeper exploration.

Tier 1: Verified and Authoritative Sources

These are primary sources, established reference works, and resources from authoritative publishers. They can be independently verified and are widely cited in the database community.

Foundational Papers and Theory

  • Bayer, R. and McCreight, E. "Organization and Maintenance of Large Ordered Indexes." Acta Informatica, Vol. 1, No. 3, 1972, pp. 173-189.

  • The original paper introducing B-trees (the predecessor to B+ trees). This is where it all began. The paper describes the structure, insertion, deletion, and splitting algorithms that remain the foundation of every B+ tree implementation in modern databases, including DB2.

  • Comer, Douglas. "The Ubiquitous B-Tree." ACM Computing Surveys, Vol. 11, No. 2, June 1979, pp. 121-137.

  • An excellent survey paper that explains B-tree variants (including B+ trees) in accessible terms. Highly recommended for readers who want to understand the theoretical foundation without reading the dense original paper.

  • Graefe, Goetz. "Modern B-Tree Techniques." Foundations and Trends in Databases, Vol. 3, No. 4, 2011, pp. 203-402.

  • A comprehensive modern survey covering B-tree implementation techniques in contemporary database systems, including concurrency control, buffer management, and write-optimized variants. Advanced reading for those interested in how DB2 and other systems implement B+ trees at the engineering level.

  • Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., and Price, T.G. "Access Path Selection in a Relational Database Management System." Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data, 1979, pp. 23-34.

  • Referenced in Chapter 3 and again here because understanding how the optimizer selects access paths is inseparable from understanding how to design indexes. The optimizer's cost model — which weighs index access against tablespace scans, considers cluster ratio, and estimates I/O — is the reason your index design decisions matter.

IBM Official Documentation

IBM's documentation is the authoritative source for DB2 index-specific details. Search the IBM Documentation portal (ibm.com/docs) for the following topics:

  • DB2 for z/OS: Indexes

  • In the Db2 12 for z/OS documentation, search for "Creating indexes" and "Index design guidelines." The administration guide covers clustering indexes, partitioned indexes (DPSI/NPSI), index compression, and PCTFREE settings. The SQL reference provides the complete CREATE INDEX syntax.

  • DB2 for LUW: Indexes

  • In the Db2 11.5 documentation, search for "Index design guidelines" and "CREATE INDEX statement." The administration guide covers index types (regular, expression-based, spatial, XML path), the INCLUDE clause, MDC, and index compression.

  • DB2 for z/OS: EXPLAIN

  • Search for "Interpreting EXPLAIN output" in the Db2 for z/OS performance documentation. Understanding EXPLAIN is essential for verifying that your indexes are being used as intended.

  • DB2 for LUW: EXPLAIN facility

  • Search for "db2expln" and "db2exfmt" in the Db2 11.5 documentation. These tools format access plan output that shows whether index-only access, matching scans, and other index-related optimizations are being applied.

IBM Redbooks

  • "DB2 12 for z/OS Technical Overview" (SG24-8482 or later)

  • Contains chapters on index management and performance tuning that cover z/OS-specific topics including DPSI vs. NPSI decisions, partition independence, and utility considerations.

  • "DB2 for z/OS: Optimizer and Access Path Selection" (various editions)

  • Search IBM Redbooks for the most recent publication on DB2 z/OS access path selection. These books provide detailed explanations of how the optimizer evaluates indexes, cluster ratios, and filter factors.

  • "Db2 11.1 Certification Study Guide" (SG24-8414 or later)

  • The certification study guides contain structured coverage of index design topics, including practice questions. Useful for self-assessment.

Textbooks

  • Mullins, Craig S. DB2 Developer's Guide. (Multiple editions, IBM Press/Pearson.)

  • The most comprehensive single-volume DB2 reference. The chapters on index design, access path selection, and performance tuning are particularly relevant to the material in Chapter 15. Mullins provides practical guidance grounded in decades of real-world DB2 experience.

  • Lightstone, Sam, Teorey, Toby, and Nadeau, Tom. Physical Database Design: The Database Professional's Guide to Exploiting Indexes, Views, Storage, and More. (Morgan Kaufmann, 2007.)

  • Not DB2-specific, but one of the best books on physical database design, including index design methodology. The treatment of index selection, covering indexes, and index maintenance is rigorous and practical.

  • Date, C.J. An Introduction to Database Systems. (Addison-Wesley/Pearson, 8th edition.)

  • Provides the theoretical foundation for indexing as a physical implementation concept. Date's treatment of physical data independence — the principle that physical storage decisions (including indexes) should not affect logical data access — provides the intellectual framework for understanding why index design matters.

Tier 2: Attributed Sources and Community Resources

These resources are authored by recognized experts or established community voices. They have not undergone formal peer review but represent practical knowledge from the DB2 community.

Practitioner References

  • Kline, Kevin and Kline, Daniel. SQL in a Nutshell. (O'Reilly Media, various editions.)

  • A multi-platform SQL reference that covers index-related SQL syntax across DB2, Oracle, SQL Server, and PostgreSQL. Useful for practitioners who work across multiple database platforms and need to understand platform-specific indexing syntax.

  • Egan, Rick. Various IDUG presentations on DB2 for z/OS index design.

  • Rick Egan has presented extensively at IDUG conferences on z/OS index design topics, including DPSI vs. NPSI selection, index compression, and performance tuning. IDUG members can access presentation archives at idug.org.

Blogs and Community Sites

  • Craig Mullins' Blog (craigsmullins.com)

  • Regular posts on DB2 indexing best practices, performance tuning, and index-related changes in new DB2 versions.

  • IDUG (International DB2 Users Group) (idug.org)

  • IDUG conference presentations frequently cover index design case studies. The "Ask the Experts" forum is a valuable resource for specific index design questions.

  • IBM Data and AI Community

  • IBM's community forums for Db2 users include index-specific discussion threads and technical articles from IBM developers. Search for "Db2 index design" or "Db2 index-only access" to find relevant content.

Performance Monitoring Tools

Several third-party tools provide index analysis capabilities beyond DB2's built-in monitoring:

  • IBM Data Server Manager: Provides visual access plan analysis and index recommendations for DB2 LUW.
  • BMC AMI for DB2: Comprehensive z/OS DB2 performance monitoring, including index usage analysis and recommendations.
  • Broadcom Detector for DB2: z/OS performance monitoring with index hit analysis and unused index identification.
  • IBM OMEGAMON for DB2: Real-time performance monitoring with index-level statistics and alerting.

For a reader working through this book sequentially:

  1. Immediately: Read the Comer survey paper ("The Ubiquitous B-Tree") if you want a deeper understanding of B+ tree theory. It is accessible and well-written.

  2. Before Chapter 16: Read the relevant sections of the IBM documentation on EXPLAIN output for your platform (z/OS or LUW). You will need to interpret access plans in the next chapter, and understanding index-related EXPLAIN columns (MATCHCOLS, INDEXONLY, ACCESSTYPE) is essential.

  3. Alongside Chapters 16-18: Read the relevant chapters of Mullins' DB2 Developer's Guide on access path selection and performance tuning. The synergy between index design (this chapter) and query optimization (next chapter) is best appreciated by reading both perspectives.

  4. For advanced study: Read Graefe's "Modern B-Tree Techniques" for a deep dive into implementation-level details. This is graduate-level material but provides insight into why DB2 makes certain implementation choices.

A Note on Version Specificity

Index features and behaviors evolve across DB2 versions. Key version-specific considerations:

  • DB2 12 for z/OS introduced enhancements to index key compression and utilities that affect index maintenance windows.
  • DB2 13 for z/OS introduced further improvements to index management and autonomic statistics.
  • Db2 11.5 for LUW is the current long-term support release and includes all index features discussed in this chapter, including expression-based indexes, INCLUDE columns, and MDC.

When consulting IBM documentation, ensure you are reading the documentation for the version deployed in your environment. Features available in one version may not exist in earlier versions.

Return to Chapter 15 | Continue to Code Examples