What about the other statistic classes?
- System services (IFCID 001), database statistics (IFCID 002), open page set detail (IFCID 105), buffer pool information (IFCID 202), and what system parameters were in effect at the time the trace was started (IFCID 106) are all recorded as a result of having class 1 started. IFCID 225 also is included with class 1.
- Class 3 has your lock timeout details (IFCID 196), deadlock (IFCID 172), lock escalation (IFCID 337), group buffer pool stuff (IFCIDs 250, 261, and 262), data set extension information (IFCID 258), indications of longrunning URs and readers (IFCID 313), and active log space shortages (IFCID 330).
- Class 4 provides information about exceptional conditions; for example, a -905 from a dynamic SQL statement exceeding its RLF ASUTIME (IFCID 173), and lots of stuff relating to distributed processing exceptions.
- Class 5 is necessary only if you’re using data sharing and record details on global buffer pool statistics (IFCID 230).
For all practical purposes, you can assume that stats records are written only at the end of each statistical interval. That’s why the overhead is negligible when turning on this trace. In fact, no percentages are even quoted in the product manuals. Most of the statistical counters are maintained in memory and are just written out in the SMF data record at the DB2 statistics interval. The overhead is more like noise when it comes to CPU and SMF data volume with SMF writing out only the 100 and 102 records. This is one DSNZPARM you’re better off just leaving alone. Use the defaults to ensure that subsystem-level statistics are always available.
If you’re running a z9, z10, or z990, your stats interval should be quite low; it should be set to five minutes or less. It’s amazing how much can happen on a z10 in just a wall clock minute. You can always lump together multiple intervals, but your stats interval is the most granularity you’ll ever achieve. With a bigger number (15, 30, or more), you have huge intervals with practically no granularity. This can make it quite difficult to identify where a system problem might exist. Even at an interval as low as one minute, you’ll record only a maximum of 1,440 stat intervals per day. For these reasons, I strongly recommend setting the interval to one minute, regardless of processor model. The cost of a statistics trace is still little more than noise. A smaller degree of granularity is needed to study your system’s growth leading up to a system slowdown. It also will enable better virtual storage planning. If the statistics interval is too large, then high points and critical events become more difficult to identify.
The audit trace has gained popularity with all the compliance and regulatory issues prevalent today. Audit traces aren’t that expensive to run. Up through V8, the overhead was cited as about 5 percent. In the DB2 9 manual, that number has been bumped up to less than 10 percent, which is still a fairly low number if the trace helps you sleep at night. That’s mostly for OLTP with all classes active. For utilities, batch, and some queries, the overhead would be negligible. Remember, the amount of overhead is directly dependent on the number of tables you’re auditing or the frequency of the event being audited. Starting an audit trace with no classes specified will get you only class 1, tracing all access attempts denied due to inadequate authorization (IFCID 140). For many, this is adequate and costs the least to run. Indicating you want class 2 will track all explicit GRANTs and REVOKEs (IFCID 141). Additional cost would be proportional to the number of times a GRANT or REVOKE was used. Invoking class 3 will trace all CREATE, ALTER, and DROP operations against audited tables or a table with multi-level security with row-level granularity (IFCID 142). Classes 4 and 5 will record the first attempted change to an audited object in a unit of work (IFCID 143) and the first attempted access to an audited object within a unit of work (IFCID 144), respectively. An audited object is a table that has been created or altered to include the AUDIT keyword. Specifying the audit class 6 trace will write a record for each invocation of the bind process for both static and dynamic SQL statements that involve audited objects (IFCID 145). When activating an audit class 7 trace, you will record:
- An issuance of a set current SQLID statement (IFCID 55)
- The ending of an identify request for an IMS, CICS, Call Attach Facility (CAF), Recoverable Resource Manager Services Attachment Facility (RRSAF), utility, or Time Sharing Option (TSO) connection (IFCID 83)
- When a SIGNON by IMS, CICS, or RRSAF occurs, or for a Database Access Thread (DBAT) that might have changed the authorization id (IFCID 87)
- A distributed authid translation (IFCID 169)
- An audit trail for security processing (IFCID 319).
The last class available for an audit is class 8. It records the start (IFCID 23), phase change (IFICD 24), and end (IFCID 25) of a utility.
There are many ways to affect the cost of an accounting trace. Which trace classes you choose to run will have an impact on the cost of running that trace.
The DB2 9 manual provides time estimates for classes 1, 3, 7, and 8. Class 1 is usually less than 5 percent unless the query is fetch-intensive and multirow fetch isn’t used. Class 3 is less than 1 percent, and classes 7 and 8 are less than 5 percent. It’s the class 2 times that can get tricky. CPU overhead for class 2 trace can range from 1 to 10 percent, leaning toward the high end for fetchintensive programs. Class 2 overhead is on a per SQL request basis (entry/exit to DB2). The simpler the base SQL cost, the bigger the overhead in percentage terms. Class 3 overhead is based on a per wait event basis.
Previous versions of DB2 give no numbers for classes 1, 3, 7, and 8. They talk only about class 2. They quote online activity at 2.5 percent and batch as high as 10 percent.