DB2 accounting and statistics monitoring

IFCID and the instrumentation facility

An IFCID (Instrumentation Facility Component Identifier, said “if-kid”) is one event record: “accounting for this agent,” “a deadlock happened,” “here are buffer pool stats.” Trace types (STAT, ACCTG, AUDIT, PERFM, MONITOR, GLOBAL) group classes, and each class is a bundle of IFCIDs. You rarely start IFCID 3 by number; you start TRACE(ACCTG) CLASS(1). Classes 30–32 are empty on purpose so you can start a custom IFCID list.

SMFPRMxx must allow types 100–102. If accounting is “on” in Db2 but SMF drops 101, your monitor is empty and the traces still cost CPU.

Statistics traces (SMF 100)

Statistics answer “how is the subsystem?” not “how is payroll.” Core IFCIDs:

• IFCID 1 — system services (log, storage manager, DDF system, latch)
• IFCID 2 — database services (buffer pools, SQL counts, RID, query parallelism)
• IFCID 202 — dynamic system parameters / buffer pool attributes
• IFCID 225 — storage summary (a favourite when MSTR is growing)
• IFCID 230 — data sharing global statistics

Many of these cut on a one-minute interval (independent of older STATIME behaviour for a subset of IFCIDs). Statistics class 1 is the usual always-on set. Class 3 adds deadlock/timeout related IFCIDs (172, 196, and others) — useful, not free. Default destination is SMF.

Use STAT traces for buffer pool monitoring (getpages vs sync reads vs prefetch), DDF system activity, and utility overlap at the subsystem level. They will not name the COBOL program.

Accounting traces (SMF 101)

Accounting starts when a thread is allocated and writes a complete record when the thread terminates, is reused, or the authid changes (and at DDF accounting intervals for long-lived DBATs). IFCID 3 is plan-level accounting. IFCID 239 is package-level overflow.

• Class 1 — required to write IFCID 3; elapsed and CPU including time outside Db2
• Class 2 — extra trace points summarized as in-Db2 elapsed and CPU
• Class 3 — wait buckets: synchronous I/O, lock/latch, drain, claim, log write, page latch, and more
• Class 7 / 8 / 10 — package CPU, package waits, and package detail (getpages, SQL) in IFCID 239. Class 7 alone is not the full package story after Db2 8

Class 2 and 3 do not write extra SMF types; they fill fields inside IFCID 3. That is why “turn on class 3” makes accounting richer without doubling SMF 101 volume the way a performance trace would.

Audit and performance traces (SMF 102)

Audit traces watch authorization failures, GRANTs, and access to tables with AUDIT attributes, often driven by audit policies. Overhead depends on how many tables and categories you enable; DEST is commonly SMF (type 102).

Performance traces are the microscope: scan, sort, mini-plan, I/O events. Default destination is often GTF because volume is high. Do not leave PERFM class 1–10 running over a weekend. Filter with PLAN, AUTHID, LOCATION. MONITOR traces feed online monitors through OP buffers (IFI) with a similar class model to accounting.

What to monitor with these records

Thread monitoring

Accounting IFCID 3 is the thread report: CONNID, CORRID, plan, CPU, elapsed, SQL counts. Pair it with DISPLAY THREAD for the live list. CICS threads that reuse can cut accounting on syncpoint — know your attach behaviour or you will under-count transactions.

Lock monitoring

Class 3 lock/latch wait time in accounting; IFCID 172 (deadlock) and 196 (timeout) in statistics/performance classes; DISPLAY BLOCKERS for now. A high class 3 lock wait with low CPU is not a “needs a faster CPU” problem.

Buffer pool monitoring

Statistics IFCID 2 (and 202) give pool-wide getpages, sync reads, VPSIZE. Accounting shows the thread’s getpages and sync I/O waits. RTS GETPAGES is per object since REORG. Use all three: pool too small (STAT), this SQL is a getpage hog (ACCTG), this tablespace is the hot object (RTS).

DDF monitoring

Statistics include DDF system counters; accounting for DBATs carries requester location, connection reuse, and SQL. DISPLAY DDF is the instant MAXDBAT picture. Inactive thread reuse can make accounting intervals surprising — one DBAT record may cover many client transactions depending on settings.

Utility monitoring

DISPLAY UTILITY for phase and STOPPED. Statistics show utility CPU and I/O at subsystem level. Accounting may show the batch allied thread that submitted DSNUTILB. Do not diagnose a REORG with only class 1 elapsed; look at the utility SYSPRINT and DISPLAY.

Stored procedure monitoring

Nested accounting and package-level IFCID 239 show time in the procedure package versus the caller. WLM delays show up as wait, not SQL. DISPLAY PROCEDURE (related DISPLAY family) shows stopped procedures; traces show the cost of the ones that ran.

Dynamic statement cache monitoring

Cache IFCIDs and monitor commands list statement text, bind time, execution count, getpages. Accounting says PLAN DISTSERV used 20 seconds; the cache says which prepared string did it. Stabilize or bind that SQL; do not just enlarge BP2.

Real-time statistics

Query SYSTABLESPACESTATS for REORGDELETES, REORGINSERTS, REORGUNCLUSTINS, GETPAGES, COPY-related counters. That is monitoring too — just SQL against the catalog, not SMF.

A practical always-on set

1
2
3
-START TRACE(STAT) CLASS(1) DEST(SMF)
-START TRACE(ACCTG) CLASS(1,2,3) DEST(SMF)
-START TRACE(ACCTG) CLASS(7,8,10) DEST(SMF)

Many sites set the equivalent in DSNZPARM (SMFSTAT, SMFACCT) so traces return after recycle. Add STAT class 3 or AUDIT policies by policy, not by panic. Performance traces get a change ticket and a STOP TRACE when the dump is enough.

Explain It Like I'm Five

Statistics are the school’s end-of-day count: how many lunches, how many times the library door opened. Accounting is each kid’s report: how long Sam sat in maths, how long Sam waited for a book. An IFCID is one kind of tick on the clipboard. SMF 100 is the school folder, SMF 101 is the pile of kid reports, SMF 102 is the extra cameras you only turn on when someone loses a shoe. DISPLAY is peeking in the window; traces are the folders you read tomorrow.

Exercises

1. Map IFCID 1, 2, 3, 225, and 239 to SMF types and to STAT versus ACCTG.
2. A transaction has high class 1 elapsed and low class 2 CPU. Which class 3 waits would you look at first?
3. Why might DISPLAY BUFFERPOOL look healthy while one package’s accounting shows huge sync reads?
4. Explain why performance trace to SMF 102 can flood the MAN data sets.
5. Write a SQL query idea against SYSTABLESPACESTATS to find objects with high GETPAGES since last REORG.