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 cannot receive a result set. SELECT INTO handles exactly one row; anything that might return several needs a cursor, which hands rows over one at a time.

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

The four steps.

    EXEC SQL DECLARE C1 CURSOR FOR
         SELECT CUST_ID, NAME FROM CUSTOMER
          WHERE REGION = :WS-REGION
    END-EXEC.

    EXEC SQL OPEN C1 END-EXEC.

    PERFORM UNTIL SQLCODE = 100
        EXEC SQL FETCH C1 INTO :WS-ID, :WS-NAME END-EXEC
        IF SQLCODE = 0
            PERFORM PROCESS-ROW
        END-IF
    END-PERFORM.

    EXEC SQL CLOSE C1 END-EXEC.

DECLARE is not executable — it is a declaration the precompiler reads. OPEN is where the query runs.

DECLAREOPEN

WITH HOLD is the one that matters in batch. A commit normally closes every open cursor, which is a problem for a program that must commit periodically while working through a million rows.

WITH HOLD

    EXEC SQL DECLARE C1 CURSOR WITH HOLD FOR ... END-EXEC.

The cursor survives the commit and keeps its position. Without it, a program that commits every thousand rows loses its place and either starts again or fails.

FOR UPDATE OF enables positioned updates.

    DECLARE C1 CURSOR FOR
        SELECT ... FROM CUSTOMER FOR UPDATE OF STATUS

FOR UPDATE OF

    EXEC SQL UPDATE CUSTOMER SET STATUS = 'C'
             WHERE CURRENT OF C1 END-EXEC.

WHERE CURRENT OF updates the row the cursor is on, with no need to repeat its key. The columns to be changed must be named in the declaration.

WHERE CURRENT OF

Static and dynamic. Everything above is static: the SQL is fixed at precompile time and its access path is chosen at bind.

A dynamic cursor builds the statement at runtime:

    MOVE 'SELECT CUST_ID FROM CUSTOMER WHERE ' TO WS-SQL
    STRING WS-SQL DELIMITED SIZE WS-CONDITION DELIMITED SIZE
        INTO WS-STMT

    EXEC SQL PREPARE S1 FROM :WS-STMT END-EXEC.
    EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC.

Necessary when the filters genuinely are not known until the program runs — a search screen where the user picks which criteria to use.

The costs of dynamic. The access path is chosen at execution rather than at bind, so you pay for that each time and cannot review it in advance with EXPLAIN.

EXPLAIN

And building SQL by concatenation is SQL injection if any part of it comes from user input. Parameter markers — WHERE REGION = ? with values supplied on OPEN — avoid that, and should be used whenever the shape is fixed and only the values vary.

WHERE REGION = ?OPEN

What the interviewer is probing.1. "What is the difference between static and dynamic SQL?" Probing: the central point. Stalls: "Nothing." Moves up: static is precompiled with the access path fixed at bind; dynamic is built and optimised at run time, which costs more but allows flexibility.

2. "When is dynamic SQL justified?" Probing: judgement. Stalls: "Never." Moves up: when the predicates genuinely vary at run time — a query builder or an ad-hoc reporting screen.

3. "What is the danger with dynamic SQL?" Probing: the security angle. Stalls: "Performance." Moves up: concatenating user input creates an injection risk, so parameter markers are mandatory, not optional.

4. "What does FOR UPDATE OF do on a cursor?" Probing: the detail. Stalls: "Nothing." *Moves up:* declares intent to update, taking stronger locks and enabling positioned UPDATE and DELETE on the current row.