Diagnose DB2 SQLCODE -911

Diagnose SQLCODE -911

When DB2 returns -911, three facts matter immediately. First, the current unit of work is gone: uncommitted changes since the last commit were rolled back. Second, the cause was deadlock or timeout, not a syntax problem. Third, the reason code, resource type, and resource name tell you which story you are in. SQLSTATE 40001 marks the transaction-rollback class.

People often say "we deadlocked" whenever they see -911. That shorthand hides timeouts. A timeout means one holder kept a lock longer than the waiter was allowed to wait. A deadlock means two or more waiters formed a circle and IRLM broke the circle by choosing a victim. Both hurt availability. Only one is solved by consistent lock ordering alone.

Also keep -913 in mind. It reports deadlock or timeout without the same automatic unit-of-work rollback semantics as -911. Bind options and environment decide which code an application sees. Your diagnosis still starts with reason code and resources; your recovery code must know which SQLCODE your plans are configured to return.

Prerequisites

• SQLCODE -911 with SQLSTATE, reason code, resource type, and resource name
• Plan or package name, authid, connection name, correlation ID, and failure timestamp
• Access to SYSLOG or console history around that timestamp
• Authority for DISPLAY THREAD and DISPLAY DATABASE LOCKS / CLAIMERS when the wait is still live
• Application knowledge of the last commit point and whether controlled retries are allowed

Steps

Step 1: Freeze the tokens and stop unsafe processing

Capture the SQLCA fields and any printed message text. Do not assume later statements in the same logical business unit still have their database changes. After -911 they do not. If the program continues as if only one statement failed, you can create logic errors that are worse than the original lock wait.

1
2
3
4
5
6
7
8
9
Minimum capture for -911:
  SQLCODE / SQLSTATE
  reason-code (often in SQLERRD(3) and message text)
  resource-type
  resource-name
  plan / package / section if available
  correlation-id, connection-id, LUW-id
  member name (data sharing)
  timestamp

Step 2: Branch on deadlock versus timeout

If the reason is 00C90088, follow the deadlock playbook: identify both participants and the conflicting resource order. If the reason is 00C9008E, follow the timeout playbook: identify the long holder, the waiter, and why the lock was held past the timeout interval (long UOW, missing commit, utility drain, held cursor, or overloaded system).

Write the branch explicitly in the incident record. Mixing the playbooks leads to useless changes such as "increase timeout" for a true deadlock cycle, or "swap update order" when one batch job simply never commits.

Step 3: Gather SYSLOG and holder evidence

Search for DSNT376I and related messages in the failure window. Note the correlation IDs for the requestor and holder. In data sharing, note the member names. If users are still waiting, issue displays before the situation clears.

1
2
3
-DISPLAY THREAD(*) TYPE(ACTIVE)
-DISPLAY DATABASE(APPDB) SPACENAM(PAYTS) LOCKS
-DISPLAY DATABASE(APPDB) SPACENAM(PAYTS) CLAIMERS

When the failure already ended, DISPLAY may show nothing interesting. That is normal. Historical messages and monitor deadlock or timeout reports become authoritative. Save those outputs with the ticket so a later reviewer can replay your reasoning.

Step 4: Reconstruct the unit of work

List the SQL statements the victim performed since the last commit. Which tables and indexes did it touch? In which order? Do the same for the partner if you have SQL text or package section information. Deadlocks almost always reduce to incompatible orders or to a hot parent row serialized under high concurrency. Timeouts often reduce to one participant holding locks across too much work.

1
2
3
4
5
6
7
8
9
10
UOW reconstruction worksheet:
  Last successful COMMIT time: ...
  Statements since COMMIT:
    1) UPDATE APP.ACCOUNT ...
    2) UPDATE APP.ORDER ...
    3) INSERT APP.AUDIT ...
  Partner held:
    APP.ORDER (from DSNT376I)
  Suspected issue:
    opposite update order / missing intermediate COMMIT

Step 5: Apply the right fix and design a safe retry

For deadlocks, enforce one lock order across applications and shorten units of work. For timeouts, find and fix the long holder, add commits, reduce held-cursor duration, or adjust utility schedules. Only after measuring should you consider timeout parameter changes, and never as a substitute for a clear cycle.

If the application retries, retry the entire business unit after acknowledging rollback, use exponential or timed backoff, cap the number of attempts, and log every retry with the reason code. Continuing a cursor or assuming earlier inserts still exist after -911 is a defect.

Verify results

1. Confirm the incident is labeled deadlock or timeout using the reason code, not folklore.
2. Confirm victim and partner identities are recorded from message or monitor evidence.
3. After corrective changes, rerun an overlapping concurrency test and count -911 / -913 occurrences.
4. Verify application handling: on -911 it restarts from last commit and does not assume partial database progress.
5. Confirm operators know which DISPLAY commands to capture if the problem returns live.

Common errors

Raising IRLMRWT globally to "make -911 go away" often converts quick failures into longer hangs and can increase time-to-detect for true operational problems. Prefer evidence-based holder reduction and lock-order standards. Parameter changes belong in a capacity conversation after the application defects are understood.

Explain it like I'm 5

You and a friend both need two toys. Sometimes you each grab a different toy and wait for the other one forever until a teacher cancels your turn and makes you start over. That cancel is SQLCODE -911. Sometimes you just wait too long for one toy because a friend will not share. That is also -911, but with a different reason code. The important part is that when the teacher cancels your turn, none of the toys you thought you placed stay where you left them—you have to begin the game again from the last save point.

Exercises

1. Write pseudocode for a batch program that handles -911 with a maximum of three retries and full UOW restart.
2. Given reason 00C9008E, list five questions you would ask before changing any timeout parameter.
3. Compare application recovery after -911 with recovery after -913 in a short paragraph.
4. Using a fictional DSNT376I pair of correlation IDs, outline how you would map them to jobs or CICS transactions.
5. Create a one-page runbook for the on-call team titled "First 15 minutes of -911."