DB2 DDF threads and connection pooling

DBAT versus connection versus allied thread

Three words get mixed in every war room:

A DBAT is created for DRDA work. It runs under a WLM-managed preemptible independent enclave SRB in DIST—zIIP-eligible, interruptible like a TCB, reported as one transaction even if work spans DIST and DBM1. When the client is between transactions, you want the expensive thread back in a pool and the cheap socket left waiting.

An allied thread is the local twin: CICS, IMS, TSO, batch, CAF, or RRSAF. Those address spaces attach through a language interface, not through DIST.CTHREAD caps allied threads. MAXDBAT caps DBATs. IBM requires CTHREAD + MAXDBAT not to exceed the documented combined maximum (20000 in current manuals). Raising MAXDBAT without watching allied demand still exhausts the same overall budget.

CMTSTAT and the DBAT pool

Subsystem parameter CMTSTAT is the big switch:

• INACTIVE (default, recommended) — at COMMIT, if the DBAT is poolable, DDF pools it, can write accounting, deletes the current WLM enclave, and makes the connection inactive. The next SQL on that connection associates a pooled or new DBAT.
• ACTIVE — the DBAT stays glued to the connection until the client disconnects. You get almost no pooling. CONDBAT high-water marks collapse toward MAXDBAT. Use this only if you have a documented reason.

A DBAT is not poolable at commit if it still owns:

• A cursor declared WITH HOLD that is still open
• Packages using KEEPDYNAMIC(YES)
• A held LOB locator
• A declared global temporary table that was not dropped

Those are legitimate designs; they just mean each such session occupies a DBAT for its life, so MAXDBAT must cover them plus everyone else.

MAXDBAT, CONDBAT, POOLINAC

If MAXDBAT is reached, new transactions queue (they still hold connections up to CONDBAT). DISPLAY DDF DETAIL shows queued requests. If CONDBAT is reached—or CONDBAT is 0—the connect is rejected. MAXDBAT=0 also forces CONDBAT to 0 and refuses DDF SQL. Set MAXDBAT high enough for peaks, low enough that a runaway pool cannot drown DBM1. Queuing for a second is better than a frozen subsystem.

POOLINAC trims the pool after a surge. A pooled DBAT unused for that many seconds is eligible to be terminated on Db2’s purge cycle. A very large POOLINAC leaves DBATs sitting around after the rush; the default 120 seconds is a sound start. IDTHTOIN does not apply to pooled DBATs—only to threads that are in use and idle (client thinking, dropped laptop, forgotten WITH HOLD cursor).

CONDBAT must be at least the sum of client pool sizes that can connect at once. A dozen application servers each opening 200 connections is 2400 CONDBAT before you count anything else.

High-performance DBATs

A high-performance DBAT is an optimization, not a third thread type. Ingredients:

• CMTSTAT=INACTIVE
• MODIFY DDF PKGREL(BNDOPT) or BNDPOOL
• The connection used at least one package bound RELEASE(DEALLOCATE)

RELEASE(DEALLOCATE) avoids repeated package allocation. Combined with HP DBATs, that allocation can last for the life of the thread. BNDOPT deallocates the HP DBAT when the connection ends. BNDPOOL can put that DBAT back in the pool for another connection. PKGREL(COMMIT) forces RELEASE(COMMIT) behavior even if the package said DEALLOCATE.

The risk: HP DBATs occupy MAXDBAT longer and can starve ordinary pooled work. Do not bind every package DEALLOCATE, and do not size the client pool as if threads were free. John Campbell’s usual warning is: implement HP DBATs on purpose, watch DISPLAY DDF, and keep a MAXDBAT cushion.

Protected threads (CICS) and allied reuse

In CICS, a protected thread stays allocated to a DB2ENTRY so the next transaction with that entry reuses it. That is allied-thread reuse, analogous in spirit to pooling but a different attachment. Too many protected threads plus a busy DDF system compete for the same Db2 engine (CTHREAD + MAXDBAT, EDM, locks, CPU).

IMS and RRSAF have their own reuse stories (PSB schedule, stored-procedure WLM environments). When you capacity-plan “threads,” always split allied versus DBAT instead of quoting one number.

Client connection pooling

JDBC pools should be sized to concurrent transactions, with a modest spare, not to total logged-in users. Each pooled connection is an inactive DDF connection most of the time (good) but still a CONDBAT slot (not free). Test:

• Pool max ≤ what CONDBAT can absorb from all farms
• Idle timeout on the client shorter than operational panic, aligned with POOLINAC
• Validation queries that do not leave WITH HOLD cursors open
• Trusted connections if you must switch end-user ids on a reused socket

What to DISPLAY

1
2
3
4
-DISPLAY DDF DETAIL
-DISPLAY THREAD (*) DETAIL
-DISPLAY THREAD (*) TYPE(INACTIVE)
-MODIFY DDF PKGREL(BNDOPT)

Look for active DBATs, pooled DBATs, inactive connections, and queued requests. If queued stays non-zero, either MAXDBAT is too low for a healthy peak or SQL is too slow (threads held). If inactive connections sit near CONDBAT, shrink client pools. If pooled DBATs never shrink after a peak, check POOLINAC.

Explain It Like I'm Five

The library phone line is a connection (CONDBAT: how many phones can be off the hook). A DBAT is an actual librarian who walks to the stacks (MAXDBAT: how many librarians). When you hang up politely but leave the line open for the next question (inactive connection), the librarian can help someone else (pooled DBAT). If you keep a book on the desk (WITH HOLD cursor), that librarian cannot go help yet. Allied threads are people who already live in the building (CICS) and never called the front desk. Protected CICS threads are a librarian assigned to one classroom for the whole morning. High-performance DBATs are librarians who keep their cart packed between questions so they start faster—and therefore stay busy longer.

Exercises

1. On a test system, read DISPLAY DDF DETAIL and identify active DBATs, pooled DBATs, and inactive connections.
2. Why can CONDBAT be 10000 while MAXDBAT is 200, and why is that usually a good idea?
3. List four reasons a DBAT would not return to the pool at COMMIT.
4. Contrast a CICS protected thread with a pooled DBAT in one short paragraph.
5. Your WAS pool is 300 per clone and you have 8 clones. What CONDBAT conversation do you have with the DBA?