OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4

OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4

by Hernando Bedoya, Jaime Gaug, James Kerl, Ser Ser NG, Jose Cardoso dos Santos
Released March 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 goal of database performance tuning is to minimize the response time of your queries. It is also to optimize your server’s resources by minimizing network traffic, disk I/O, and CPU time.

This IBM IBM Redbooks publication helps you to understand the basics of identifying and tuning the performance of Structured Query Language (SQL) statements using IBM DB2 for i5/OS . DB2 for i5/OS provides a comprehensive set of tools that help technical analysts tune SQL queries. The SQL Performance Monitors are part of the set of tools that IBM i5/OS provides for assisting in SQL performance analysis since Version 3 Release 6. These monitors help to analyze database performance problems after SQL requests are run. In V5R4 of i5/OS iSeries Navigator provides a series of new tools to do SQL Performance analysis that we cover in this book. Among the new tools that we will covering are:
- Capability of visualizing the contents of the SQE Plan Cache
- SQE Plan Cache Snapshots
- The new reporting tool - Dashboard
- OnDemand Index Advisor
- Evaluators such as Index and Materialized Query Tables
This book also presents tips and techniques based on the SQL Performance Monitors and other tools, such as Visual Explain and all the tools provided in V5R4. You’ll find this guidance helpful in gaining the most out of both DB2 for i5/OS and query optimizer when using SQL.

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  3. Part 1: Introduction to DB2 for i5/OS and database performance tools
  4. Chapter 1: Determining whether you have an SQL performance problem
    1. 1.1: Questions to ask yourself
    2. 1.2: How do you know that there is a problem?
    3. 1.3: Where is the problem occurring?
    4. 1.4: Did you ever have satisfactory performance?
    5. 1.5: Do SQL queries appear to have performance problems?
  5. Chapter 2: DB2 for i5/OS performance basics
    1. 2.1: Basics of indexing
      1. Binary radix tree indexes
      2. Encoded-vector index
    2. 2.2: Query engines: an overview
      1. Database architecture before V5R2M0
      2. Current database architecture
      3. Query Dispatcher
      4. Statistics Manager
      5. SQE Optimizer
      6. Data Access Primitives
      7. Access plan
      8. SQL packages
      9. SQE Plan Cache
      10. Open Data Path
  6. Part 2: Gathering, analyzing, and querying database performance data
  7. Chapter 3: Overview of tools to analyze database performance
    1. 3.1: Introduction to the tools
    2. 3.2: SQE Plan Cache
    3. 3.3: SQE Plan Cache Snapshots
    4. 3.4: The Database Performance Monitors
      1. Detailed Monitor (1/2)
      2. Detailed Monitor (2/2)
      3. Summary Monitor or Memory Resident Database Monitor (1/2)
      4. Summary Monitor or Memory Resident Database Monitor (2/2)
      5. Importing a Database Monitor to SQL Performance Monitor
      6. The Monitor Comparison feature of SQL Performance Monitor
    5. 3.5: Visual Explain
    6. 3.6: Index Advisor
    7. 3.7: Evaluators
      1. Index Evaluator
      2. MQT (Materialized Query Tables) Evaluator (1/2)
      3. MQT (Materialized Query Tables) Evaluator (2/2)
    8. 3.8: Current SQL for a Job function
    9. 3.9: Debug messages
    10. 3.10: Print SQL information
  8. Chapter 4: Gathering SQL performance data
    1. 4.1: Types of SQL Performance Monitors
    2. 4.2: Collecting Detailed SQL performance data
      1. Starting a Detailed Database Monitor using the command interface
      2. The SQL Performance Monitor Wizard
      3. Starting a Detailed SQL Performance Monitor
      4. Enabling Database Monitors in ODBC clients
      5. Enabling Database Monitors in OLE DB clients
      6. Enabling Database Monitors in JDBC clients
      7. Enabling Database Monitors in .NET clients
      8. Enabling Database Monitors using an exit program
    3. 4.3: Collecting Summary SQL Performance Monitor data
    4. 4.4: Importing Database Monitors into iSeries Navigator
      1. Importing detailed monitor data
      2. Importing detailed monitor data from a previous release
      3. Importing summary monitor data
    5. 4.5: SQL Performance Monitors properties
      1. Detailed monitor properties
      2. Summary monitor properties
      3. Imported monitor properties
    6. 4.6: Summary or Detailed SQL Performance Monitor
  9. Chapter 5: Analyzing SQL performance data using iSeries Navigator
    1. 5.1: Detailed SQL Performance Monitor Analysis overview
      1. Analysis overview
      2. Amount of work requested
      3. Environmental factors
      4. Implementation information
      5. Types of statements
      6. Miscellaneous information
      7. I/O information
    2. 5.2: In-depth analysis reports
      1. Getting detailed reports from Summary and Statement buttons
      2. Additional information reports (1/2)
      3. Additional information reports (2/2)
      4. Action menu items
    3. 5.3: Summary SQL Performance Monitor analysis overview
      1. SQL performance report information from summary data
      2. Examples and application of Summary SQL Performance Monitor
      3. Limitations of the Summary monitor
    4. 5.4: Show Statements
      1. Filtering options
      2. Launching Visual Explain
    5. 5.5: Compare monitors
    6. 5.6: Case study
      1. A poor performing SQL statement
      2. Why are table scans being done? (1/2)
      3. Why are table scans being done? (2/2)
      4. Why is CQE being used?
      5. Comparison
  10. Chapter 6: Custom Database Monitor Analysis
    1. 6.1: The Database Monitor record types
      1. Database Monitor record types
      2. The 1000 Record: SQL statement summary
      3. The 30XX Records: Query Optimization Row Type
      4. The 3000 Record: Arrival sequence (table scan)
      5. The 3001 Record: using an existing index
      6. The 3002 Record: temporary index created
      7. The 3003 record: query sort
      8. The 3004 record: temporary file
      9. The 3006 record: access plan rebuild
      10. The 3007 record: index evaluation
      11. The 3010 record: host variables
      12. The 3014 record: general query optimization information
      13. The 3015 record: SQE statistics advised
      14. The 3018 record: STRDBMON/ENDDBMON
      15. The 3019 record: rows retrieved detail
      16. The 3020 record: index advised (SQE)
      17. The 3030 record: materialized query table
    2. 6.2: Introduction to query analysis
    3. 6.3: Tips for analyzing the Database Monitor files
      1. Using an SQL ALIAS for the Database Monitor table
      2. Using a subset of the Database Monitor table for faster analysis
      3. Using SQL views for the Database Monitor table
      4. Creating additional indexes over the Database Monitor table
    4. 6.4: Database Monitor query examples
      1. Finding SQL requests that are causing problems
      2. Total time spent in SQL
      3. Individual SQL elapsed time
      4. Analyzing SQL operation types
      5. Full open analysis
      6. Isolation level used
      7. Table scan
      8. Temporary index analysis
      9. Index advised
      10. Access plan rebuilt
      11. Query sorting
      12. SQE advised statistics analysis
      13. Fetched and Retrieved detail rows
      14. Materialized query tables
  11. Chapter 7: SQE Plan Cache and SQE Plan Cache Snapshots
    1. 7.1: SQE Plan Cache and SQE Plan Cache Snapshot
    2. 7.2: SQE Plan Cache
      1. Viewing the properties of the SQE Plan Cache
      2. Viewing the content of the SQE Plan Cache
      3. Using the filter options
      4. Finding and Visual Explaining a query from the SQE Plan Cache
    3. 7.3: SQE Plan Cache Snapshot
      1. Creating an SQE Plan Cache Snapshot using iSeries Navigator
      2. Creating an SQE Plan Cache Snapshot using Stored Procedure
      3. Creating an SQE Plan Cache Snapshot using an Exit Program
      4. Analyzing an SQE Plan Cache Snapshot (1/2)
      5. Analyzing an SQE Plan Cache Snapshot (2/2)
      6. Working with SQL statements from an SQE Plan Cache Snapshot
      7. An example of finding table scans in a SQE Plan Cache Snapshot
      8. Comparing SQE Plan Cache Snapshots (1/2)
      9. Comparing SQE Plan Cache Snapshots (2/2)
  12. Chapter 8: Analyzing database performance data with Visual Explain
    1. 8.1: What is Visual Explain
    2. 8.2: Finding Visual Explain
      1. The SQL Script Center
      2. Explain Only
      3. Run and Explain
      4. Explain While Running
    3. 8.3: Navigating Visual Explain
      1. Toolbar
      2. Menu options
      3. Controlling the diagram level of detail
      4. Displaying the Environment Settings
      5. Visual Explain query attributes and values
    4. 8.4: Using Visual Explain with Database Monitor data
    5. 8.5: Using Visual Explain with imported data
      1. Show Statements
    6. 8.6: Using Visual Explain with SQE Plan Cache and Plan Cache Snaphot
      1. Using Visual Explain with SQE Plan Cache
      2. Using Visual Explain with SQE Plan Cache Snapshot
    7. 8.7: Non-SQL interface considerations
    8. 8.8: The Visual Explain icons (1/2)
    9. 8.8: The Visual Explain icons (2/2)
  13. Chapter 9: Index Advisor
    1. 9.1: What is the Index Advisor
    2. 9.2: System Wide Index Advised Table
    3. 9.3: Levels of Index Advisor access
      1. Index Advisor access at Database level
      2. Index Advisor access at Schema level
      3. Index Advisor access at Table level
    4. 9.4: Index Advisor interface in iSeries Navigator
    5. 9.5: Interfaces to Index Advised information
      1. Access to Index Advised information from Detailed SQL Performance Monitor screen interface
      2. Access to Index Advised information from SQE Plan Cache screen interface
      3. Access to Index Advised information from SQE Plan Cache Snapshot screen interface
      4. Access to Index Advised information from Visual Explain screen interface (1/2)
      5. Access to Index Advised information from Visual Explain screen interface (2/2)
      6. Access to Index Advised information from the Debug messages (1/2)
      7. Access to Index Advised information from the Debug messages (2/2)
    6. 9.6: Temporary Indexes
      1. CQE - Temporary Indexes
      2. SQE - Temporary Indexes
  14. Chapter 10: SQL performance analysis: a methodology
    1. 10.1: Performance methodology
    2. 10.2: Performance troubleshooting
      1. Problem source determination
      2. Performance data capture
      3. Performance analysis process
    3. 10.3: Application of the tools to the methodology
    4. 10.4: Example of using the methodology (1/2)
    5. 10.4: Example of using the methodology (2/2)
  15. Part 3: Additional tips
  16. Chapter 11: Environmental settings that affect SQL performance
    1. 11.1: Introduction
    2. 11.2: Optimization goal
      1. What is the goal?
      2. Setting the Optimization Goal
    3. 11.3: Sensitive Cursors
      1. Performance and query optimization impacts
      2. Cursor sensitivity programming interfaces
    4. 11.4: SMP (Symmetrical multiprocessing) Degree
      1. iSeries i5/OS Architecture
      2. What is SMP?
      3. SMP parallel-enabled functions
      4. Parallel Database Processing
      5. Enabling parallel processing
      6. Feedback
      7. Available and balanced resources
      8. SMP considerations
  17. Chapter 12: Tips to proactively prevent SQL performance problems
    1. 12.1: Indexing strategy
      1. Access methods
      2. Guidelines for perfect indexes
      3. Additional indexing tips
      4. Index Advisor
    2. 12.2: Coding of your SQL statements
      1. Avoid using logical files in your select statements
      2. Avoid using SELECT * in your select statements
      3. Avoid using the relative record number to access your data
      4. Avoid numeric data type conversion
      5. Avoid numeric expressions
      6. Using the LIKE predicate
      7. Avoid scalar functions in the WHERE clause
  18. Chapter 13: Using Collection Services data to identify jobs using system resources
    1. 13.1: Relationship of Collection Services, Database Monitor data and Performance Trace
    2. 13.2: Collection Services and Database Monitor data
      1. Starting Collection Services
      2. From iSeries Navigator
      3. Using Performance Management APIs
      4. V5R4 STRPFRCOL command
    3. 13.3: Using Collection Services data to find jobs using CPU
      1. Finding jobs using CPU with the Component Report
      2. Finding jobs using CPU with iSeries Navigator Graph History
      3. Finding jobs using CPU with Management Central System Monitors
    4. 13.4: Using Collection Services data to find jobs with high disk I/O counts
    5. 13.5: Using Performance Data of the Database Monitor to find the query that needs optimization (1/2)
    6. 13.5: Using Performance Data of the Database Monitor to find the query that needs optimization (2/2)
    7. 13.6: Using Performance Trace to find object locks
  19. Appendix A: Tools to check a performance problem
    1. WRKACTJOB command
    2. WRKSYSACT command
      1. WRKSYSSTS command
      2. WRKOBJLCK command
      3. WRKJOB command
      4. iDoctor for iSeries Job Watcher
  20. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  21. Index (1/2)
  22. Index (2/2)
  23. Back cover

Product information

  • Title: OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4
  • Author(s): Hernando Bedoya, Jaime Gaug, James Kerl, Ser Ser NG, Jose Cardoso dos Santos
  • Release date: March 2007
  • Publisher(s): IBM Redbooks
  • ISBN: None