CICS DB2 Interview Questions

CICS-DB2 questions test the attachment facility, plans, SQL coding rules under CICS, and two-phase commit.

Interviewers expect you to know why CICS programs never code COMMIT and what happens to cursors at syncpoint.

Attachment and plans

• Q: How does CICS connect to DB2? A: Through the CICS-DB2 attachment facility. RCT (Resource Control Table) entries map each trans-id to a DB2 plan.
• Q: What is a plan? A: A bound package of the program's SQL statements created with the BIND command. The plan must be rebound after any SQL change.
• Q: SQLCODE -805? A: The SQL statement is not in the plan. Rebind the plan - the program was changed without rebinding.
• Q: SQLCODE -818? A: Timestamp mismatch between the load module and the plan. Recompile, relink and rebind together.
• Q: SQLCODE -904? A: Resource unavailable - the table space or index is stopped. Check DB2 status and retry.

Coding SQL in CICS

• Q: Why no COMMIT in CICS programs? A: CICS is the syncpoint coordinator. EXEC CICS SYNCPOINT commits DB2 and CICS resources together; an SQL COMMIT would break two-phase commit.
• Q: Can a cursor stay open across a pseudo-conversational RETURN? A: No. The syncpoint at RETURN closes cursors. Re-OPEN and re-FETCH after each return.
• Q: How do you check SQL results? A: Test SQLCODE after every statement: 0 success, +100 not found, negative error. The SQLCA carries the details.
• Q: SQLCODE -911? A: Deadlock or timeout. Roll back and retry the unit of work; keep units of work short to avoid it.
• Q: How are NULLs handled? A: With indicator variables. A -310 means a NULL reached a host variable without an indicator.

Two-phase commit

• Q: What is two-phase commit? A: CICS as coordinator asks DB2 and the file system to prepare, then commits both or rolls both back - they never disagree.
• Q: SYNCPOINT vs SYNCPOINT ROLLBACK? A: SYNCPOINT commits the unit of work; SYNCPOINT ROLLBACK undoes everything since the last syncpoint.
• Q: What happens to DB2 locks if the task abends? A: CICS rolls back the unit of work automatically, releasing DB2 locks and undoing uncommitted changes.
• Q: Why finish DB2 work before RETURN? A: The syncpoint at RETURN commits and releases locks. Holding locks across terminal waits blocks other users.