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:
- ongoing performance
monitoring to ferret out exceptions, and;
- 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.
