Simplify performance management and tuning with DB2 Performance Expert: DB2 Performance Expert overview

Part 1 in a series explaining how to quickly troubleshoot and tune your DB2 UDB servers

DB2® Performance Expert is a tool for monitoring, analyzing, and tuning the performance of DB2 Universal Database™ (DB2 UDB) and its applications. Part 1 of this article introduces the tool and covers its basic functions. Part 2 walks you through some practical scenarios so you can see exactly how it can help you.

Share:

Werner Schuetz (werner_schuetz@de.ibm.com), Technical Consultant DB2, IBM Innovation Center Stuttgart, IBM

Werner Schuetz photoWerner Schuetz is an IBM Certified IT Specialist, IBM Certified Advanced Database Administrator and Certified Application Developer for DB2 UDB V8. He works as a DB2 Technical Consultant in the IBM Innovation Center in Stuttgart, Germany and supports Independant Software Vendors in testing DB2 solutions, executing performance and tuning sessions and running competitve database migrations.



16 September 2004

Introduction

Do you need a detailed analysis of key performance factors that let you control and tune the performance of DB2 UDB and DB2 applications? Are you expected to proactively diagnose performance and availability problems? Or, have you had the experience of encountering a problem with your DB2 server, but the cause of the problem could not be detected in your current snapshots, and you wished you had historical monitoring data?

DB2 Performance Expert V2.1 is a tool which can assist you in accomplishing those tasks. Part 1 of this article introduces DB2 Performance Expert and illustrates its components. Part 2 takes you into the product, demonstrating various practical usage scenarios, such as:

  • Determining whether or not an index would improve performance
  • Reviewing sort performance
  • Checking the need to reorganize tables
  • Ensuring that there are sufficient DB2 agents to handle the workload
  • Resolving lock conflicts
  • Examining frequently used SQL statements from the package cache
  • Analyzing buffer pools
  • Monitoring system health.

DB2 Performance Expert V2.1

DB2 Performance Expert (DB2 PE) is a workstation-based performance analysis and tuning tool that simplifies DB2 performance management. It gives you the capability to monitor applications, system statistics and system parameters in real-time and in historical mode. In addition, DB2 PE helps you analyze performance bottlenecks and produces tuning recommendations for improving system and application performance.

With Performance Expert, you can manage a heterogeneous mix of DB2 systems from a single user interface. It integrates performance monitoring, reporting, buffer pool analysis, and a Performance Warehouse function. It provides a consolidated, consistent system view for monitoring multiple DB2 instances on different operating systems. DB2 Performance Expert supports DB2 servers running on Windows®, AIX®, HP-UX, z/OS®, Linux, Linux on zSeries®, and the Sun Solaris Operating Environment.

Here are some of the advanced capabilities of DB2 Performance Expert:

  • It monitors and analyzes the performance of DB2 and DB2 applications to simplify performance tuning.
  • It includes a performance warehouse for storing performance data and analysis tools that lets you:
    • Save DB2 snapshot and event monitoring data (for SQL, database and buffer pool activities) and create reports for investigation and trend analysis
    • Configure and schedule the report and load process from the workstation GUI functions
    • Define and apply analysis functions to identify performance bottlenecks.
  • It provides expert analysis, a real-time on-line monitor, and a wide range of reports for analyzing and optimizing DB2 applications and SQL statements. The online monitor includes an extended system overview, graphs in details panels, improved navigation and filtering.
  • It provides exception conditions to anticipate emerging DB2 performance and availability problems.
  • It lets you generate buffer pool reports in multiple formats including, tables, pie charts and diagrams.
  • It provides full support for DB2 Extended Enterprise Edition (EEE) or DB2 Enterprise Server Edition with the Data Partitioning Feature, enabling highly sophisticated performance monitoring of large enterprise systems.
  • It monitors DB2 Connect gateways including databases and system related information.

DB2 PE component overview

System Overview and configuration

The System Overview screen, shown in Figure 1, provides an overview of all the systems you are monitoring in terms of logon and configuration status. It gives you a single view of critical performance counters, and shows event exceptions (such as deadlocks) with drill down capability.

Figure 1. System Overview
System overview

Online Monitor - Statistics Details

Figure 2 shows the statistics details screen for the online monitor. This screen displays statistics counters in detail for:

  • Instance information
  • Databases (usage, caches, high water marks, locks, reads, writes, and so on)
  • Tablespaces and tables
  • Buffer pools (read, writes, I/O, and so on)
  • Memory pools
  • Dynamic SQL Statement cache details
Figure 2. Online Monitor - Statistic Details
Statistic Details

In Figure 3 you can see the level of detail that is available if you drill down from the previous screen. In this instance, you can see I drilled down from the database level to show SQL activity. Information about SQL statements is shown in graphs as well as report format to help you more quickly retrieve statistical information.

Figure 3. Online Monitor - Database drill down
Database drill down

Online Monitor - System Health

Graphical data views for critical performance counters can be defined to monitor their evolution over time. In Figure 4, you see various graphs that show the details which you might want to monitor such as the buffer pool hit ratio, failed SQL statements, and sort overflows. Again, these are details that will quickly give you insight into the performance of your system, and alert you right away if there are problems.

Figure 4. Online Monitor - System Health Panel
System Health Panel

Online Monitor - Application Details

The Application Details screen gives an overview what's currently active at your instance and displays application summaries and details, including the following:

  • Times
  • Locking
  • SQL activity
  • SQL statement
  • Caching
  • Sorting
  • Memory Pools

In Figure 5, we see details on a specific SQL statement.

Figure 5. Online Monitor - Application Details
Online Monitor - Application Details

The SQL activity report, shown in Figure 6, is based on event monitoring data. It shows you all SQL statements (static and dynamic) executed by an application and provides a high level of detail on each statement.

Figure 6. Online Monitor - SQL Activity Report
SQL Activity Report

Exception processing

Exception processing is monitoring for those situations which indicate an abnormal problem, or an unusual situation that warrants attention from the DBA. For example, if your application begins encountering deadlocks, you would want to alert the DBA. There may be other situations which you would want to define for a threshold exception. You can define alerts and notifications for these exception conditions.

Figure 7 shows event exception processing for deadlocks.

Figure 7. Exception Processing (deadlocks)
Exception Processing (deadlocks)

Figure 8 shows the threshold exception log, drilling down to see periodic exception details for SQL activity.

Figure 8. Exception Processing (threshold exeption log)
Exception Processing (threshold exeption log)

Performance Warehouse (PWH)

The Performance Warehouse provides long-term storage for SQL, buffer pool and database activity data. The data may be analyzed by generating reports, executing queries, or Rules-of-Thumb. Analyzing and following recommendations allows you to proactively improve your system behavior.

There are 25 predefined queries that may be run against the information stored in the Performance Warehouse. Some of those are shown in the Performance Warehouse screen in Figure 9.

Figure 9. Performance Warehouse
performance warehouse

Figure 10 shows how those predefined queries may be examined or executed.

Figure 10. Performance Warehouse
performance warehouse

In Figure 11 you see one of the predefined queries.

Figure 11. Performance Warehouse - Predefined query showing sort overflows - SQL
predefined query

Figure 12 shows the results from running the query.

Figure 12. Performance Warehouse - Predefined query showing sort overflows - Results
query results

Buffer pool analyzer (BPA)

The buffer pool analyzer shows buffer pool activity over a specified period of time with both report and graphical views. Figure 13 shows a screen shot of the buffer pool analyzer. The line graph lets you quickly see the percentage of SQL requests that are being read from the buffer pool.

Figure 13. Buffer Pool Analyzer
buffer pool analyzer

DB2 Control Center Plugin

The monitoring functions of DB2 PE can be directly launched from the DB2 Control Center. As you see in Figure 14, when you have DB2 PE installed, you right-click on the instance name to access the Performance Expert capabilities.

Figure 14. DB2 Control Center Plugin
Sample display screen

DB2 PE eMail Notification

Notifications regarding periodic and event exceptions can be send to an email address via SMTP. Figure 15 illustrates how you would define an SMTP alert.

Figure 15. DB2 PE eMail Notification
Sample display screen

Conclusion

In Part 1 you have seen illustrated the basic capabilities of the DB2 Performance Expert. Move on to Part 2 for practical examples of how to make DB2 Performance Expert solve your problems.


Acknowledgements

Special thanks to Ute Baumbach, IBM Development Lab DB2 Performance Expert, for reviewing this article.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=16289
ArticleTitle=Simplify performance management and tuning with DB2 Performance Expert: DB2 Performance Expert overview
publish-date=09162004