IBM DB2 9 for z/OS: New Tools for Query Optimization

An IBM Redbooks publication

Published 13 December 2007

cover image

ISBN-10: 0738488615
ISBN-13: 9780738488615
IBM Form #: SG24-7421-00
(458 pages)

More options

Rate and comment

Authors: Paolo Bruni, Terry Berman, John Iczkovits, Basuki Soetarman, Bart Steegmans, Mike Turner


The cost-based optimizer of IBM DB2 for z/OS has continually evolved since its initial inception. Support has included new optimization algorithms, join methods, complex relational data structures, such as star schemas. DB2 for z/OS V8 has provided access path visualization and a Statistics Advisor function through a new Visual Explain.

DB2 9 for z/OS starts addressing the challenge of reducing cost of ownership by extending query optimization through expert-based query and workload analysis, server enhancements, and an Index Advisory function. These functions are delivered with two tools: DB2 Optimization Service Center and DB2 Optimization Expert.

This book helps you understand the installation, customization, and usage aspects of the tools. You are guided through scenarios of gradually increasing complexity where the functions of the tools are exploited for query optimization.

Table of contents

Part 1. Introduction
Chapter 1. Query optimization
Chapter 2. Sample query optimization
Part 2. Installation and customization
Chapter 3. Installing Optimization Service Center
Chapter 4. Installing Optimization Expert
Chapter 5. Tools configuration and customization
Part 3. Critical query detection
Chapter 6. Profile monitoring
Chapter 7. Workloads
Part 4. Solution components
Chapter 8. Query tools
Chapter 9. Statistics Advisor
Chapter 10. Index Advisor
Chapter 11. Access Path Advisor and Query Advisor
Part 5. Tuning scenarios
Chapter 12. Analysis of a problem query
Chapter 13. Analysis of a problem workload
Chapter 14. Common practices
Appendix A. Tips for optimizing very large static workloads
Appendix B. Administrative Scheduler
Appendix C. Relevant DB2 objects

Follow IBM Redbooks

Follow IBM Redbooks