Explain Cursors: Static and Dynamic in IBM Mainframes

39 / 60

DB2intermediate

Cursors: Static and Dynamic

Understand why COBOL needs cursors to process multi-row SQL result sets and the difference between static and dynamic cursor definitions.

COBOL has no native concept of a result set — every embedded SQL statement either returns exactly one row (via SELECT INTO) or, for anything that could return multiple rows, must be processed through a cursor, which acts as a pointer that steps through a result set one row at a time. This is one of the first genuinely DB2-specific (rather than general SQL) concepts a COBOL developer must internalize, and interviewers ask about it constantly because nearly every batch DB2 program that processes more than a handful of known rows uses one.

A cursor is a librarian's bookmark moving through a shelf of matching books one at a time on request — a static cursor knows exactly which shelf to search before the day even starts, while a dynamic cursor gets handed a search request written on the fly and has to figure out where to look right then.

Key Concepts

A static cursor's SQL is fully known at precompile time — DECLARE CURSOR names a fixed SELECT statement, and the cursor's access path is determined during BIND, exactly like any other embedded SQL statement. The typical lifecycle is DECLARE (compile-time, defines the query), OPEN (positions the cursor and, in effect, executes the query), repeated FETCH calls (retrieving one row per call into host variables, checking SQLCODE +100 to detect end-of-result-set), and CLOSE (releasing the cursor's resources).

A dynamic cursor is used when the actual SQL text isn't known until runtime — built as a string in working-storage and prepared with PREPARE, then associated with a cursor via DECLARE CURSOR FOR that prepared statement, opened, fetched, and closed the same way. This is essential for applications that must construct different WHERE clauses or table references based on runtime conditions, at the cost of the optimizer only being able to determine an access path at execution time rather than in advance.

A sharp interview question probes cursor scope and update behavior: cursors declared WITH HOLD survive across commit points within the same unit of work (important for long-running batch cursors that must commit periodically without losing their position), and a cursor intended for subsequent positioned UPDATE/DELETE via WHERE CURRENT OF must be declared FOR UPDATE OF specifying the columns that will be modified.