DB2 tracing

Trace types, classes, and IFCIDs

Hierarchy:

• Type — STAT, ACCTG, PERFM, AUDIT, MONITOR, GLOBAL
• Class — a numbered bundle of IFCIDs inside that type (accounting class 1 versus class 3 are different bundles)
• IFCID — one record layout (IFCID 3 accounting, IFCID 172 deadlock, IFCID 316 dynamic cache, …)

Starting a class starts every IFCID in that class. Starting the same IFCID in two classes does not write it twice. Classes 30, 31, and 32 are reserved empty so you can code IFCID(n,m,...) without dragging a whole performance class along.

START TRACE

1
2
3
4
5
-START TRACE(ACCTG) CLASS(1,2,3) DEST(SMF)
-START TRACE(STAT) CLASS(1) DEST(SMF)
-START TRACE(PERFM) CLASS(30) IFCID(22,63) DEST(GTF) PLAN(PAY01)
-START TRACE(AUDIT) DEST(SMF)
-START TRACE(MONITOR) CLASS(1,2,3) DEST(OPX)

Useful keywords:

• CLASS — which bundles
• IFCID — extra or only IFCIDs (with class 30–32 for IFCID-only)
• DEST — SMF, GTF, SRV, OP1–OP8, OPX
• PLAN, AUTHID, LOCATION, CONNID, CORRID, PKGPROG — include filters
• XPLAN, XAUTHID, … — exclude filters
• TDATA — extra headers (COR, CPU, DIST, TRA)
• SCOPE(LOCAL|GROUP) — data sharing
• COMMENT — shows up on DISPLAY TRACE so the next shift knows why
• AUDTPLCY — audit type with named audit policies (not with CLASS/IFCID)

DSNZPARM SMFSTAT and SMFACCT auto-start statistics and accounting classes at Db2 start so you do not rely on a forgotten START TRACE after IPL. User-started traces (including monitor products that START TRACE into OPX) need the same discipline as a change window.

STOP TRACE and MODIFY TRACE

DISPLAY TRACE(*) first. Note the TNO.

1
2
3
4
-DISPLAY TRACE(*)
-STOP TRACE(TNO(04))
-STOP TRACE(PERFM) DEST(GTF)
-MODIFY TRACE(PERFM) TNO(05) CLASS(30) IFCID(22,63,95)

STOP TRACE can qualify by type, class, dest, or TNO. If you omit DEST, destination is not used as a filter — you might stop more than the GTF experiment.MODIFY TRACE changes IFCIDs/classes on an active trace so monitors do not have to detach. You cannot invent a new destination with MODIFY; stop and start if you need GTF instead of SMF.

Accounting, statistics, performance, audit, global

• Statistics — interval, subsystem-wide, SMF 100. Leave class 1 on.
• Accounting — per thread, SMF 101. Class 1 on; 2 and 3 for waits; 7/8/10 for packages.
• Performance — deep IFCIDs (scans, sorts, mini-plans). Short windows, tight PLAN/AUTHID filters, GTF or a dedicated SMF class you can offload.
• Audit — who failed authorization, who read an AUDIT CHANGES table. Policies beat tracing every IFCID in the audit type.
• Monitor — same flavour as accounting classes but destined to OP buffers for OMPE, vendor monitors, or home-grown IFI readers.
• Global — internal service trace. Overhead can dwarf the workload. Start only when IBM asks, with a PMR number in COMMENT.

Trace destinations

SMF mapping: statistics IFCIDs 1, 2, 202, 225, 230 → type 100; accounting 3 and 239 → type 101; remaining IFCIDs → type 102. GTF must be active with the USR option that accepts Db2’s record id. OPX assigns the next free OP buffer; a monitor issues READA/READS against it. If the monitor dies and the buffer fills, traces can wrap or stall — DISPLAY TRACE still shows them as active.

Trace performance overhead

Cost is not one number. Rough field practice:

• STAT class 1 / ACCTG class 1 — built to run always; usually low single-digit percent and treated as the price of admission
• ACCTG class 2 and 3 — extra internal trace points on many Db2 paths; widely used in production; measure at your peak
• ACCTG 7/8/10 — per package; cost scales with SQL and package count
• PERFM — can be severe; every qualifying event writes a record. Unfiltered class 1–10 on a busy member can swamp SMF or GTF
• GLOBAL — service only

Reduce cost: filter PLAN/AUTHID/LOCATION, use class 30 + IFCID list, prefer GTF for a 10-minute burst, STOP when you have data, never stack duplicate START TRACE of the same classes. COMMENT every start so DISPLAY TRACE is readable at 3 a.m.

Trace interpretation

Raw SMF is not a report. You need:

• A monitor (OMPE, vendor) that formats IFCID 3 into class 1/2/3 times and wait buckets
• Or a batch reporter against unloaded SMF
• DSNWMSGS to decode a mystery IFCID number from a dump

Interpretation order: confirm the trace was on for the incident window (DISPLAY TRACE history is gone — rely on COMMENT and the SMF interval). Check you are looking at the right CONNID/CORRID. Then class 1 vs 2 vs 3, then getpages, then EXPLAIN. A performance trace of IFCID 22 (mini-plan) without accounting context is a pile of parts without the box.

Record layout is writer header (SMF vs GTF vs OP) + self-defining section + product section + data sections. You almost never parse that by hand unless you write an IFI program.

Explain It Like I'm Five

Tracing is putting cameras in the Db2 kitchen. Statistics cameras take one wide photo every minute of the whole kitchen (SMF 100). Accounting cameras follow each waiter with a stopwatch (SMF 101). Performance cameras film every stir of every pot — you only turn those on when the soup tastes wrong, and you point them at one recipe (PLAN) so you do not fill the memory card (GTF/SMF). START TRACE is turning a camera on, STOP is off, MODIFY is changing the lens while it rolls. DISPLAY TRACE is walking the hallway to see which cameras are still blinking. Global cameras are for the factory inspectors, not for dinner service.

Exercises

1. Write a START TRACE that captures only IFCID 172 and 196 to GTF for plan PAY01.
2. Explain what goes wrong if you STOP TRACE DEST(SMF) on a member that also needs accounting class 1.
3. Why might a monitor’s OPX trace disappear from DISPLAY TRACE after the monitor address space is cancelled?
4. Map STAT, ACCTG, and a typical PERFM IFCID to SMF types 100, 101, and 102.
5. List three filters you would add before starting PERFM classes during a production peak.