Reading DB2 performance metrics

Accounting versus Statistics

Accounting (IFCID 3, typically SMF 101) is about a thread: one CICS transaction, one batch job step, one DDF transaction (or a rolled-up set). Use it when “this program got slow.”

Statistics (IFCID 1 and 2, SMF 100) is about the subsystem: buffer pools, EDM, RID pool, DDF, SQL counts for everyone. Use it when “the LPAR feels busy” or a pool is mis-sized.

Package accounting (IFCID 239) splits class 2/3 style numbers per package. That is how you find which DBRM inside a big plan is the villain. Dynamic statement cache plus IFCID 318 (when enabled) attributes CPU and getpages to statement text.

Elapsed time and CPU consumption

Elapsed time is wall-clock. CPU is processor time. They answer different questions. Users feel elapsed. The bill often cares about general processor CPU; zIIP-eligible work (including much parallel query in recent Db2) can shift the bill.

• Class 1 — thread lifetime, including application time outside Db2 (COBOL between SQL calls)
• Class 2 — in-Db2 elapsed and in-Db2 CPU
• Class 3 — named waits inside Db2 (suspensions)

A useful identity:

1
2
3
4
class 2 elapsed
  ≈ class 2 CPU
  + total class 3 wait
  + not-accounted time

If class 2 CPU ≈ class 2 elapsed, the SQL is CPU-bound (big scan, scalar functions, sort). If class 3 synchronous I/O dominates, you have random I/O. If lock/latch wait dominates, you have contention. If not-accounted is huge and class 3 was not started, start class 3 before you redesign the database.

Compare class 1 elapsed to class 2: a large gap means the program is slow between SQL calls (application logic, MQ, file I/O), not inside Db2.

Getpages

A getpage is a request to the buffer manager for a 4K/8K/16K/32K page. Indexes and data both getpage. Getpages drive CPU (finding the page, latching, copying) even when the page is already in memory.

High getpages with a tablespace scan of a large table is expected. High getpages with MATCHCOLS = 0 and INDEXONLY = N can mean “index that does not filter.” Per-commit or per-transaction getpages are more meaningful than a raw total from an all-day batch.

Synchronous reads, asynchronous reads, and prefetch

When a getpage misses the pool, Db2 reads from DASD (or the group buffer pool in data sharing).

• Synchronous reads — the thread waits for that page (random access, typical singleton SELECT through an unclustered index). Class 3 “synchronous I/O” time tracks this wait
• Asynchronous reads / prefetch — sequential, dynamic, or list prefetch brings a bundle of pages before the thread needs them. Elapsed wait per page is much lower when prefetch works

Random I/O shows up as sync reads and sync I/O wait. Sequential I/O shows up as prefetch pages and (usually) less wait per page, but more pages transferred. Parallel index I/O (and query CP parallelism more generally) can issue multiple prefetch streams; Accounting may show parallel activity and class 3 “PQ synchronization” style waits when child tasks join.

A matching index on a well-clustered table often has modest sync I/O on the index tree plus sequential-looking data pages (dynamic prefetch). The same index on a disorganized table explodes sync reads unless list prefetch was chosen.

Buffer pool hit ratio

Hit ratio is the share of getpages that did not require a disk (or GBP) read. A common textbook form:

1
hit ratio ≈ 1 − (total pages read / getpages)

Use the counters your monitor labels for that pool (or for the thread’s buffer-pool block). Watch out: prefetch can read pages that are never touched, which makes “pages read” look worse than the application’s true miss rate. Still, a production OLTP pool with a collapsing hit ratio and rising sync I/O is a sizing or steal-threshold story (next tutorial section: buffer pools).

Do not chase 100% hit ratio on a warehouse scan of a table larger than the pool. Do chase a sudden drop on a 2-millisecond transaction that used to hit memory.

A simple diagnosis path

1. Filter Accounting Long to the authid / plan / time of the complaint.
2. Compare class 1 versus class 2 elapsed (in Db2 or in the app?).
3. Split class 2 into CPU versus class 3 waits versus not-accounted.
4. If sync I/O: look at getpages, sync reads, clustering, EXPLAIN PREFETCH.
5. If CPU: look at getpages, sorts, scalar functions, scans, parallelism degree.
6. If lock wait: commit frequency and lock size (next pages), not indexes first.

SQL that “got slower over the day” with rising lock or I/O wait is often data growth, disorganization, or contention—not a mysterious optimizer mood. Capture EXPLAIN for the same statement and put the two pictures side by side.

SQL performance monitoring

Practical sources, lightest first:

• Accounting Long by plan/package — always-on if classes 1–3 (and 7/8) are active
• Dynamic statement cache — DISPLAY THREAD / monitor cache, EXPLAIN STMTCACHE, IFCID 318
• Profile tables — start traces for a workload without tracing the whole subsystem
• Performance trace (IFCID 53, 58–66, 198, …) — statement text and getpage detail; overhead is real, use a window

Rank statements by CPU, getpages, and elapsed separately. The top CPU statement is not always the one users wait on (elapsed), and neither may be the one hammering DASD (sync reads).

Explain It Like I'm Five

Elapsed time is how long you waited for a sandwich. CPU is how long the cook’s hands were moving. Class 3 waits are “oven not ready,” “someone else using the knife,” “bread still in the storeroom.” Getpages are every time the cook opens the fridge. If the sandwich is already on the shelf (buffer pool hit), opening the fridge is quick. If they walk to the warehouse for every slice (synchronous read), lunch takes forever. Prefetch is bringing a whole tray of bread before you ask. Hit ratio is how often the fridge already had what you needed.

Exercises

1. On a sandbox Accounting report, write down class 1 elapsed, class 2 elapsed, class 2 CPU, and total class 3.
2. Compute not-accounted time. If it is large, check whether class 3 is on.
3. For one buffer pool, compute a hit ratio from getpages and pages read.
4. Compare sync reads for a clustered matching index query versus a tablespace scan of the same table.
5. Ask which Accounting classes your shop starts by default (1, 2, 3, 7, 8, 10).