Chapter 24. Traditional DB2 Performance Monitoring

IN THIS CHAPTER

• DB2 Traces

• Trace Destinations

• Tracing Guidelines

• DB2 Performance Monitor (DB2 PM)

• Using DB2 PM

• Online DB2 Performance Monitors

• Viewing DB2 Console Messages

• Displaying the Status of DB2 Resources

• Monitoring z/OS and OS/390

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, but also the DB2 address spaces. You can accomplish this task in three ways:

• 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.

• 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.

I will examine these monitoring methods later in this chapter, but first I will outline 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 for monitoring 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 (using EXPLAIN or DB2 Catalog reports, for example).

• 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.)

NOTE

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% up time, 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.