DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI

DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI

by Nagraj Alur, Peter Farrell, Philip Gunning, Saeid Mohseni, Swaminaathan Rajagopalan
Released April 2004
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

This IBM Redbooks publication provides detailed information on implementing high performance OLTP and BI applications in DB2 UDB ESE V8 environments involving AIX and Windows 2000 platforms. It is aimed at a target audience of DB2 application developers and database administrators (DBAs).

We provide an overview of the architecture of a DB2 UDB V8 non-DPF environment from a performance perspective, and describe key performance drivers in OLTP, BI, and mixed workload environments.

This book’s primary focus is a single partition (non-DPF) environment, and we provide best practices to achieve optimal application and system performance in OLTP, BI, and mixed workload environments.

Finally, we discuss some of the commonly encountered problems faced by a DBA when managing a DB2 environment, and describe techniques for problem diagnosis using typical problem scenarios.

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 redbook
    2. Become a published author
    3. Comments welcome
  7. Chapter 1: Introduction to performance management
    1. Introduction
    2. Performance management
    3. Types of monitoring
      1. Routine monitoring
      2. Online/realtime event monitoring
      3. Exception monitoring
    4. Problem determination methodology
  8. Chapter 2: DB2 UDB architecture overview
    1. Introduction
    2. Main components of DB2
      1. High level overview of main DB2 components
      2. High level overview of DB2 architecture and processes (1/2)
      3. High level overview of DB2 architecture and processes (2/2)
      4. Process model
      5. Memory model (1/3)
      6. Memory model (2/3)
      7. Memory model (3/3)
    3. Single user transaction/query flow
      1. Transaction flow with connection concentrator enabled
      2. Transaction flow with connection concentrator disabled
    4. Multi-user (concurrent) transaction/query flow
    5. Key performance knobs
      1. Configuration Advisor and AUTOCONFIGURE
      2. Database Manager (DBM) configuration parameters
      3. Database (DB) configuration parameters
      4. DB2 registry and environment variables
    6. Performance monitoring facilities
      1. CLI/ODBC/JDBC trace
      2. Database System Monitor (1/3)
      3. Database System Monitor (2/3)
      4. Database System Monitor (3/3)
      5. DB2 administration notification log
      6. db2batch
      7. db2diag.log
      8. DB2 Performance Expert
      9. Design Advisor
      10. Explain and Visual Explain (1/2)
      11. Explain and Visual Explain (2/2)
      12. Heath Monitor and Health Center
      13. Memory Tracker
      14. Memory Visualizer
  9. Chapter 3: Application design and system performance considerations
    1. OLTP and BI characteristics
      1. OLTP characteristics
      2. BI characteristics
    2. Key performance drivers
    3. Application design considerations
      1. Table design
      2. MDC design considerations (1/2)
      3. MDC design considerations (2/2)
      4. MQT/AST design considerations (1/2)
      5. MQT/AST design considerations (2/2)
      6. Index design
      7. Table space design (1/3)
      8. Table space design (2/3)
      9. Table space design (3/3)
      10. Writing efficient SQL (1/7)
      11. Writing efficient SQL (2/7)
      12. Writing efficient SQL (3/7)
      13. Writing efficient SQL (4/7)
      14. Writing efficient SQL (5/7)
      15. Writing efficient SQL (6/7)
      16. Writing efficient SQL (7/7)
      17. Concurrency (1/3)
      18. Concurrency (2/3)
      19. Concurrency (3/3)
    4. System environment considerations
      1. I/O placement considerations
      2. Log considerations (1/4)
      3. Log considerations (2/4)
      4. Log considerations (3/4)
      5. Log considerations (4/4)
      6. Monitor switch settings
      7. Connection considerations (1/3)
      8. Connection considerations (2/3)
      9. Connection considerations (3/3)
      10. Buffer pool considerations (1/5)
      11. Buffer pool considerations (2/5)
      12. Buffer pool considerations (3/5)
      13. Buffer pool considerations (4/5)
      14. Buffer pool considerations (5/5)
      15. Locking considerations (1/2)
      16. Locking considerations (2/2)
      17. Package cache considerations
      18. Catalog cache considerations
      19. Sort considerations (1/2)
      20. Sort considerations (2/2)
      21. Other memory considerations (1/3)
      22. Other memory considerations (2/3)
      23. Other memory considerations (3/3)
      24. Miscellaneous considerations (1/2)
      25. Miscellaneous considerations (2/2)
  10. Chapter 4: Command and utility considerations
    1. Introduction
    2. Backup
      1. Brief description
      2. Performance considerations
      3. Best practices
    3. Export
      1. Brief description
      2. Performance considerations
      3. Best practices
    4. Import
      1. Brief description
      2. Performance considerations
      3. Best practices
    5. Load
      1. Brief description
      2. Performance considerations
      3. Best practices (1/2)
      4. Best practices (2/2)
    6. Reorg
      1. Brief description
      2. Performance considerations
      3. Best practices
    7. Restore
      1. Brief description
      2. Performance considerations
      3. Best practices
    8. Runstats
      1. Brief description
      2. Performance considerations
      3. Best practices
  11. Chapter 5: Operating system considerations
    1. Introduction
    2. AIX platform
      1. Operating system considerations
      2. Memory considerations (1/3)
      3. Memory considerations (2/3)
      4. Memory considerations (3/3)
      5. Disk and filesystem considerations (1/4)
      6. Disk and filesystem considerations (2/4)
      7. Disk and filesystem considerations (3/4)
      8. Disk and filesystem considerations (4/4)
      9. Monitoring and problem determination tools (1/4)
      10. Monitoring and problem determination tools (2/4)
      11. Monitoring and problem determination tools (3/4)
      12. Monitoring and problem determination tools (4/4)
    3. Windows platform
      1. Operating system considerations
      2. Memory considerations
      3. Disk and filesystem considerations
      4. Monitoring and problem determination tools (1/2)
      5. Monitoring and problem determination tools (2/2)
  12. Chapter 6: Problem determination scenarios
    1. Introduction
    2. DB2 hypotheses hierarchy
      1. DB2 database server system resource constraints
      2. DB2 system resource constraints
    3. Exception event scenarios
      1. Lock waits due to default LOCKTIMEOUT value (OLTP) (1/3)
      2. Lock waits due to default LOCKTIMEOUT value (OLTP) (2/3)
      3. Lock waits due to default LOCKTIMEOUT value (OLTP) (3/3)
      4. Poor SQL performance due to missing indexes (OLTP) (1/3)
      5. Poor SQL performance due to missing indexes (OLTP) (2/3)
      6. Poor SQL performance due to missing indexes (OLTP) (3/3)
      7. Poor SQL performance due to unused MQTs (BI) (1/3)
      8. Poor SQL performance due to unused MQTs (BI) (2/3)
      9. Poor SQL performance due to unused MQTs (BI) (3/3)
    4. Routine monitoring scenarios
      1. Deteriorating space utilization conditions (BI) (1/2)
      2. Deteriorating space utilization conditions (BI) (2/2)
      3. Deteriorating buffer pool hit ratios (OLTP) (1/2)
      4. Deteriorating buffer pool hit ratios (OLTP) (2/2)
    5. Online/Realtime monitoring scenarios
      1. Lock contention (OLTP) (1/2)
      2. Lock contention (OLTP) (2/2)
  13. Appendix A: DB2 UDB ESE Version 8 performance enhancements
    1. Introduction
    2. Application-related performance enhancements
      1. Multidimensional clustering
      2. MQT enhancements
      3. Compression of NULLS and DEFAULT
      4. Load enhancements
    3. System-related performance enhancements
      1. Prefetching enhancements
      2. Faster page cleaners
      3. Connection concentrator
      4. Type 2 indexes
      5. Stored procedures and UDFs thread-based model
      6. DMS container enhancements
      7. RUNSTATS enhancements
      8. Logging enhancements
      9. Manageability enhancements
    4. DB2 UDB Version 8.1.4
      1. Backup compression
      2. Range-clustered tables
      3. Direct I/O support on AIX
      4. Asymmetric index splitting
      5. Buffer pool memory allocation
      6. Page cleaning enhancements
      7. Lock deferral
      8. Improved sort performance
  14. Appendix B: Workloads used in the scenarios
    1. Introduction
    2. DTW workload
    3. EBIZ database
    4. Trade2 database and application
    5. WebSphere Performance Tools (WPT)
  15. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  16. Index (1/3)
  17. Index (2/3)
  18. Index (3/3)
  19. Back cover

Product information

  • Title: DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI
  • Author(s): Nagraj Alur, Peter Farrell, Philip Gunning, Saeid Mohseni, Swaminaathan Rajagopalan
  • Release date: April 2004
  • Publisher(s): IBM Redbooks
  • ISBN: None