Monitoring and tuning
This section describes how to monitor a DB2® subsystem, determine performance problems by using batch reports and the Online Monitor functions of OMEGAMON® XE for Db2 PE and how to and tuning tasks with the ISPF Online Monitor. Such tasks include viewing past data and statistics, monitoring exceptions and thread activities, explaining SQL statements, and evaluating Db2 system parameters.
- Determining how an application will perform or is performing over a period of time
- Indicating where there are tuning opportunities in your system
- Distinguishing among subsystem and application problems
- Monitoring an application in a detailed manner so you can identify problem areas
- Analyzing constraints acting on an application
- Determining the performance effects of any adjustments made within the Db2 subsystem
- Determining whether performance objectives are being met
OMEGAMON XE for Db2 PE offers different ways to monitor your subsystem depending on whether you want to see current or past activity. Use the Online Monitor to monitor an active subsystem as well as to view events that happened in the recent past. Use the Batch reports to examine performance problems in the more distant past and trends over a period of time.
The amount of data generated for monitoring a Db2 subsystem is vast, so limiting the amount of data to show only potential problem areas is essential. OMEGAMON XE for Db2 PE offers several ways of reducing the amount of data that needs to be examined, the most important is exception processing. Exception processing makes it easy for you to focus on possible performance problems by highlighting data that is outside defined thresholds. It is available in Batch reporting and the Online Monitor.
A good approach is to monitor an active Db2 subsystem using Online Monitor exception processing (you do not need to be logged on to have exception processing running) and to regularly generate Statistics and Accounting exception reports. Online Monitor exception processing alerts you to performance problems as soon as they occur, and the Accounting and Statistics reports give you a detailed picture of application and system performance over a period of time.
Deadlock and timeout participant details are available online through exception event processing. Consider generating deadlock and timeout traces regularly, because in this way information is available to help you investigate any locking problems in detail. There is no significant performance overhead on the Db2 side in collecting the data for these reports.
The best way to investigate performance trends is by producing Accounting and Statistics reports that are ordered by interval.
To detect problems as they occur, use the Online Monitor periodic exception processing. When you detect poor thread performance, you can examine the comprehensive performance data that is shown in Thread and Statistics panels. If you assume the problem is caused by SQL, you can analyze the access path using the online explain function.
If the problem occurred in the recent past, you can use the Online Monitor HISTORY command to view the events surrounding the problem without having to re-create it.
If the panels do not provide enough information to solve the problem, you can use the Online Monitor to collect instrumentation data for batch reports. You can specify the data collection to be triggered by exception thresholds; in this way you can minimize the time high-volume and high-cost traces are active and ensure that the data needed is collected at the right time.
OMEGAMON XE for Db2 PE provides a comprehensive set of reports with different levels of detail and for different areas of performance. This represents a top-down approach to problem determination: the most generic reports indicate the problem area and, if necessary, more details can be shown to narrow down the cause of the problem.