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.

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