DB2 thread and connection monitoring

Instrumentation Facility and the thread

Accounting IFCID 3 is the thread’s scorecard. It is written when the thread ends, is reused, the authorization ID changes, or a DDF accounting interval closes. IFCID 239 repeats much of the story per package when accounting classes 7, 8, and 10 are on — that is how you see that the time is in package PAYROLL.FETCH not in the caller.

Identify the thread first: CONNID (CICS, TSO, UTILITY, SERVER), CORRID (CICS transid or job name), AUTHID, PLAN, LUWID/TOKEN. DISPLAY THREAD is the live list; accounting is the history for that identity.

CPU and elapsed time

Elapsed is wall-clock. CPU is processor time (TCB plus, where reported, SRB / specialty engines — read your monitor’s labels). Accounting classes split the story:

• Class 1 elapsed — start to accounting cut, including COBOL, CICS, and the network
• Class 2 elapsed — time the agent was in Db2
• Class 1 CPU vs class 2 CPU — application CPU versus Db2 CPU

Classic patterns:

• Class 1 elapsed high, class 2 low — not a Db2 SQL problem (chatty CICS, waits outside, slow stored-procedure WLM queue before SQL)
• Class 2 elapsed high, class 2 CPU high — Db2 is computing (bad access path, sort, RID processing)
• Class 2 elapsed high, class 2 CPU low — waiting; open class 3

On zIIP-eligible work (DDF, parallelism, some utilities), CPU may show on specialty engines. Elapsed can still be terrible if the thread waits on I/O even when general CP looks idle.

Getpages and buffer pool reads

A getpage is a request for a page from the buffer manager. It is the best single “how hard did this SQL hit the data” counter. It is not a disk I/O.

• Synchronous (random) reads — the page was not in the pool; the thread waited. This is the painful read.
• Sequential / list / dynamic prefetch reads — asynchronous; they fill the pool ahead of the scan. They still use disk and pool, but they do not stall the agent the same way.
• Immediate writes / vertical deferred write — the write side; usually less visible on a read-only transaction

Buffer pool hit ratio for a thread ≈ 1 − (sync reads / getpages). A scan can have an excellent prefetch hit ratio and still burn elapsed. Do not celebrate a 99 percent hit ratio on a query that did 50 million getpages — it still did 50 million getpages. Compare getpages per commit or per SQL call to a baseline.

Synchronous I/O wait

Class 3 synchronous I/O wait is elapsed spent in those random reads (and related sync writes). If this bucket owns the elapsed time:

• Access path became a matching index with terrible clustering (random getpages)
• Buffer pool too small for the working set
• Object on slow DASD or HyperPAV saturation (check RMF, not only Db2)

Prefetch wait is a different class 3 bucket: the scan ran faster than the I/O. That is a sequential problem, not “missing index” in the same way.

Lock wait, deadlock, timeout

Lock wait (class 3) is time waiting for a lock or often IRLM latch. Causes: long URs (missing COMMIT), tablespace locks, lock escalation, index hot spots on monotonically increasing keys, claim/drain behind a utility.

Deadlock (IFCID 172) is a cycle: A waits for B, B waits for A. Victim usually gets SQLCODE -911 and rollback. Timeout (IFCID 196) is waiting longer than IRLMRWT: -911 if rolled back, -913 if not. Monitoring: spikes in these IFCIDs in statistics class 3, plus accounting lock-wait for the surviving threads. DISPLAY BLOCKERS shows who holds the lock now; SMF tells you who waited then.

Sort activity

Accounting reports sorts performed, sort elapsed, and often whether work files were used. Sorts come from ORDER BY without a supporting index, GROUP BY, DISTINCT, merge join, nested loop with sort, and some subquery rewrites. A sudden sort after a rebind means the optimizer dropped an index order. Work-file (DSNDB07) explosion shows up as this thread’s sort plus statistics for the work-file buffer pool. Do not confuse a “sort” in EXPLAIN with zero sort in accounting — some sorts are avoided at run time.

Connections, DBATs, and reuse

Allied threads (CICS, IMS, TSO, batch) map cleanly to a connection. DDF is messier:

• Connection — TCP/IP session from a requester
• DBAT — the Db2 thread that runs the SQL
• Inactive — connection kept, DBAT returned to the pool

Accounting for a reused DBAT may cover many client units of work depending on CMTSTAT and accounting intervals. If one SERVER thread shows 10 minutes of CPU, it might be one monster SQL or a hundred small ones. Package-level 239 and the dynamic cache break that apart. DISPLAY DDF counts connections versus active DBATs versus MAXDBAT — when MAXDBAT is hit, new work queues and class 1 elapsed on the client explodes before class 2 on the DBAT even starts.

A short diagnosis path

1. Identify CONNID / CORRID / PLAN / package
2. Compare class 1 vs class 2 elapsed and CPU
3. If in Db2 and waiting, read class 3 (I/O vs lock vs drain vs log)
4. If in Db2 and CPU, read getpages, sync reads, sorts, SQL counts
5. Confirm live with DISPLAY THREAD / BLOCKERS / BUFFERPOOL

1
2
3
-DISPLAY THREAD(*) DETAIL
-DISPLAY BLOCKERS
-DISPLAY BUFFERPOOL(BP2) DETAIL

Explain It Like I'm Five

A thread is one kid doing a worksheet. Elapsed time is how long until they hand it in. CPU is how long their pencil was actually moving. Getpages are how many times they asked for a piece of paper from the shared drawer (the buffer pool). Synchronous I/O is walking to the cupboard because the drawer was empty. Lock wait is standing behind another kid who will not share the red crayon. A deadlock is two kids each holding the crayon the other needs. A timeout is giving up after waiting too long. A sort is dumping all the papers on the floor and putting them in order. Class 1 includes walking to the classroom; class 2 is only time at the desk.

Exercises

1. A CICS trans shows 2.0 s class 1 elapsed, 0.05 s class 2. Where do you look next?
2. Same trans, 1.8 s class 2 elapsed, 0.1 s class 2 CPU, large sync I/O wait. Name two likely causes.
3. Getpages jumped 100× after REBIND with no data growth. What statistic or EXPLAIN change do you suspect?
4. Distinguish SQLCODE -911 from -913 after a timeout for the application’s retry logic.
5. DISPLAY DDF shows connections near the maximum but few ACTIVE DBATs. Is that a thread problem or a connection-pool problem?