Db2 for z/OS Performance Traces Part 1 - Intro, Accounting, and Audit

When it comes to Db2 for z/OS performance management, one of the first things you need to understand is the traces that are available that must be started in order to cause Db2 to track performance metrics. You can think of a Db2 trace as a window into the performance characteristics of aspects of your workload. Db2 traces record diagnostic information describing specific performance events. As Db2 operates, and workload is run against it, Db2 writes trace information based on the traces types that have been started. The information is written out in such a way that it can be read and analyzed by DBAs and performance analysts.

There are six types of Db2 traces available, each one pertaining to a different category of Db2 performance and operations. These six types of traces are:

• Accounting - performance information about the execution of DB2 application programs 
• Audit - information about Db2 DDL, security, ­utilities, and data modification activities
• Global - information for servicing Db2 
• Monitor - data useful for online monitoring of the Db2 subsystem and application programs 
• Performance - detailed data about Db2 events, enabling database and performance analysts to pinpoint the causes of performance problems 
• Statistics - information regarding the Db2 subsystem’s use of resources 

There are two ways to start most Db2 traces: you can specify the appropriate DSNZPARMs to start traces when Db2 starts up or you can issue the -START TRACE command to initiate specific traces when Db2 is already running. 

Each trace is broken down further into classes, each of which provides information about aspects of that trace. 

Accounting Trace

The accounting trace is one of the most important traces for judging the performance of Db2 application programs. Accounting trace records can contain information regarding:

• CPU and elapsed time of the program
• Usage of the EDM pools
• Locks and GETPAGE page requests, by buffer pool, issued by the program
• Number of synchronous writes
• Thread wait times
• Type of SQL issued by the program
• Number of COMMITs and ABORTs issued by the program
• Program’s use of sequential prefetch and other DB2 performance features (RLF, distributed processing, and so on)

There are ten groups, or classes, of Db2 accounting traces:

• Class 1: Standard accounting information
• Class 2: Entry or exit from DB2 events
• Class 3: Elapsed wait time in DB2
• Class 4: Installation-defined accounting record
• Class 5: Time spent processing IFI requests
• Class 6: Reserved
• Class 7: Entry or exit from event signaling package accounting
• Class 8: Wait time for a package
• Class 10: Package detail
• Class 11 through 28: Reserved
• Class 30 through 32: Local use

Most organizations start Db2 accounting classes 1, 2, and 3 together because they provide a great deal of information with only a low overhead. Furthermore, you cannot run class 2 or 3 without also running class 1.

Accounting trace classes 7 and 8 provide performance trace information at the package level. For class 7 accounting, trace information is gathered for packages executed, and for class 8 information, the amount of time an agent was suspended in Db2 for each executed package is collected. If you have already activated accounting trace classes 2 and 3, adding trace classes 7 and 8 incurs only a minimal additional cost.

Audit Trace

The audit trace is useful for installations that must meticulously track specific types of Db2 events, particularly to support regulatory and compliance requirements. Not every shop needs the audit trace. However, those wanting to audit by AUTHID, specific table accesses, and other Db2 events mayfind the audit trace invaluable. 

Of course, as of Db2 10 for z/OS, an alternate approach to auditing with audit policies is available. This requires use of SECADM, which is not pervasively implemented, though.

There are eight categories of audit information that can be provided using the audit trace:

• All instances in which an authorization failure occurs, for example, if USER1 attempts to SELECT information from a table for which he or she has not been granted the appropriate authority
• All executions of the DB2 data control language GRANT and REVOKE statements
• Every DDL statement issued for specific tables created by specifying AUDIT CHANGES or AUDIT ALL
• The first DELETE, INSERT, or UPDATE for an audited table
• The first SELECT for only the tables created specifying AUDIT ALL
• DML statements encountered by DB2 when binding
• All AUTHID changes resulting from execution of the SET CURRENT SQLID statement
• All execution of IBM DB2 utilities

Again, there are groups, or classes of trace information that can be started. In the case of the audit trace, there are eleven trace classes:

• Class 1: Attempted access denied due to lack of authority
• Class 2: GRANT and REVOKE 
• Class 3: CREATE, ALTER, and DROP statements against audited tables
• Class 4: First change made to an audited object
• Class 5: First read made against an audited object
• Class 6: BIND information for SQL statements on audited objects
• Class 7: Assignment or change of an AUTHID
• Class 8: Utility execution
• Class 9: Installation-defined audit trace record
• Class 10: Trusted Context information
• Class 11: Audit administrative authorities
• Class 12 through 29: Reserved
• Class 30 through 32: Local use

The overhead of the audit trace depends greatly on the amount of data that is being audited. Things like transaction frequency, modification frequency, DDL activity, and so on will dictate the actual amount of overhead required.

Next Time...

So far, we have only looked at two of the six types of Db2 traces. Be sure to check back later for Parts 2 and 3 of this blog series that will introduce the remaining types of traces and additional useful information on dealing with Db2 performance tracing.