DB2 Traces

 <  Day Day Up  >  

The first type of performance monitoring I discuss here is monitoring based on reading trace information. You can think of a DB2 trace as a window into the performance characteristics of aspects of the DB2 workload. DB2 traces record diagnostic information describing particular events. As DB2 operates, it writes trace information that can be read and analyzed to obtain performance information.

DB2 provides six types of traces, and each describes information about the DB2 environment. These six types of traces are outlined in Table 24.1.

Table 24.1. DB2 Trace Types

Trace

Started By

Description

Accounting

DSNZPARM or -START TRACE

Records performance information about the execution of DB2 application programs

Audit

DSNZPARM or -START TRACE

Provides information about DB2 DDL, security, utilities, and data modification

Global

DSNZPARM or -START TRACE

Provides information for the servicing of DB2

Monitor

DSNZPARM or -START TRACE

Records data useful for online monitoring of the DB2 subsystem and DB2 application programs

Performance

-START TRACE

Collects detailed data about DB2 events, enabling database and performance analysts to pinpoint the causes of performance problems

Statistics

DSNZPARM or -START TRACE

Records information regarding the DB2 subsystem's use of resources


Note that you start DB2 traces in two ways: by specifying the appropriate DSNZPARMs at DB2 startup or by using 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. Classes are composed of IFCIDs. An IFCID (sometimes pronounced if-kid ) is an Instrumentation Facility Component Identifier. An IFCID defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked by DB2.

The six DB2 trace types are discussed in the following sections.

Accounting Trace

The accounting trace is probably the single most important trace for judging the performance of DB2 application programs. Using the accounting trace records, DB2 writes data pertaining to the following:

  • CPU and elapsed time of the program

  • EDM pool use

  • 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 COMMIT s and ABORT s 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 of DB2 accounting trace classes:

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 or DBRM accounting

Class 8 ” Wait time for a package

Class 10 thru 29 ” Reserved

Class 30 thru 32 ” Local use

Estimated overhead : DB2 accounting class 1 adds approximately 3% CPU overhead. DB2 accounting classes 1, 2, and 3 together add approximately 5% CPU overhead. 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. Enabling this level of tracing can cause significant overhead.

Audit Trace

The audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every shop needs the audit trace. However, those wanting to audit by AUTHID , specific table accesses , and other DB2 events will find the audit trace invaluable. Eight categories of audit information are provided:

  • 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

This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications.

There are eleven groups of DB2 audit trace classes:

Class 1 ” Attempted access denied due to lack of authority

Class 2 GRANT and REVOKE statements

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 thru 29 ” Reserved

Class 30 thru 32 ” Local use

Estimated overhead : Approximately 5% CPU overhead per transaction is added when all audit trace classes are started. See the "Tracing Guidelines" section later in this chapter for additional information on audit trace overhead.

Global Trace

Global trace information is used to service DB2. Global trace records information regarding entries and exits from internal DB2 modules as well as other information about DB2 internals. It is not accessible through tools that monitor DB2 performance. Most sites will never need to use the DB2 global trace. You should avoid it unless an IBM representative requests that your shop initiate it.

CAUTION

IBM states that the global trace can add up to 100% CPU overhead to your DB2 subsystem.


Monitor Trace

An amalgamation of useful performance monitoring information is recorded by the DB2 monitor trace. Most of the information in a monitor trace is also provided by other types of DB2 traces. The primary reason for the existence of the monitor trace type is to enable you to write application programs that provide online monitoring of DB2 performance.

Information provided by the monitor trace includes the following:

  • DB2 statistics trace information

  • DB2 accounting trace information

  • Information about current SQL statements

There are ten groups of DB2 monitor trace classes:

Class 1 ” Activate the READS IFCIDs

Class 2 ” Entry or exit from DB2 events

Class 3 ” DB2 wait for I/O or locks

Class 4 ” Installation-defined monitor trace record

Class 5 ” Time spent processing IFI requests

Class 6 ” Changes to tables created with DATA CAPTURE CHANGES

Class 7 ” Entry or exit from event signaling package or DBRM accounting

Class 8 ” Wait time for a package

Class 9 thru 29 ” Reserved

Class 30 thru 32 ” Local use

Estimated overhead : The overhead that results from the monitor trace depends on how it is used at your site. If, as recommended, class 1 is always active, and classes 2 and 3 are started and stopped as required, the overhead is minimal (approximately 2 to 5%, depending on the activity of the DB2 system and the number of times that the other classes are started and stopped ). However, if your installation makes use of the reserved classes (30 through 32) or additional classes (as some vendors do), your site will incur additional overhead.

NOTE

Some online performance monitoring tools do not use the monitor trace; instead, they read the information directly from the DB2 control blocks. Sampling DB2 control blocks requires less overhead than a monitor trace.


Performance Trace

The DB2 performance trace records an abundance of information about all types of DB2 events. You should use it only after you have exhausted all other avenues of monitoring and tuning because it consumes a great deal of system resources.

When a difficult problem persists, the performance trace can provide valuable information, including the following:

  • Text of the SQL statement

  • Complete trace of the execution of SQL statements, including details of all events (cursor creation and manipulation, actual reads and writes, fetches, and so on) associated with the execution of the SQL statement

  • All index accesses

  • All data access due to referential constraints

There are twenty groups of DB2 performance trace classes:

Class 1 ” Background events

Class 2 ” Subsystem events

Class 3 SQL events

Class 4 ” Reads to and writes from buffer pools and the EDM pool

Class 5 ” Writes to log or archive log

Class 6 and 7 ” Summary (6) and detailed (7) lock information

Class 8 ” Data scanning detail

Class 9 ” Sort detail

Class 10 ” Detail on BIND , commands, and utilities

Class 11 ” Execution

Class 12 ” Storage manager

Class 13 ” Edit and validation exits

Class 14 ” Entry from and exit to an application

Class 15 ” Installation-defined performance trace record

Class 16 ” Distributed processing

Class 17 ” Claim and drain information

Class 18 thru 19 ” Reserved

Class 20 and 21 ” Data sharing coherency summary (20) and detail (21)

Class 23 thru 29 ” Reserved

Class 30 thru 32 ” Local use

Estimated overhead : When all DB2 performance trace classes are active, as much as 100% CPU overhead can be incurred by each program being traced. The actual overhead might be greater if the system has a large amount of activity. Furthermore, due to the large number of trace records cut by the DB2 performance trace, systemwide (DB2 and non-DB2) performance might suffer because of possible SMF or GTF contention . The overhead when using only classes 1, 2, and 3, however, ranges from 20 to 30% rather than 100%

Statistics Trace

Information pertaining to the entire DB2 subsystem is recorded in statistics trace records. This information is particularly useful for measuring the activity and response of DB2 as a whole. Information on the utilization and status of the buffer pools, DB2 locking, DB2 logging, and DB2 storage is accumulated .

There are ten groups of DB2 statistics trace classes:

Class 1 ” Statistics data

Class 2 ” Installation-defined statistics record

Class 3 ” Data on deadlocks, lock escalation, group buffers, data set extension, long-running units of recovery, and active log shortage

Class 4 ” Exceptional conditions

Class 5 ” Data sharing statistics

Class 6 ” Storage usage

Class 7 ” Reserved

Class 8 ” Data set I/O

Class 9 thru 29 ” Reserved

Class 30 thru 32 ” Local use

Estimated overhead : An average of 2% CPU overhead per transaction.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net