IMS and DB2: attachment, regions, and syncpoint

IMS attachment facility overview

The IMS attachment facility receives and interprets requests for Db2 access using exit routines that are part of the IMS subsystem. IMS usually connects to Db2 automatically with little operator intervention once the external subsystem is defined. From the application view, you add EXEC SQL (or equivalent) after precompile and bind, and you keep using IMS synchronization calls for commit and abort.

Db2 provides database services for IMS dependent regions. DL/I batch support extends the same idea to batch: authorized programs can touch IMS data and Db2 data in one job with coordinated recovery. The attachment handles the two-phase commit protocol so a restart after failure can bring both sides back to a consistent unit of recovery.

Even when runtime traffic is 100% IMS, you still need the TSO attachment and ISPF for many Db2 administrative tasks: install customization, binds, and online Db2 tools. IMS does not replace those operational paths.

IMS DB/DC, batch, BMP, and MPP

IMS DB/DC is the classic online stack: IMS databases plus data communications (terminals, messages, transactions). Online programs in MPP regions pull messages, run business logic, and may update DL/I and Db2 before the next message boundary.

BMP regions blur batch and online. A transaction-oriented BMP can process messages somewhat like an MPP. A batch-oriented BMP runs through a large volume of database work under IMS control without being driven solely by the message queue. Both can use Db2 if the attachment is configured.

IMS batch (DL/I batch) jobs use JOBLIB/STEPLIB to reach Db2 load libraries so attachment modules can load. Optional DD statements such as DDOTV02 can receive attachment messages about indoubt threads and diagnostics—follow your install guide for exact ddnames.

IMS message processing and Db2 transactions

An IMS transaction is not identical to a Db2 thread, but they meet in the unit of work. The program retrieves a message, runs SQL and DL/I, then reaches a syncpoint. Locks in Db2 remain until that syncpoint just as they would until COMMIT in a TSO program—except IMS decides when the syncpoint happens.

For single-mode MPPs, getting the next message (GU) often establishes the commit point for the previous message's work. Issuing a basic CHKP in an MPP can behave like asking for another message, which surprises developers who expected a quiet mid-transaction commit. Multiple-mode programs rely more on explicit checkpoint calls and normal termination for commit points. Always match checkpoint design to the program's processing mode.

1
2
3
4
5
/* Conceptual flow in a message-driven IMS + Db2 program */
1. GU  -> get input message  (may commit prior UOW in single-mode)
2. DL/I calls + EXEC SQL against Db2
3. Next GU / CHKP / normal end -> IMS syncpoint
4. IMS coordinates 2PC with Db2 (and other external subsystems)

IMS syncpoint and two-phase commit

When the application issues an IMS synchronization call such as CHKP, or when message-driven commit rules fire, IMS is the coordinator. IMS tells Db2 to make its changes permanent as part of the same unit of recovery. Db2 does not need a separate application-level COMMIT for that coordinated path; in fact, unmanaged SQL COMMIT can fight the design.

On CHKP with Db2 present, IMS processes the checkpoint for DL/I databases and Db2 commits Db2 resources. Checkpoint positioning details for Db2 are not recorded the same way as DL/I checkpoint data areas—if you need application restart state for relational keys, put that information in areas included with a symbolic checkpoint or in your own restart table.

Two-phase commit means prepare then commit (or rollback). If the system fails between phases, a thread can be indoubt. The attachment and IMS recovery paths resolve those units. Batch programs that use XRST (extended restart) obtain checkpoint information and help resolve indoubt Db2 work as part of restart.

Batch warning from IBM guidance: if you omit a checkpoint before program end, Db2 may commit while IMS has not yet committed DL/I in a failure window—or the reverse—leaving data out of sync. Always end with a proper synchronization design. Incorrect CHKP, ROLB, ROLL, or XRST status codes can cause Db2 to abend the application; test sync calls before production.

IMS Db2 connection, DSNAIMS, and commands

Connection setup is an IMS external subsystem definition problem as much as a Db2 one. The subsystem member (SSM), PROCLIB, and attachment modules wire IMS to a Db2 subsystem or group. Names such as DSNAIMS and DSNAIMS2 appear in IBM documentation and load libraries as part of that attachment path. Your systems programmer sets the SSM entries; application teams usually inherit a working connection and focus on binds and SQL.

IMS commands (for example displays of active regions, stops, and starts) remain the operational language for the IMS side. Db2 commands (-DISPLAY THREAD, and so on) show the allied threads from the Db2 side. Troubleshooting almost always means looking at both consoles.

1
2
3
4
5
6
7
/* Operational idea (exact command forms vary by IMS release/shop) */
/* IMS: display active regions / transactions */
/* Db2: -DISPLAY THREAD(*) DETAIL  -- look for IMS allied threads */
 
/* Application bind still required */
BIND PACKAGE (IMSPKG) MEMBER(myprog) ...
BIND PLAN (IMSPLAN) PKLIST(IMSPKG.*) ...

Recovery and troubleshooting

Recovery uses the IMS log for IMS commit instants and Db2 logs for relational undo/redo. Coordinated backout returns both products to the last shared commit point after an application abend. After subsystem failures, follow IMS and Db2 restart procedures; watch for indoubt threads and resolve them with documented tools rather than ad-hoc updates.

Troubleshooting patterns:

• SQL works in SPUFI but fails in MPP — check plan/package grants for the IMS authid, collection, and whether the region is actually attached to the intended Db2
• Locks pile up — commit frequency too low in BMP/batch; add CHKP cadence
• Hang after CHKP in MPP — CHKP may be waiting for the next message; confirm processing mode and whether you meant a quiet commit
• Indoubt after outage — display threads on Db2, follow IMS recovery, do not FORCE casually
• Batch abend on sync call — invalid CHKP/ROLB/ROLL/XRST status; fix the DL/I call before blaming Db2 SQL

1
2
3
4
5
6
7
8
9
10
      * Sketch: SQL inside an IMS-managed unit of work
           EXEC SQL
                INSERT INTO ORD_HIST
                     (ORD_NO, STATUS)
                VALUES (:WS-ORD, :WS-STAT)
           END-EXEC
           IF SQLCODE NOT = 0
              ... ROLB / error path per shop standard ...
           END-IF
      * Commit via IMS CHKP / message boundary -- not a lone SQL COMMIT

Plans, packages, and region startup

IMS programs that use SQL still go through precompile (or the Db2 coprocessor), compile, link-edit, and bind. The plan or package collection must be executable for the authorization ID that the IMS region presents to Db2. A common support ticket is “it works in batch TSO under my ID but fails in the MPP”—almost always an authid, collection, or PLAN name mismatch, not a mysterious IMS bug.

Dependent regions need access to Db2 load libraries at startup. If STEPLIB is wrong, the region may come up for DL/I work and only fail on the first SQL call. Capture the first SQLCODE and the attachment messages; they usually name the missing module or the failed identify/sign-on to Db2. Coordinate region PROCs with the Db2 systems programmer whenever you migrate Db2 releases so SDSNLOAD levels stay matched.

Locks, GSAM, and mixed resource design

Holding Db2 locks across a long BMP without checkpoints is a classic outage pattern. Checkpoint often enough to release locks and to create restart points, but not so often that checkpoint overhead dominates. For programs that also use GSAM or z/OS files, remember that IMS symbolic checkpoint support and Db2 commit are coordinated for the database side—you may still need your own strategy for non-IMS files. IBM documents that symbolic checkpoint areas can carry application restart data; use them for keys and counters you must restore after XRST.

Deadlocks between IMS DL/I and Db2 resources are less about a single lock manager and more about application ordering: update Db2 then DL/I in one program while another does the reverse. Standardize access order in design reviews the same way you would for two Db2 tables.

Other external subsystems beside Db2

Many IMS systems also attach MQ or other external subsystems. Two-phase commit then includes more participants. A hang on syncpoint may be Db2, MQ, or IMS itself waiting on a response. When troubleshooting, display all external subsystem connections, not only Db2 threads. Document which subsystems each transaction touches so on-call staff know the full syncpoint set.

Performance isolation matters too: a Db2-heavy BMP can starve message regions if they share constrained WLM or CPU resources. Classify IMS and Db2 work appropriately in WLM service classes so online MPPs keep priority over large batch BMPs that happen to use SQL.

Comparing IMS–Db2 to CICS–Db2

Both CICS and IMS act as syncpoint coordinators with Db2 as a resource manager, but the day-to-day vocabulary differs. CICS talks about DB2CONN, entry threads, and EXEC CICS SYNCPOINT. IMS talks about SSM definitions, dependent regions, GU, and CHKP. Thread pooling concepts that dominate CICS tuning are less central in IMS; region and transaction scheduling inside IMS matter more. If you support both stacks, keep runbooks separate so operators do not apply DSNC advice to an MPP outage.

Application portable SQL is still portable—the same SELECT works—but commit discipline is not. A program copied from CICS that issues EXEC SQL COMMIT inside an IMS UOW is a defect waiting for production. Train developers on the coordinator for the environment they ship to, and code review syncpoint APIs as carefully as you review SQLCODEs.

Explain It Like I'm Five

IMS is a post office that delivers messages to workers (MPP/BMP programs). Db2 is a filing cabinet of tables. The attachment is the hallway that lets a worker open both the mail bag and the filing cabinet in one job. When the worker finishes a letter, the post office rings a bell (syncpoint). Everyone agrees together: stamp the mail done and lock the files—or put both back like nothing happened. DSNAIMS is part of the hallway wiring. If the power fails mid-bell, the adults (operators) check both buildings for packages stuck “maybe done” (indoubt) and finish the job carefully.

Exercises

1. Compare commit triggers for a single-mode MPP versus a batch-oriented BMP that uses Db2.
2. Why is IMS the syncpoint coordinator instead of Db2 in this architecture?
3. List two things you would check if an MPP gets SQLCODE -805 or -551 but SPUFI works.
4. Explain why XRST matters for a DL/I batch program that updates Db2.
5. Draft a short runbook line for “indoubt IMS–Db2 thread after IPL.”