Applications & Databases

Sep 1 ’08

If asked what the three biggest challenges are with DB2 for z/OS, the answer you might hear most often is, “Performance, performance, and performance.” The most jammed sessions at conferences cover performance topics. The most popular articles are on performance, as are most questions on lists and forums.

Is the DB2 for z/OS community obsessed with performance? Are there more performance issues with a mainframe database? Well, no more or less than any other platform. Performance is just something that’s always been a priority. ... The mainframe, and DB2 for z/OS, both offer what can be a blessing and, at times, a curse: great metrics. There are records describing just about everything. How do you obtain this information? One way is with DB2 traces.

Often, when someone has a DB2 for z/OS performance question, one of the best first responses is, “What traces were you running?” After all, how can you determine if something isn’t doing what you expect, within the parameters you expect, if you have no idea what it’s doing? That can be a real problem. You don’t always have the luxury of running an application a second time in an attempt to obtain the same performance results just so you have the opportunity to turn on the appropriate traces.

Should you constantly run all the possible traces you might need? No. At some point, such a “checkup” can cause more damage than the disease. You should be aware of what traces you’re running and their cost. Be careful of others that recommend running certain traces. You must always consider how much harm a trace might potentially cause.

Tracing your DB2 subsystem is important and some monitoring should almost always be performed, but what’s the cost of running one or more of DB2’s traces? The average possible cost of the most popular traces is documented in the DB2 manuals; you can refer to:

  • Chapter 26, “Improving Response Time and Throughput” in the DB2 V7 and V8 Administration Guides (SC26-9931 and SC18-7413, respectively)
  • Chapter 21, “Using DB2 Trace to Monitor Performance” in the DB2 9 Performance Monitoring and Tuning Guide (SC18-9851).

But the story doesn’t end there. If you believe what’s published in the manuals, you had better make sure you pick up the correct manual for the DB2 you’re running. The numbers differ, depending on the version of DB2. Differences between the V7 and V8 manuals are minimal, and are more substantial when moving from V8 to DB2 9.

Some of the changes are subtle or affect traces you shouldn’t run anyway. Consider DB2’s global trace. Using it can be extremely expensive and we’ve been incessantly warned against running it. DB2 V6 and V7 state the overhead can be from 20 to 100 percent. The DB2 9 book has completely different values: 10 to 150 percent. These values seem credible. Don’t turn on this trace unless you do so at Level 2 support’s suggestion when trying to track down a service problem.

The more commonly used traces and what you should know about them is discussed in the following sections.

Statistics Trace

Leave this on. It’s “on” by default in your DSNZPARM member and you would have to do something adverse to turn it off. Using the default, you get classes 1, 3, 4, 5, and 6. Class 6 is a fairly recent addition to the default list. This was the class that gave you Instrumentation Facility Component Identifier (IFCID) 225, your DBM1 address space storage map. When problems with the DBM1 storage started back in V7, IFCID 225 records were needed to help determine where the storage problem existed. Initially, it was tough to get people to turn on the extra class 6 recording. Even today, some still don’t see the importance of having this information available, so eventually the IFCID 225 record was just added to stats class 1. Now everyone continuously gets it.

4 Pages