Planning Your Db2 Performance Monitoring Strategy

The first part of any Db2 performance management strategy should be to provide a comprehensive approach to the monitoring of the Db2 subsystems operating at your shop. This approach involves monitoring not only the threads accessing Db2 and the SQL they issue, but also the DB2 address spaces. 

There are three aspects that must be addressed in order to accomplish this task:

• Batch reports run against Db2 trace records. While Db2 is running, you can activate traces that accumulate information, which can be used to monitor both the performance of the Db2 subsystem and the applications being run. For more details on Db2 traces see my earlier 2-part blog post (part 1, part 2).
• Online access to Db2 trace information and Db2 control blocks. This type of monitoring also can provide information on Db2 and its subordinate applications.
• Sampling Db2 application programs as they run and analyzing which portions of the code use the most resources.

There are many in-depth details that comprise the task of setting these three components up to efficiently and effectively monitor your Db2 activity. I go over these details in my book, Db2 Developers Guide, so I direct interested parties there for the gory details.

But let's go over some performance monitoring basics. When you’re implementing a performance monitoring methodology, keep these basic caveats in mind:

• Do not overdo monitoring and tracing. Db2 performance monitoring can consume a tremendous amount of resources. Sometimes the associated overhead is worthwhile because the monitoring (problem determination or exception notification) can help alleviate or avoid a problem. However, absorbing a large CPU overhead to monitor a Db2 subsystem that is already performing within the desired scope of acceptance might not be worthwhile.
• Plan and implement two types of monitoring strategies at your shop:

1. ongoing performance monitoring to ferret out exceptions, and;
2. procedures for monitoring exceptions after they have been observed.

• Do not try to drive a nail with a bulldozer. Use the correct tool for the job, based on the type of problem you’re monitoring. You would be unwise to turn on a trace that causes 200% CPU overhead to solve a production problem that could be solved just as easily by other types of monitoring (e.g. using EXPLAIN or Db2 Catalog reports).
• Tuning should not consume your every waking moment. Establish your Db2 performance tuning goals in advance, and stop when they have been achieved. Too often, tuning goes beyond the point at which reasonable gains can be realized for the amount of effort exerted. (For example, if your goal is to achieve a five-second response time for a TSO application, stop when you have achieved that goal instead of tuning it further even if you can.)

Tuning goals should be set using the discipline of service level management (SLM). A service level is a measure of operational behavior. SLM ensures applications behave accordingly by applying resources to those applications based on their importance to the organization. Depending on the needs of the organization, SLM can focus on availability, performance, or both. In terms of availability, the service level can be defined as “99.95% uptime, during the hours of 9:00 AM to 10:00 PM on weekdays.” Of course, a service level can be more specific, stating “average response time for transactions will be two seconds or less for workloads of 500 or fewer users.”

For a service level agreement (SLA) to be successful, all of the parties involved must agree upon stated objectives for availability and performance. The end-users must be satisfied with the performance of their applications, and the DBAs and technicians must be content with their ability to manage the system to the objectives. Compromise is essential to reach a useful SLA.

If you do not identify service levels for each transaction, then you will always be managing to an unidentified requirement. Without a predefined and agreed upon SLA, how will the DBA and the end-users know whether an application is performing adequately? Without SLAs, business users and DBAs might have different expectations, resulting in unsatisfied business executives and frustrated DBAs... Not a good situation.