Willie's "Getting the Most out of DB2 for z/OS and System z" blog.

If someone has a performance question, one of the best first responses is "What traces are running?" After all, how can you determine if something is not doing what you expect (within the parameters you expect), when you have no idea what it's doing. It can be a real problem. You don't always have the luxury of running something a second time ans getting the same performance results, just so you can turn on the appropriate traces. So, do you run all of the traces all of of time? Well, maybe not. At some point the "checkup" may start to cause more damage than the disease you're trying to cure. And I have to tell you, I'm at the top of the list of people that like to tell other people to turn on more traces. When I spout off that someone needs to be running this trace or that trace, very often the first question I get is "What's the overhead, the cost of performing the trace?" Because I usually not suggesting they run a global trace, I can usually safely say "Minimal". Maybe a better answer might be "Acceptable". After all, I can live with all of the overhead YOUR DB2 subsystem incurs. I get to go home and my pager (or cell) rarely goes off because you have a performance problem. I say rarely because I have been tracked down on a weekend and asked a performance question. So, what is the cost of running a DB2 trace? And the answer is pretty easy... Why? Because the average possible cost of the most popular traces is actually documented in the DB2 manuals. There's a paragraph or two in Chapter 26, "Improving response time and throughput" in the DB2 V7 and V8 Administration Guides or Chapter 23, "Using DB2 Trace to monitor performance" in the DB2 9 Performance Monitoring and Tuning Guide. But the story doesn't just end there. You will find, that if you believe what is published in the manuals, you had better make sure you pick up the correct manual for the DB2 you running. You see, the numbers are different depending on the version of DB2. Now I would have expected that when looking at the V7 manual and comparing it to the V8 manuals. However, between those two version there was little, if any, change. The change in the literature came in the move from the V8 Admin Guide to the new DB2 9 Performance Monitoring and Tuning Guide. My thinking, and this is purely conjecture on my part, is the changes just didn't catch up until they put together the DB2 9 Performance book. Some of the changes are subtle or affect traces you haven't any business running in the first place. Take for example DB2's global trace. I'm sure everyone loves to turn on this trace just for the fun of it (he stated tongue in cheek). we all know that global traces can be expensive and we have been warned against running them as long as there has been a DB2. DB2 V6 and V7 state the overhead can be from 20-100%. The V8 manuals changed this slightly. They claim 2-100% leading me to think there might have been a possible typo in the book as apposed to a huge drop in the low end cost of running this trace. The DB2 9 book has completely different values; 10-150%. These values are significantly different and I tend to believe them. Of course the bottom line is that you don't turn on this trace unless Level 2 support tells you to when trying to track down a service problem. What about some of the more common USED traces? Statistics trace - you should always have this turned on. By default it is on and you would have to something adverse to shut 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 IFCID 225; your storage map. When problems with DBM1 storage started back in V7, we needed the IFCID 225 records to help determine where the problem was. It was tough at first to get people to turn on the extra class 6 recording. Some still couldn't see the importance of having this information available, so we eventually just added the IFCID 225 record to class 1. So now we get it all of the time. What about overhead... stat records are only recorded at statistical intervals. That is one of the reasons the overhead for turning this trace on is negligible. so much so, no percentages are even quoted; and I checked the books all the way back to V5. BTW, if you are running a z9 or z10 (and maybe even a z990), your stats interval should be cranked down real low, like someplace around 5 minutes. You can always lump multiple intervals together, but your stats interval is the most granularity you are going to ever get. With a big number (15, 30, and some even have 60 minutes), you have huge intervals with practically no granularity. Audit trace - here's a trace that has really gained popularity with all of the compliance and regulatory issue running around today. It seems no one wants to go to jail... but I digress... Audit traces are pretty cheap to run, up through DB2 V8 we were quoting around maybe 5%. In the DB2 9 manual, that number has been bumped up to 10%, still a fairly low number if it gives you the confidence to sleep well at night. Keep in mind that the amount of overhead is directly dependent on the number of tables you are auditing or the frequency of the event that is being audited. Accounting trace - Now this is the fun one. There are kinds of way to affect the cost of this trace. Even which traces you run is important. The DB2 9 manual does give some estimates for Class 1, 3, 7, and 8. Class 1 is usually less than 5% unless fetch intensive and multi-row fetch is not being used. Class 3 is less than 1% and Class 7 and 8 are less than 5%. It's the class 2 time that can get tricky. CPU overhead for class 2 can reach as high as 20% for fetch intensive programs. Previous version of DB2 give no numbers, for class 1, 3, 7, and 8. They only talk about class 2. they quote online activity at 2.5% and batch as high as 10%, neither even close to the DB2 9 20% overhead. What about not using Class 2? Well, that really won't work. You see class 1 is your transaction time while class 2 is the portion of the class 1 time spent in DB2, your SQL time. Class 3 is how much of the class 2 time was spent waiting. Classes 7 and 8 pertain to packages. Class 7 is your in Db2 time and class 8 is you wait times. So you see, you need them all if you are trying to shot an application performance problem. And be very careful about using the Accounting class 10 package detail trace; this thing can really be expensive. Performance trace - Not going to spend too much time here. If you need to run a performance trace, you need to run it and you are not going to worry about the cost. You are problem trying to solve some other more pressing problem anyway. Performance traces can be from 5-100% overhead depending on the activity going on in your DB2 subsystem. Fetch intensive activity will drive the performance class 3 times toward 100%, so be careful. You will usually turn on this trace under advisement.