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
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
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
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
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:
- SQL activity
- SQL statement
- Memory Pools
In Figure 5, we see details on a specific SQL statement.
Figure 5. 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
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)
Figure 8 shows the threshold exception log, drilling down to see periodic exception details for SQL activity.
Figure 8. 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
Figure 10 shows how those predefined queries may be examined or executed.
Figure 10. Performance Warehouse
In Figure 11 you see one of the predefined queries.
Figure 11. Performance Warehouse - Predefined query showing sort overflows - SQL
Figure 12 shows the results from running the query.
Figure 12. Performance Warehouse - Predefined query showing sort overflows - 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
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
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
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.
Special thanks to Ute Baumbach, IBM Development Lab DB2 Performance Expert, for reviewing this article.
- DB2 Performance Expert for Linux, UNIX and Windows Homepage gives an introduction on DB2 Performance Expert.
- DB2 Performance Expert - Documentation.