CICS and DB2: attachment, threads, and syncpoint

Why CICS needs an attachment to DB2

CICS and Db2 are separate address-space environments. CICS owns terminals, programs, and task control. Db2 owns tables, locks, and the log. The attachment facility lets a CICS task become an allied address space user of Db2: when your COBOL program runs EXEC SQL, the request crosses into Db2 under a thread that counts toward Db2's local thread limits (related to CTHREAD), not toward DDF's MAXDBAT pool.

You define the connection with CICS resource definitions—not with a Db2 CREATE statement. The three definitions you will hear constantly are:

• DB2CONN — the connection to a Db2 subsystem (or group attach name), pool and command thread limits, TCBLIMIT, purge cycle, and related defaults
• DB2ENTRY — a specialized entry-thread definition for one transaction or a group of transactions
• DB2TRAN — maps additional transactions onto an existing DB2ENTRY

Install DB2CONN before you start the connection. Do not confuse the resource with the CICS system initialization parameter also named DB2CONN, which only says whether CICS should start the Db2 connection automatically at initialization.

CICS attachment facility and DSNC

Once DB2CONN is installed and the connection is up, CICS transactions can use SQL. Operators and support staff use the DSNC transaction to work with the attachment: display statistics, understand thread usage, and manage operational views of the connection. Command threads defined on DB2CONN are reserved for Db2 commands issued through DSNC. If no command thread is free, those commands overflow to the pool.

1
2
3
4
/* Typical operational checks from a CICS terminal */
DSNC DISPLAY STATISTICS
DSNC DISPLAY PLAN
/* Exact subcommand spellings follow your CICS/Db2 guide and shop standards */

DSNC DISPLAY STATISTICS is especially useful: it shows activity by DB2ENTRY and for *POOL / *COMMAND, including SQL calls, auth checks, waits or pool overflows, high-water marks, aborts, and one-phase versus two-phase commit counts. That report is how you prove whether THREADLIMIT and PROTECTNUM settings match real traffic.

CICS threads: pool, entry, and protected

A CICS thread in this context is a Db2 thread tied to a CICS subtask TCB used for SQL. Pool threads are the general-purpose supply defined on DB2CONN (THREADLIMIT for the pool). New or low-priority transactions often live entirely in the pool. You only need DB2CONN for that model.

Entry threads reserve capacity for named transactions that need faster response, dedicated accounting, or special authorization attributes. You set how many of that type can exist at once (THREADLIMIT on the DB2ENTRY) and what happens when they are all busy:

Protected threads are entry threads with PROTECTNUM greater than zero. When a transaction finishes and no peer is waiting, a protected thread is not torn down immediately. It stays reusable for roughly two PURGECYCLE intervals from DB2CONN. The next matching transaction reuses the thread and avoids create/terminate cost. Unprotected entry threads terminate when released unless another waiter is already present.

Size carefully. The sum of THREADLIMIT values across DB2ENTRY definitions plus the pool THREADLIMIT plus COMTHREADLIMIT should not exceed what TCBLIMIT can support—threads without TCBs do not help. Across many CICS regions attached to one Db2, the sum of region TCBLIMIT values can exceed Db2 CTHREAD because idle protected threads and unused subtask TCBs are not all consuming a Db2 thread at once; still, peak concurrent SQL must fit inside CTHREAD.

DB2CONN and DB2ENTRY in practice

Think of DB2CONN as the region-wide contract with Db2: which subsystem or group attach name, how large the pool is, how many command threads exist, how long protected threads linger (PURGECYCLE), and related connection options. Think of DB2ENTRY as a VIP lane: which transaction IDs (or wildcards) use it, which plan name applies, authorization and priority relative to the CICS main TCB, and protect/overflow policy.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* Conceptual CEDA-style picture (names vary by shop) */
DB2CONN:
  DB2ID / GROUP attach name
  THREADLIMIT (pool)
  TCBLIMIT
  COMTHREADLIMIT
  PURGECYCLE
 
DB2ENTRY:
  TRANSID (or wildcard)
  PLAN
  THREADLIMIT
  PROTECTNUM
  THREADWAIT(YES|NO|POOL)
  AUTH / PRIORITY options as required
 
DB2TRAN:
  maps extra TRANSID values onto an existing DB2ENTRY

Modern advice for many new transactions is: start with the pool, bind packages with sensible RELEASE options, and add protected entry threads only where measurement shows allocate cost or contention. Thread-safe CICS–Db2 design (open TCB / L8-style paths) further reduces TCB switches; that is a CICS performance topic tightly coupled to this attachment.

CICS syncpoint and two-phase commit

A CICS task that updates Db2 should treat EXEC CICS SYNCPOINT (or normal task end) as the commit boundary—not a lone EXEC SQL COMMIT that fights the CICS unit of work. CICS is the syncpoint coordinator. Db2 is a resource manager. On syncpoint, CICS runs two-phase commit: prepare participants, then commit or back out so CICS recoverable resources and Db2 tables stay consistent.

If the coordinator or a participant fails between prepare and commit, units can go indoubt. CICS and Db2 recovery resolve them using logs and resynchronization. Operators use CICS and Db2 displays for indoubt threads; never casually FORCE a unit without understanding which side is in doubt.

Design units of work to be short. Long CICS tasks that hold Db2 locks across terminal waits create deadlocks and lock timeouts for everyone else.

Locking, deadlocks, and SQL from CICS

SQL from CICS uses the same Db2 locking rules as batch or DDF: isolation level, lock size, and commit frequency dominate. What is special about CICS is concurrency—hundreds of tasks may touch the same hot rows. Common patterns:

• Locking — prefer short UOWs; avoid SELECT then update much later without releasing; understand ISOLATION(CS) versus RR for online paths
• Deadlocks — two transactions update tables in opposite order; one gets SQLCODE -911 / -913 style failures. Fix ordering, indexing, and commit scope
• SQL — same embedded SQL as elsewhere; host variables, cursors, and SQLCA/SQLCODE handling live in the CICS program

Deadlock victims often retry. Build idempotent retry logic and never assume the first attempt committed.

Packages, plans, and performance

CICS programs are precompiled and bound like other Db2 applications. You typically bind packages into collections and reference them from a plan named on DB2CONN or DB2ENTRY. Thread reuse prefers stable plan names: if overflow to the pool carries a different plan than the pool thread last used, reuse may break and you pay create cost again.

Performance levers on the CICS side include THREADLIMIT, PROTECTNUM, TCBLIMIT, whether work stays on open TCBs, and purge cycle length. On the Db2 side you still care about buffer pools, indexes, access paths, and RELEASE(COMMIT) versus RELEASE(DEALLOCATE) for protected threads that intentionally keep resources across transactions. Measure with DSNC statistics, CICS monitoring, and Db2 accounting (SMF) before guessing.

Recovery and troubleshooting

Recovery spans both products: CICS dynamic transaction backout, Db2 rollback for the unit of recovery, and indoubt resolution after failures. Image copies and Db2 logs still protect table spaces; CICS journals protect CICS resources. Practice failure drills on a test region so operators know which display to trust.

Troubleshooting checklist when “CICS cannot talk to Db2”:

1. Is DB2CONN installed and the connection started?
2. Is the Db2 subsystem (or group) up and accepting allied threads?
3. Do DSNC displays show pool/entry exhaustion or abends?
4. Are plan/package EXECUTE privileges correct for the authid in use?
5. Are SQLCODEs application bugs, lock timeouts, or attachment limits?
6. After an outage, are indoubt units clearing normally?

1
2
3
4
5
6
7
8
9
10
11
12
      * CICS program sketch: SQL inside a syncpoint scope
           EXEC SQL
                UPDATE ACCT
                   SET BALANCE = :WS-BAL
                 WHERE ACCT_NO = :WS-ACCT
           END-EXEC
           IF SQLCODE NOT = 0
              MOVE 'DB2UPD' TO WS-ERR
              EXEC CICS SYNCPOINT ROLLBACK END-EXEC
              ...
           END-IF
           EXEC CICS SYNCPOINT END-EXEC

Authorization and accounting under CICS threads

Every SQL call still needs a Db2 authorization ID. On the CICS attachment you choose how that ID is derived: the end user, the transaction ID, a secondary authid strategy, or a general-purpose attachment ID depending on DB2ENTRY and DB2CONN options. Wrong choices create either “everyone shares one powerful ID” (too coarse for audit) or “every terminal user must be GRANTed individually” (operational pain). Most shops use a controlled service identity for the region plus roles or secondary IDs for application packages.

Accounting ties to plan and package names and to how threads are reused. If you care about chargeback per transaction, entry threads with stable plan names make SMF accounting clearer than a noisy pool where many TRANSID values share one thread type. Protected threads with RELEASE(DEALLOCATE) keep package resources across transactions, which is fast but can blur per-transaction accounting—know what your finance and capacity teams expect before you copy a tuning tip from another site.

Thread safety and open TCB paths

Older CICS–Db2 designs forced SQL onto a limited set of TCBs and paid for TCB switches. Modern CICS threadsafe programs can run Db2 requests on open API TCBs (commonly discussed as L8-style paths) when definitions and program attributes allow it. That reduces switches and improves CPU for high-volume transactions, but only when the application is truly threadsafe and the CICS–Db2 definitions match. Non-threadsafe programs, QUASIREnt modules, or incorrect CONCURRENCY settings push work back onto constrained paths and erase the benefit.

Treat thread safety as a joint CICS and Db2 project: review the program, the CICS program definition, the attachment, and the Db2 package bind. A protected entry thread on a non-threadsafe transaction is still useful for reuse, but you will not get the full open-TCB story until the application stack is clean.

Capacity planning across many CICS regions

Large sites attach dozens of CICS regions to one Db2 subsystem or data sharing group. Each region brings its own DB2CONN THREADLIMIT and TCBLIMIT. Peak concurrency is not the sum of every limit—protected idle threads and unused TCBs do not all hold Db2 threads—but a Monday morning surge can still approach CTHREAD. Plan with measured high-water marks from DSNC and -DISPLAY THREAD, not with spreadsheet optimism.

In data sharing, remember that locking and GBP interest add cost beyond a single-member mental model. A CICS region pinned to one member may look fine until affinity breaks and the same hot row is updated through another member. Coordinate affinity, package binds, and deadlock retry standards across the group.

Explain It Like I'm Five

Imagine CICS is a busy restaurant dining room and Db2 is the kitchen. The attachment is the set of waiters (threads) who carry orders. Pool waiters serve anyone. Entry waiters are reserved for VIP tables. Protected waiters hang around near a VIP table for a few minutes so the next guest does not wait for a brand-new waiter. DSNC is the clipboard the manager uses to see how many waiters are busy. When everyone finishes dessert, the manager rings a bell (syncpoint) so the kitchen and the dining room agree the meal is done—or undo the whole meal together if something spilled.

Exercises

1. List what belongs in DB2CONN versus DB2ENTRY for a high-volume PAY1 transaction.
2. Explain when you would choose THREADWAIT(POOL) instead of THREADWAIT(YES).
3. Why can the sum of CICS TCBLIMIT values exceed Db2 CTHREAD without being “wrong”?
4. Describe what two-phase commit protects when a CICS task updates Db2 and a recoverable VSAM file.
5. Using a DSNC STATISTICS mental model, what metric would rise if PROTECTNUM is too low for a busy entry?