Question
REXX and DB2: Intermittent Connection Pool Exhaustion Diagnosis
Ari Atlas
0 reputation · 02 Aug 2024, 07:35 UTC
125.3K views1
Loading…
0 reputation · 02 Aug 2024, 07:35 UTC
When a REXX program that connects to DB2 via DSNREXX or RRSAF terminates, the address space sometimes retains idle DB2 threads that are not reclaimed until the TSO session ends. The threads remain in an “in‑doubt” state if the script opens a WITH HOLD cursor and exits without an explicit COMMIT or ROLLBACK. The DSNREXX interface does not automatically commit on EXIT, so the thread can stay active for the duration of the address‑space idle timeout (IDTHTOIN). This behavior is documented in the DB2 manual under “Thread Reuse and Connection Management” and in the DSNREXX reference under “EXECSQL CONNECT”.
Because the thread exhaustion appears intermittently, it is unclear whether the root cause is the uncommitted WITH HOLD cursor, the DSNREXX thread‑reuse policy, or a combination of both. Determining the exact trigger is essential for designing a robust connection‑pooling strategy that avoids resource leaks.
To clarify the situation, I need to answer the following:
35275 reputation · 02 Aug 2024, 18:44 UTC
1. Does DSNREXX always leave the thread idle until IDTHTOIN expires after a REXX EXIT without COMMIT?
No. DSNREXX does not automatically commit on REXX EXIT, so an open unit of work can remain associated with the DB2 thread. However, the thread is not guaranteed to sit idle until IDTHTOIN expires; it may be reused within the same address space, terminated by DB2, ended at signoff, or subject to IDTHTOIN depending on the DSNREXX disconnect and thread-reuse behavior in your release.
2. Does a WITH HOLD cursor guarantee the thread is ineligible for reuse even after a COMMIT?
No. WITH HOLD preserves a cursor across COMMIT, but COMMIT ends the unit of work. Reuse eligibility is governed by the plan RELEASE option and DSNREXX thread-reuse settings, not by the cursor alone. Held cursors and locks can persist until cursor close or program termination.
3. What is the documented interaction between CMTSTAT=INACTIVE and RELEASE(COMMIT)?
There is no universally documented guarantee that this combination prevents exhaustion. RELEASE(COMMIT) generally releases thread resources at commit; CMTSTAT=INACTIVE, where supported, should keep DSNREXX from retaining an active thread. Neither closes WITH HOLD cursors, and neither replaces explicit COMMIT/ROLLBACK or DISCONNECT. Exact semantics vary by DB2 for z/OS release and PTF.
Confirmed facts: DSNREXX does not auto-commit on REXX EXIT; uncommitted work can keep a thread and its locks or cursors active; WITH HOLD preserves cursor state across COMMIT; IDTHTOIN is an idle-thread timeout, not an immediate reclaim mechanism.
Likely explanation: intermittent exhaustion is most consistent with scripts that exit while a unit of work is open, especially WITH HOLD, and rely on implicit disconnect or thread reuse. Threads accumulate only when address spaces stay active and reuse or timeout does not reclaim them before the next connection request.
rexxDISPLAY THREAD output. Correlate correlation IDs, plans, and status with active REXX or TSO address spaces. Look for open units of work and held cursors.COMMIT or ROLLBACK before REXX EXIT and DSNREXX DISCONNECT where supported, then repeat the load test and compare thread counts and reuse against baseline.To choose between explicit COMMIT/ROLLBACK plus DISCONNECT versus tuning IDTHTOIN or thread-reuse, confirm whether the threads observed during exhaustion have open units of work or WITH HOLD cursors and remain tied to active TSO address spaces after REXX EXIT.
Use comments to ask for clarification. Post a solution as an answer.
6,640 reputation · 02 Aug 2024, 19:15 UTC
When a REXX script terminates without an explicit COMMIT, DSNREXX does *not* generate an implicit commit. The DB2 thread stays in an idle state until the address‑space idle timeout (IDTHTOIN) expires or the thread is explicitly disconnected.
CMTSTAT=INACTIVE flag tells DSNREXX to return a thread to the pool after a COMMIT, but this is overridden if any WITH HOLD cursor is open.RELEASE(COMMIT) plan bind option frees most resources on commit. It does not affect a WITH HOLD cursor, so the thread remains tied until the cursor is closed.In practice, verify thread status with -DISPLAY THREAD and ensure cursors are closed in a RELEASE or ROLLBACK block to avoid idle‑thread buildup.