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

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

by Paolo Bruni, Terry Berman, John Iczkovits, Basuki Soetarman, Bart Steegmans, Mike Turner
Released December 2007
Publisher(s): IBM Redbooks
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

  1. Figures (1/2)
  2. Figures (2/2)
  3. Tables
  4. Examples
  5. Notices
    1. Trademarks
  6. Preface
    1. The team that wrote this book
    2. Become a published author
    3. Comments welcome
  7. Part 1: Introduction
  8. Chapter 1: Query optimization
    1. 1.1: Autonomic computing
      1. DB2 and autonomic computing
      2. Total cost of ownership
      3. Quality of service
      4. Optimization Service Center or Optimization Expert
      5. Customer adaptation
      6. Extensible architecture
      7. Customer value
      8. Conclusion
    2. 1.2: The delivery of query optimization
      1. Optimization Service Center (1/4)
      2. Optimization Service Center (2/4)
      3. Optimization Service Center (3/4)
      4. Optimization Service Center (4/4)
      5. Optimization Expert
      6. Product functions and DB2 support
  9. Chapter 2: Sample query optimization
    1. 2.1: Starting an Optimization Service Center session
    2. 2.2: Creating a project
    3. 2.3: Investigating a query by typing the text (1/4)
    4. 2.3: Investigating a query by typing the text (2/4)
    5. 2.3: Investigating a query by typing the text (3/4)
    6. 2.3: Investigating a query by typing the text (4/4)
    7. 2.4: Investigating a query from a package (1/2)
    8. 2.4: Investigating a query from a package (2/2)
    9. 2.5: Investigating a query from the dynamic statement cache
    10. 2.6: Using the Statistics Advisor (1/2)
    11. 2.6: Using the Statistics Advisor (2/2)
  10. Part 2: Installation and customization
  11. Chapter 3: Installing Optimization Service Center
    1. 3.1: Data server and DB2 requirements
    2. 3.2: Workstation requirements
    3. 3.3: Installing Optimization Service Center on a workstation
      1. Installing Optimization Service Center from the product CD
      2. Installing Optimization Service Center from SMP/E
      3. Installing Optimization Service Center from the Web download
    4. 3.4: Step-by-step install instructions
    5. 3.5: Performing a silent install
  12. Chapter 4: Installing Optimization Expert
    1. 4.1: Data server and DB2 requirements
    2. 4.2: Workstation requirements
    3. 4.3: Installing Optimization Expert on a workstation
      1. Installing Optimization Expert from the product CD
      2. Installing Optimization Expert from SMP/E
      3. Purchasing and downloading DB2 Optimization Expert from the ShopzSeries Web page
    4. 4.4: Step-by-step install instructions
    5. 4.5: Performing a silent install
  13. Chapter 5: Tools configuration and customization
    1. 5.1: Workstation configuration
      1. Identify a DB2 system
      2. Connect to a DB2 system
      3. Bind packages on the DB2 system (optional)
      4. Enable the EXPLAIN function (1/2)
      5. Enable the EXPLAIN function (2/2)
      6. Create user tables for workload information
    2. 5.2: Security considerations
  14. Part 3: Critical query detection
  15. Chapter 6: Profile monitoring
    1. 6.1: Profile monitor capabilities
      1. Monitor profiles
      2. Monitor pushout
      3. Consolidation
      4. Scheduling: Client-side or administrative (server-side)
      5. Compared to other monitoring solutions
      6. Data sharing limitation
      7. Restarting monitoring after a DB2 restart
    2. 6.2: Operating the profile monitor
      1. Viewing monitors
      2. Displaying monitoring options currently in effect
      3. Setting up a new profile monitor
      4. Starting all monitor profiles
      5. Monitor results
      6. Monitoring workload projects
      7. Why are you not seeing all the results?
      8. Important considerations
    3. 6.3: Profile monitor DB2 tables
      1. Profile monitor definitions
      2. DB2 tables containing monitoring results
  16. Chapter 7: Workloads
    1. 7.1: Defining a new workload
      1. Workload sources
      2. Tasks
      3. Workloads and workload projects
      4. Defining a new workload
      5. Gather explain information
    2. 7.2: Working with workloads
      1. Workload list
      2. Workloads - operations
      3. Workload project
      4. Workload statements
      5. Workload schedule tasks and history
  17. Part 4: Solution components
  18. Chapter 8: Query tools
    1. 8.1: Providing to a query to analyze
      1. Selecting a query from a workload
      2. Selecting a query using a query project
      3. Context setting
      4. Explaining an SQL statement
    2. 8.2: Access Plan Graph (1/2)
    3. 8.2: Access Plan Graph (2/2)
    4. 8.3: Query Annotation tool
      1. Annotation (1/2)
      2. Annotation (2/2)
    5. 8.4: Query reports
      1. Table report
      2. Index reports
      3. Predicate reports
    6. 8.5: Gather service information
      1. Specifying SQL statement and tables to be used
      2. Specifying which data to generate
      3. Sending Service SQL information to IBM
    7. 8.6: Visual Plan Hint
      1. Introducing optimization hints
      2. Introducing Visual Plan Hint
      3. Invoking Visual Plan Hint
      4. Visual Plan Hint join graph
      5. Visual Plan Hint general toolbar
      6. VPH hint area
      7. Implementing an optimization hint using Visual Plan Hint
      8. Validating the hint
      9. Refining the hint
      10. Implementing the hint
      11. Visual Plan Hint graph reports
  19. Chapter 9: Statistics Advisor
    1. 9.1: Access Path statistics introduction
      1. BASE Statistics
      2. COLUMN statistics
    2. 9.2: Statistics Advisor analysis
    3. 9.3: Invoking Statistics Advisor
      1. EXPLAIN as a pre-requisite
      2. Statistics Advisor options
      3. Statistics Advisor output panel
    4. 9.4: Sample Statistics Advisor scenarios
      1. Conversion to DB2 V9
      2. Typical RUNSTATS - is it sufficient?
      3. Checking for consistent statistics
      4. Workload Statistics Advisor sample
      5. Statistics first-cut recommendations for very large workloads
  20. Chapter 10: Index Advisor
    1. 10.1: Index Advisor overview
    2. 10.2: Query Index Advisor
      1. Running Query Index Advisor
      2. Query Index Advisor recommendations
      3. Acting on the recommendations
      4. Setting Query Index Advisor options for the current project
      5. Setting Query Index Advisor option defaults for new projects
    3. 10.3: Workload Index Advisor
      1. Before running Workload Index Advisor
      2. Running Workload Index Advisor (1/2)
      3. Running Workload Index Advisor (2/2)
      4. Workload Index Advisor recommendations
      5. What-If Analysis
      6. Acting on the recommendations
      7. Setting Workload Index Advisor options for the current project (1/2)
      8. Setting Workload Index Advisor options for the current project (2/2)
      9. Setting Workload Index Advisor option defaults for new projects
  21. Chapter 11: Access Path Advisor and Query Advisor
    1. 11.1: Creating a workload from a file (1/2)
    2. 11.1: Creating a workload from a file (2/2)
    3. 11.2: Access Path Advisor
      1. Running the Access Path Advisor
      2. Access Path Advisor recommendations
      3. Acting on the recommendations
      4. Setting Access Path Advisor options
    4. 11.3: Query Advisor
      1. Running the Query Advisor
      2. Query Advisor recommendations
      3. Acting on the recommendations
      4. Setting Query Advisor options
    5. 11.4: Workload Query Advisor
      1. Running the Workload Query Advisor
      2. Workload Query Advisor recommendations (1/2)
      3. Workload Query Advisor recommendations (2/2)
      4. Acting on the recommendations
      5. Setting Workload Query Advisor options
  22. Part 5: Tuning scenarios
  23. Chapter 12: Analysis of a problem query
    1. 12.1: Not optimal access path because of data correlation
      1. The query
      2. The current access path
      3. Comparing optimizer estimate to real number
      4. Query breakdown (1/2)
      5. Query breakdown (2/2)
      6. Detecting column correlation
      7. Collecting correlation statistics
      8. Enhanced access path after correlation statistics
      9. Data access cost
      10. Conclusion
  24. Chapter 13: Analysis of a problem workload
    1. 13.1: The selected workload
    2. 13.2: Connect to Optimization Service Center or Optimization Expert
    3. 13.3: Defining a monitor profile
    4. 13.4: Activating monitoring and measuring the initial run
      1. Starting the monitor profile
      2. Executing the workload that you want to capture
      3. Obtaining monitoring profile data
    5. 13.5: Analyzing the results
    6. 13.6: Running Workload Statistics Advisor (1/2)
    7. 13.6: Running Workload Statistics Advisor (2/2)
    8. 13.7: Implementing the Workload Statistics Advisor recommendations
    9. 13.8: Re-measuring the workload after Workload Statistics Advisor recommendations
      1. Using Workload Index Advisor (1/2)
      2. Using Workload Index Advisor (2/2)
    10. 13.9: Implementing the indexes suggested by Workload Index Advisor
      1. Creating the indexes
      2. Gathering statistics for the newly created indexes
    11. 13.10: Re-measuring the workload after Workload Index Advisor recommendations
    12. 13.11: Comparing all three workload runs
    13. 13.12: Conclusion
  25. Chapter 14: Common practices
    1. 14.1: Using Optimization Service Center or Optimization Expert in application’s life cycle
      1. Usage during application design and development
      2. Usage during testing or quality assurance
      3. Usage during production phase
    2. 14.2: Common practice for tuning steps
      1. Tuning a single query
      2. Tuning a query workload (1/2)
      3. Tuning a query workload (2/2)
  26. Part 6: Appendixes
  27. Appendix A: Tips for optimizing very large static workloads
    1. A.1: Report all column expressions (index candidates)
      1. Mass-explain procedure
      2. Reporting column expressions
    2. A.2: Automated RUNSTATS column lists
      1. Mass-explain procedure
      2. Columns in predicates (need COLUMN statistics)
      3. Columns and literal values (need FREQVAL)
  28. Appendix B: Administrative Scheduler
    1. B.1: Purpose of the Administrative Scheduler
    2. B.2: Setup of the Administrative Scheduler
    3. B.3: Security for the Administrative Scheduler
    4. B.4: Sample SELECT to display the task list
    5. B.5: Additional information
  29. Appendix C: Relevant DB2 objects
    1. C.1: Security requirements for Optimization Service Center or Optimization Expert
    2. C.2: Job DSNTIJOS
    3. C.3: Job AOCDDL
    4. C.4: Packages required by Optimization Service Center
    5. C.5: Additional packages required by Optimization Expert
  30. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks publications
    5. Help from IBM
  31. Index (1/2)
  32. Index (2/2)
  33. Back cover

Product information

  • Title: IBM DB2 9 for z/OS: New Tools for Query Optimization
  • Author(s): Paolo Bruni, Terry Berman, John Iczkovits, Basuki Soetarman, Bart Steegmans, Mike Turner
  • Release date: December 2007
  • Publisher(s): IBM Redbooks
  • ISBN: None