kfogarty.com – Database Consultants

Under the direction of the IBM Support Center, the IFC Selective Dump (DSN1SDMP) utility can be used to:

• Force dumps when selected DB2 trace events occur.
• Write DB2 trace records to a user-defined MVS data set.

DSN1SDMP must be run via an MVS batch job, and be executed using the DSN TSO command processor. To execute DSN1SDMP, the DB2 subsystem has to be running.

The batch job will complete successfully only under the following conditions:

• The TRACE and any additional selection criteria started by DSN1SDMP meet the criteria specified in the FOR parameter.
• The TRACE started by DSN1SDMP is stopped using the STOP TRACE command.
• The job is canceled by the operator.

Sample JCL for using DSN1SDMP

//*
//STEP1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//*
//STEPLIB DD DSN=&DSN..SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SDMPPRNT DD SYSOUT=*
//SDMPTRAC DD DSN=&SYSUID..DSN1SDMP.TRACE.OUTPUT,
// UNIT=SYSDA,SPACE=(8192,(100,100)),
// DCB=(DSORG=PS,LRECL=8188,RECFM=VB,BLKSIZE=8192),
// DISP=(NEW,CATLG,CATLG)
//SDMPIN DD *
START TRACE(P) CLASS(32) IFCID(53,58) AUTHID(*) DEST(OPX)
FOR(1)
AFTER(1)
ACTION(ABENDTER(00E60188))
SELECT
* POSITION TO THE PRODUCT SECTION
P4,00
* INSURE QWHSIID = 58 OR 53 (NOT IFCID 4)
GE,04,X'0005'
* POSITION TO THE DATA SECTION 1
P4,08
* COMPARE SQLCODE IN QW0058SQ OR QW0053SQ
DR,74,X'FFFFFF98'
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(ssid)
RUN PROG(DSN1SDMP) PLAN(DSNEDCL)
/*

DSN1SDMP uses the following DD cards:

SDMPIN defines the control data set that specifies the input parameters to DSN1SDMP. This DD card is mandatory.
The LRECL of the file has to be 80, but only the first 72 columns are checked by DSN1SDMP.
SDMPPRNT Defines the sequential message data set used for DSN1SDMP messages. If the SDMPPRNT DD statement is omitted, no messages are written. The LRECL of SDMPPRNT is 131.
SYSABEND defines the data set to contain an ABEND dump in case DSN1SDMP abends. This DD card is optional.
SDMPTRAC defines the sequential DB2 trace record data set used for trace records returned to DSN1SDMP from DB2. This DD card is required only if trace data is written to an OPX trace destination. If the destination is anything other than an OPX buffer SDMPTRAC is ignored.

Trace records written to SDMPTRAC are of the same format as records written to SMF or GTF, except that, instead of containing the SMF or GTF headers, the SDMPTRAC trace records contain the monitor header (mapped by DSNDQWIW).

The DCB parameters are VB, BLKSIZE=32760, LRECL=32756.

SYSTSIN Defines the DSN commands to connect to DB2 and to execute an IFC selective dump:
DSN SYSTEM(subsystem name)
RUN PROG(DSN1SDMP) LIB('prefix.SDSNLOAD') PLAN(DSNEDCL)

The DB2 subsystem name must be filled in by the user. The DSN RUN command must specify a plan for which the user has execute authority.
DSN1SDMP dump does not execute the specified plan; the plan is only used to connect to DB2.

When no plan name is specified on the DSN RUN command, DSN defaults the plan name to the program. When DSN1SDMP is executed without a plan, DSN generates an error if no DSN1SDMP plan exists for which the user has execute authority.

Generating a Dump

All of the following must occur before DSN1SDMP generates a DB2 dump:

• DB2 produces a trace record that satisfies all of the selection criteria.
• An abend action (ABENDRET or ABENDTER) is specified.
• The AFTER and FOR conditions for the trace are satisfied.

If all of these three things occur, an 00E601xx abend occurs. xx is an integer between 1 and 99 that DB2 obtains from the user-specified value on the ACTION keyword.

Ensure that the generated 00E601xx abend is not suppressed by your system, by checking the contents of the SYS1.DAE dataset.

If DSN1SDMP must be stopped, use the DB2 STOP TRACE command.

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.

Estimated overhead: IBM states that the global trace can add 100 percent CPU overhead to your DB2 subsystem.

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 DB2 utilities

This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications.
Estimated overhead: Approximately 5 percent CPU overhead per transaction is added when all audit trace classes are started.

Using Audit Trace

If the site standard is to create tables with the AUDIT parameter, all audit trace classes should be started. If there are no audited tables, use the DSNZPARMs at DB2 startup to start only audit classes 1, 2 and 7 to audit authorization failures, DCL and utility execution. Aside from these types of processing, audit classes 1, 2 and 7 add no additional overhead. As must transactions do not result in authorization failures or issue GRANT, REVOKE or utilities, running these traces is cost-effective.

When a trace is started, DB2 formats records containing the requested information. After the information is prepared, it must be externalized. DB2 traces can be externalized to six destinations:

1. GTF GTF (Generalized Trace Facility) is a component of MVS and is used for storing large volumes of trace data.
2. RES RES is a wraparound table residing in memory.
3. SMF SMF (System Management Facility) is a source of data collection used by MVS to accumulate information and measurements. This destination is the most common for DB2 traces.
4. SRV SRV is a routine used primarily by IBM support personnel for servicing DB2.
5. OPn OPn (where n is a value from 1 to 8 ) is an output buffer area used by the Instrumentation Facility Interface (IFI).
6. OPX OPX is a generic output buffer. When used as a destination, OPX signals DB2 to assign the next available OPn buffer (where X is a number between 1 and 8 ).

The Instrumentation Facility Interface (IFI), which is a DB2 trace interface, enables DB2 programs to read, write, and create DB2 trace records and issue DB2 commands. Many online DB2 performance monitors are based on the IFI.

A synopsis of the available and recommended destinations for each DB2 trace type is provided in the following table. Y indicates that the specified trace destination is valid for the given type of trace; N indicates that it is not.

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 bufferpools, DB2 locking, DB2 logging, and DB2 storage is accumulated.

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

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 latches requested for the program
• Number of get page requests, by bufferpool, issued by the program
• Number of synchronous writes
• 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

Estimated overhead: DB2 accounting class 1 adds approximately 3 percent CPU overhead. DB2 accounting classes 1, 2, and 3 together add approximately 5 percent 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.

DB2 traces record diagnostic information describing particular events within a DB2 subsystem. As DB2 operates, trace information is written to various destinations. This trace information can be read and analyzed to obtain information on subsystem and application performance.

DB2 provides six types of traces, and each describes information about the DB2 environment. These six types of traces are outlined in the following table:

DB2 traces can be started in two ways; By specifying the appropriate DSNZPARM values 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) defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked by DB2.

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

Estimated overhead

When all DB2 performance trace classes are active, as much as 100 percent 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, system-wide (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 percent rather than 100 percent.

Using Performance Trace

Avoid using performance trace class 7 unless directed to do so by IBM.

Lock detail trace records are written when performance trace class 7 is activated. They can cause as much as a 100 percent increase in CPU overhead per program being traced.

An amalgamation of useful performance monitoring information is recorded by the DB2 monitor trace. Most of the information 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

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 percent, 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.