Not using class 2 will not work. Class 1 is the portion of the transaction time that DB2 is aware of while class 2 is the portion of the class 1 time spent in DB2 (what’s affectionately referred to as “in SQL” time). Class 3 is the portion of the class 2 time spent waiting (lock/latch contention, sync I/O, update commit, etc.). Class 7 is your “in DB2” time and class 8 is your wait time for packages, similar to plan’s class 1 and 2 times. You need them all if you’re trying to troubleshoot an application performance problem. V8 also introduced an additional class for packages. Class 10 contains package detail (IFCID 239). The detailed information for class 10 is available only when classes 7 and 8 also are active. If there are no classes 7 and 8, then there’s no class 10 output. Be cautious when specifying class 10 because it can be expensive.
Here are breakdowns of IFCIDs associated with the most commonly used accounting classes:
- Class 1 contains IFCIDs 3, 106, and 239.
- Class 2, IFCID 232
- Class 3, IFCIDs 6-9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213-216, 226, 227, 242, 243, 321, 322, and 329
- Class 7, IFCIDs 232 and 240
- Class 8, IFCIDs 6-9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213-216, 226, 227, 241-243, 321, and 322
- Class 10, IFCID 239. IFCID 239 existed prior to V8 and came into play when there were more than 10 packages. In V8, with classes 7 and/or 8 active, only sections 1 and 2 of IFCID 239 will be written. However, for class 10, data sections 3, 4, and 5 are filled out.
Note the IFCID similarities between classes 2 and 7, and classes 3 and 8.
If you need to run a performance trace, you need to run it and you aren’t going to worry about the cost; you’re probably trying to solve a more pressing problem. Performance trace classes 1 (background events), 2 (subsystem events), and 3 (SQL events) can be from 5 to 100 percent overhead, depending on your DB2 subsystem activity. Fetch-intensive activity will drive the performance class 3 trace toward 100 percent, so be careful. You’ll usually turn on this trace when trying to solve a specific problem. In all, there are 22 classes you can choose from when running a performance trace in addition to three classes available for your own use. Those last three classes can come in handy.
When using a performance trace, specify only the classes, plans, authorization IDs, and IFCIDs you really need to solve your problem. This will minimize the amount of trace data being collected. DB2 9 enhances the START TRACE by allowing greater qualification granularity using the EXCLUDE and INCLUDE keywords. You also should use the Generalized Trace Facility (GTF) as the trace destination. GTF is the default destination for a reason. Trace records are immediately available at the conclusion of the trace and there’s no need to deal with SMF to see your trace results. You do need to be careful if the GTF trace data set is on disk; the trace could wrap. You also want to make sure the trace runs for as little time as possible. Start the trace just before the event you’re trying to trap and stop it as soon as the event concludes. This also will help minimize the amount of trace data gathered.
All the standard traces are interesting, but running them is of no use if you don’t examine and take advantage of the reports they can yield. Make sure you have the proper reporting tools in place to interrogate and interpret your trace results. If you’re going to run a trace, regardless of the cost, they’re all expensive if you don’t examine trace outputs.
Again, for most DB2 traces, the cost isn’t all that great, and sometimes it’s just something you must absorb. Knowing the cost upfront may make it easier to plan when to run a trace. Running some DB2 traces can (and usually will) be critical to the success of your DB2 for z/OS database.
Now that you have decided to run a few of the DB2 traces, you might benefit from the following hints, which can enhance your DB2 tracing experience.
Starting a Trace