DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition

DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition

by Whei-Jen Chen, Ute Baumbach, Marcia Miskimen, Werner Wicke
Released March 2006
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

DB2 Performance Expert (PE) for Multiplatforms V2.2 is a workstation-based performance analysis and tuning tool for managing a heterogeneous mix of DB2 systems with a single end-user interface. DB2 PE simplifies DB2 performance management by providing you with the ability to monitor applications, system statistics, and system parameters using a single tool.

This IBM Redbooks publication provides an overview of the architecture of DB2 Performance Expert. We highlight key considerations in planning DB2 PE V2.2 for your environment and provide a step-by-step installation and configuration guide

We discuss, in detail, the DB2 PE V2.2 functions and features. Recommendations and tips for DB2 performance tuning are also introduced.

Finally, we discuss some of the commonly encountered problems faced by a DBA when managing a DB2 environment, and describe how the tool can be used to diagnose and resolve these performance problems.

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this redbook
    2. Acknowledgements
    3. Become a published author
    4. Comments welcome
  3. Summary of changes
    1. March 2006, Second Edition
  4. Chapter 1: Introduction
    1. Performance Expert overview
      1. Performance monitor
    2. PE architecture
    3. Ordering information
  5. Chapter 2: Planning and installation
    1. Planning
      1. Information gathering
      2. Topology
    2. Installation
      1. Lab environment
      2. Installing Performance Expert Server on AIX (1/2)
      3. Installing Performance Expert Server on AIX (2/2)
      4. Installing Performance Expert Server on Windows
      5. Installing Performance Expert Client on Windows
      6. Installing Performance Expert Server V2.2 Fix Pack on AIX
      7. Installing Performance Expert Server V2.2 Fix Pack on Windows
      8. Installing Performance Expert Client V2.2 Fix Pack on Windows
      9. Migrating Performance Expert Client from Version 2.1 to Version 2.2 on Windows
      10. Migrating Performance Expert Server from Version 2.1 to Version 2.2 on Windows
      11. Migrating Performance Expert Server from Version 2.1 to Version 2.2 on AIX
      12. Enabling CIM
  6. Chapter 3: Configuration and verification
    1. Configuration
      1. PE Server configuration and related tasks (1/2)
      2. PE Server configuration and related tasks (2/2)
      3. Worksheet
      4. Configuring the CIM server
      5. Server configuration examples (1/4)
      6. Server configuration examples (2/4)
      7. Server configuration examples (3/4)
      8. Server configuration examples (4/4)
      9. New PE server configuration features of V2.2 Fix Pack 1 (1/2)
      10. New PE server configuration features of V2.2 Fix Pack 1 (2/2)
      11. Client setup (1/5)
      12. Client setup (2/5)
      13. Client setup (3/5)
      14. Client setup (4/5)
      15. Client setup (5/5)
      16. Getting started (1/2)
      17. Getting started (2/2)
    2. Installation verification and support
      1. What log files does PE generate?
      2. Interpreting and using the log files (1/2)
      3. Interpreting and using the log files (2/2)
      4. Troubleshooting the PE Server
      5. Troubleshooting the PE Client
  7. Chapter 4: Features and functions - online and short-term monitoring
    1. Online monitoring
      1. Application details (1/2)
      2. Application details (2/2)
      3. SQL activity tracing (1/2)
      4. SQL activity tracing (2/2)
      5. Statistics Details (1/2)
      6. Statistics Details (2/2)
      7. System Health (1/2)
      8. System Health (2/2)
      9. Locks monitoring
      10. System parameters
      11. Operating System monitoring (1/2)
      12. Operating System monitoring (2/2)
    2. Alerts and exceptions
      1. Periodic exception processing (1/8)
      2. Periodic exception processing (2/8)
      3. Periodic exception processing (3/8)
      4. Periodic exception processing (4/8)
      5. Periodic exception processing (5/8)
      6. Periodic exception processing (6/8)
      7. Periodic exception processing (7/8)
      8. Periodic exception processing (8/8)
      9. Deadlock event exceptions (1/2)
      10. Deadlock event exceptions (2/2)
  8. Chapter 5: Features and functions - long-term monitoring
    1. Performance Warehouse
      1. Background of Performance Warehouse
      2. Life cycle of performance data (1/2)
      3. Life cycle of performance data (2/2)
      4. Using data from the Performance Warehouse (PWH) (1/2)
      5. Using data from the Performance Warehouse (PWH) (2/2)
      6. Performance Warehouse processes (1/4)
      7. Performance Warehouse processes (2/4)
      8. Performance Warehouse processes (3/4)
      9. Performance Warehouse processes (4/4)
      10. Rule of Thumb analysis (1/2)
      11. Rule of Thumb analysis (2/2)
      12. Queries (1/3)
      13. Queries (2/3)
      14. Queries (3/3)
    2. Buffer Pool Analysis (1/2)
    3. Buffer Pool Analysis (2/2)
      1. Generating Buffer Pool Analysis reports (1/2)
      2. Generating Buffer Pool Analysis reports (2/2)
      3. Buffer Pool Analysis Text Report
      4. Buffer Pool Analysis Interactive Report
      5. Performance Warehouse for buffer pool analysis (1/3)
      6. Performance Warehouse for buffer pool analysis (2/3)
      7. Performance Warehouse for buffer pool analysis (3/3)
  9. Chapter 6: Scenarios
    1. Trend analysis
      1. Buffer pool utilization
      2. Scenario: Connection and workload management (1/4)
      3. Scenario: Connection and workload management (2/4)
      4. Scenario: Connection and workload management (3/4)
      5. Scenario: Connection and workload management (4/4)
      6. Evaluating effects of a configuration change (1/2)
      7. Evaluating effects of a configuration change (2/2)
    2. Problem reported
      1. Identify and fix slow SQL statements (1/8)
      2. Identify and fix slow SQL statements (2/8)
      3. Identify and fix slow SQL statements (3/8)
      4. Identify and fix slow SQL statements (4/8)
      5. Identify and fix slow SQL statements (5/8)
      6. Identify and fix slow SQL statements (6/8)
      7. Identify and fix slow SQL statements (7/8)
      8. Identify and fix slow SQL statements (8/8)
      9. Responding to Performance Expert alerts (1/2)
      10. Responding to Performance Expert alerts (2/2)
      11. Another exception scenario (1/2)
      12. Another exception scenario (2/2)
      13. Transaction log full (1/4)
      14. Transaction log full (2/4)
      15. Transaction log full (3/4)
      16. Transaction log full (4/4)
    3. Tuning DB2 for OLTP application with PE
      1. Scenario description
      2. DB2 UDB configuration tuning
  10. Appendix A: Rules of Thumb definition samples
    1. Buffer-pool cluster definitions
    2. Database cluster definitions
    3. SQL activity cluster definitions
    4. Tablespace cluster definitions
  11. Appendix B: Monitoring CM databases with Performance Expert
    1. Introduction to CM database monitoring
      1. Understanding the CM and Performance Expert environments
      2. Out of the box CM database tuning
      3. Monitoring CM databases with Performance Expert
    2. CM DB2 Database performance monitoring using Performance Expert
      1. Monitoring the health of the CM DB2 databases
      2. Monitoring the health using System Health graphs
      3. Monitoring the health using exception processing
      4. Monitoring the connections and agents of your CM library server (1/2)
      5. Monitoring the connections and agents of your CM library server (2/2)
    3. Analyzing deadlocks and locking conflicts
      1. Analyzing deadlocks
      2. Analyzing locking conflicts
      3. Detecting the need for running reorg and runstats
      4. Monitoring buffer pool effectiveness
      5. Discovering peak and normal workload times and monitoring the performance in these times
      6. Monitoring stored procedure call throughput
      7. Identifying long-running SQL statements
      8. Detecting heavy hitter tables
      9. Monitoring tablespaces and file systems
      10. Monitoring the operating system
  12. Appendix C: DB2 Performance Expert For Business Intelligence
    1. Introduction
    2. DB2 PE features for BI
      1. DPF monitoring and skew detection
      2. Engine monitoring (1/2)
      3. Engine monitoring (2/2)
      4. Application monitoring and tracing
      5. History analysis
      6. Performance Warehouse analysis
      7. Operating system monitoring
      8. Visualization
      9. Threshold exceptions/predefined threshold set
    3. BI Performance Tuning scenarios with DB2 PE
      1. Check if a system is CPU bound
      2. Sort and hash join tuning (1/2)
      3. Sort and hash join tuning (2/2)
      4. Page I/O tuning
      5. Detecting skews (1/2)
      6. Detecting skews (2/2)
      7. Understanding long-running queries (1/3)
      8. Understanding long-running queries (2/3)
      9. Understanding long-running queries (3/3)
      10. SQL tracing
      11. Monitoring and tuning load (1/2)
      12. Monitoring and tuning load (2/2)
      13. Parameter marker check
      14. Verifying MQT effectiveness
      15. FCM tuning
      16. Dashboard monitoring of key BI performance indicators
    4. Conclusion
  13. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  14. Index (1/3)
  15. Index (2/3)
  16. Index (3/3)
  17. Back cover

Product information

  • Title: DB2 Performance Expert for Multiplatforms V2.2, 2nd Edition
  • Author(s): Whei-Jen Chen, Ute Baumbach, Marcia Miskimen, Werner Wicke
  • Release date: March 2006
  • Publisher(s): IBM Redbooks
  • ISBN: None