DB2 concurrency and commit tuning

The unit of work is the unit of locking

Locks and claims last until COMMIT or ROLLBACK (with exceptions: WITH HOLD cursors, LOCK TABLE, some LOB locators). Isolation (CS, RS, RR, UR) and lock size (row, page, table, table space) decide how many other threads you block. Accounting class 3 lock wait is the metric; SQLCODE -911 / -913 is the user-facing failure.

Commit frequency

OLTP: commit once per business transaction. Nested SQL in a stored procedure still belongs to the caller’s unit of work unless you designed autonomous work.

Batch: commit every N rows (or every N minutes of work) so:

• Other jobs can get to the same keys
• You have a restart point after -911
• Log writes stay paced (too-frequent commit can increase log I/O and CPU)

Too rare: timeouts, deadlock victims, huge rollback. Too often: commit-path CPU, extra log force, and slower elapsed. Tune N with Accounting lock wait versus CPU, not with superstition.

Distributed applications must commit even when they only SELECT. Otherwise the DBAT looks in-flight forever, hits IDTHTOIN idle timeout, or sits on MAXDBAT until the subsystem wheezes.

1
2
3
4
-- Batch pattern (host program issues COMMIT every 500 rows)
UPDATE HR.EMPLOYEE
   SET BONUS = BONUS * 1.02
 WHERE EMPNO = :HV-EMP;

Thread reuse

CICS and IMS

A protected thread (CICS DB2ENTRY THREADLimit / PROTECT) stays allocated across transactions. Bind with RELEASE(DEALLOCATE) so packages stay in the EDM until the thread goes away—less allocation CPU, longer-lived parent locks. Use it for high-rate, short transactions. Do not combine it with transactions that never commit.

DDF DBATs

With CMTSTAT=INACTIVE (recommended), a clean commit sends the DBAT back to the pool and the connection can go inactive. The next transaction on that connection (or another) reuses a pooled DBAT. POOLINAC is how long an unused pooled DBAT lives (default often 120 seconds). Low reuse rates (< 95%) often mean POOLINAC is short or commits are not clean.

High-performance DBATs form when the DBAT runs a package bound RELEASE(DEALLOCATE). The DBAT stays glued to that connection across commits until a reuse limit (on the order of hundreds of transactions). CPU savings can be 10%+ on skinny SQL. Costs: memory growth, locks held across commits, harder to REBIND/DDL. Target chatty clients that commit hundreds of times per connection with steady arrival rate—not connect-once-a-day reports. MODIFY DDF PKGREL(COMMIT) is the emergency “stop HP DBATs” switch.

Pooling fails when WITH HOLD cursors stay open, declared temp tables are not dropped, KEEPDYNAMIC keeps state, or there is no commit. Statistics and IFCID 411/412 style reports help find “rogue” applications that never go inactive.

Connection pooling

Application servers pool connections (sockets). Db2 pools threads (DBATs) separately. Limits:

• CONDBAT — max remote connections
• MAXDBAT — max active DBATs
• Client pool size should not assume each connection has a dedicated DBAT when INACTIVE pooling works

A huge client pool of idle connections still counts against CONDBAT. Idle active threads count against MAXDBAT and IDTHTOIN. Profile tables (DSN_PROFILE_TABLE) can set connection and idle-thread limits per IP, authid, or application name without changing global ZPARMs.

Lock contention

Contention is time in class 3 lock/latch wait, plus page latch, drain, and in data sharing global contention / notify messages. Causes:

• Long units of work
• Hot rows (one account updated by everyone)
• Tablespace scans taking intent-exclusive or worse for longer than an indexed touch
• LOCK TABLE, RR isolation, or unclustered mass update in key order that fights inserts
• False contention in data sharing (hash collisions)—a sysprog topic, but it looks like lock wait

NUMLKTS / NUMLKUS and LOCKMAX control escalation to a bigger lock. Escalation reduces lock-table memory and can explode contention. Track escalations on Accounting; if they spike, commit more often or lock fewer rows (better access path), do not only raise the limit.

Deadlocks and timeouts

DEADLOK is how often IRLM searches for cycles. IRLMRWT (and CURRENT LOCK TIMEOUT) is how long a waiter waits before -911/-913. Deadlock victims and timeout counts belong on your weekly report.

• Always lock resources in the same order in every program
• Keep transactions short
• Retry -911 in the application with backoff
• Use IFCID 172 / 196 (and your monitor’s deadlock trace) to see the two plans involved

Raising IRLMRWT from 30 to 600 seconds makes users wait ten minutes instead of failing fast. Prefer failing fast plus shorter commits unless you have a measured batch that needs a longer wait for a nightly exclusive.

Putting it together

1. If lock wait dominates Accounting, draw the unit of work: when is COMMIT issued?
2. If DDF MAXDBAT is full, look for missing commits, HP DBATs, and IDTHTOIN.
3. If CICS CPU is high on simple SQL, look at unprotected threads and RELEASE(COMMIT).
4. If deadlocks cluster on two tables, fix update order—not only indexes.

Explain It Like I'm Five

A lock is borrowing a crayon. Commit is putting the crayon back so the next kid can use it. If you color for an hour without putting it back, everyone else waits (timeout) or two kids each hold a crayon the other needs (deadlock). Thread reuse is keeping the same desk for the next coloring session so you do not rebuild the desk every time. Connection pooling is the line of kids already in the classroom. High-performance DBATs are taping the crayon to one kid’s wrist for 200 pictures—faster for that kid, annoying for everyone who needed that color.

Exercises

1. Find CMTSTAT, MAXDBAT, CONDBAT, POOLINAC, and IDTHTOIN on your subsystem.
2. On a Statistics DDF section, compute DBAT reuse rate (reuse / (create + reuse)).
3. Identify whether a CICS transaction issues COMMIT and whether the package is RELEASE(DEALLOCATE).
4. From Accounting, compare lock wait for a program before and after cutting batch commit interval in half (sandbox).
5. Read a deadlock report and list the two lock resources and the SQLCODEs.