Investigating Db2 performance problems

Many different logs, traces, and reports are available for analyzing Db2 performance problems, and you might find it hard to know where to look first. By taking a systematic approach, you can focus your investigation and determine the nature of the problem more quickly.

Before you begin

Depending on the symptoms, the first step of investigating a performance problem might be to look at the overall system to determine whether the problem might be outside of Db2:

Analyze why application processes are progressing slowly, or why a given resource is being heavily used. The best tools for that kind of investigation include:
- z/OS® Resource Measurement Facility (RMF)
- IBM® OMEGAMON® AI for Db2.
If the application processes are running on distributed platforms, analyze the performance on those platforms and the network that connects them.

After you determine that the problem is inside Db2, you can begin detailed analysis of performance data that you captured during monitoring.

About this task

If your initial analysis suggests that the performance problem is within Db2, the problem might be poor response time, an unexpected and unexplained high use of resources, or locking conflicts.

Procedure

To investigate Db2 performance problems.

Activate the necessary trace classes. The following trace classes are recommended, as needed:
- Accounting trace classes:
  - 1, 2, and 3 for plans.
  - 7, and 8 for packages, when needed.
  - 10 for package details, when needed. This class is resource intensive.
- Statistics trace classes:
  - 1, 3, and 4 for non-data sharing environments.
  - 5 for data sharing environments.
  - 7 for distributed location statistics
  - 8 for buffer pool data set statistics (IFCID 0199).
Use the trace data to further narrow your performance investigation.
- Use Db2 statistics trace data to analyze performance problems at the subsystem level.
- Use Db2 accounting trace data to analyze application performance problems at the thread level.