DB2 SQL PL cursors and dynamic SQL

Cursors in SQL PL

DECLARE CURSOR belongs in the compound statement after SQL variables and before handlers. Each cursor name must be unique in the body. OPEN materializes the result table; FETCH INTO SQL variables or parameters; CLOSE releases the cursor. The cursor is only referenced from that compound (unless it becomes a result set).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE V_FIRST VARCHAR(12);
DECLARE V_LAST  VARCHAR(15);
DECLARE V_AT_END INT DEFAULT 0;
DECLARE C1 CURSOR FOR
  SELECT FIRSTNME, LASTNAME
    FROM DSN8C10.EMP
   WHERE WORKDEPT = P_DEPT;
DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET V_AT_END = 1;
 
OPEN C1;
FETCH C1 INTO V_FIRST, V_LAST;
WHILE V_AT_END = 0 DO
  -- process one row
  FETCH C1 INTO V_FIRST, V_LAST;
END WHILE;
CLOSE C1;

FOR UPDATE OF on the DECLARE CURSOR select, then UPDATE … WHERE CURRENT OF C1, still works when you need positioned updates. Scrollable cursors (SCROLL, SENSITIVE, and so on) follow the same DECLARE CURSOR rules as embedded SQL; result-set cursors that are returned to a client have extra limits (returned cursors are not scrollable in the usual WITH RETURN path).

WITH RETURN and result sets

• WITHOUT RETURN — default for a cursor declared in SQL PL when you do not specify RETURN. Ordinary working cursor; close it when you are done. Left open at the end, it is not meant as a client result set.
• WITH RETURN (TO CALLER) — if the cursor is still open when the procedure returns, it is a result set for the immediate caller. CREATE PROCEDURE must specify DYNAMIC RESULT SETS n large enough.
• WITH RETURN TO CLIENT — the set is intended for the original application, skipping nested SQL procedures in between.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE HR.LIST_DEPT
     (IN P_DEPT CHAR(3))
     LANGUAGE SQL
     DYNAMIC RESULT SETS 1
     READS SQL DATA
   BEGIN
     DECLARE C_OUT CURSOR WITH RETURN FOR
       SELECT EMPNO, LASTNAME, SALARY
         FROM DSN8C10.EMP
        WHERE WORKDEPT = P_DEPT;
     OPEN C_OUT;
     -- do not CLOSE; caller FETCHes the result set
   END

Nested CALL: opening another cursor with the same name while one is already open can promote the old cursor to a result-set cursor and open a new working cursor — IBM documents that pattern for procedures that return sets from a recursive or nested call. Prefer distinct names until you are copying that pattern on purpose.

Dynamic SQL

Dynamic SQL is a character string you build, then ask Db2 to prepare at run time. Use it when the table name, a variable IN-list of columns, or a piece of DDL is not fixed at CREATE PROCEDURE time. IBM’s CREATEDEPTTABLE example concatenates a department number into a table name, PREPARE DROP/CREATE/INSERT, and EXECUTE.

1
2
3
4
5
6
7
8
9
10
11
DECLARE STMT VARCHAR(200);
DECLARE TABLE_NAME VARCHAR(30);
SET TABLE_NAME = 'DEPT_' || P_DEPT || '_T';
SET STMT = 'DROP TABLE ' || TABLE_NAME;
PREPARE S1 FROM STMT;
EXECUTE S1;
 
SET STMT = 'INSERT INTO ' || TABLE_NAME ||
  ' SELECT EMPNO, LASTNAME FROM DSN8C10.EMP WHERE WORKDEPT = ?';
PREPARE S3 FROM STMT;
EXECUTE S3 USING P_DEPT;

Critical rule: the string cannot contain SQL variable or parameter names as substitutions the way a COBOL precompiler would. If you write WHERE WORKDEPT = P_DEPT inside the quotes, Db2 looks for a column or the letters P_DEPT, not the parameter. Values go through parameter markers (?) and USING on EXECUTE or OPEN.

Object names (table, column) cannot be parameter markers in standard SQL. Those you concatenate — only after you validate them against an allow-list. Concatenating a user-typed table name is an injection hole. Prefer static SQL whenever the statement shape is known.

Dynamic SELECT: DECLARE C1 CURSOR FOR S1; PREPARE S1 FROM STMT; OPEN C1 USING P_DEPT; FETCH … You can also OPEN using an array variable and an index when the prepared text casts a parameter marker to an array type — IBM shows CAST(? AS INTARRAY)[?] with OPEN C2 USING INTA, INTV.

DEFER PREPARE and DYNAMICRULES on CREATE PROCEDURE / bind options affect when PREPARE runs and which authorization ID dynamic SQL uses. Match your shop’s package defaults before you assume CURRENT SQLID is who dynamic DDL runs as.

Temporary tables

When a procedure needs a private work file — explode a bill of materials, stage keys before a join, collect rows for a result set — use a declared global temporary table (DGTT) or a created global temporary table (CGTT) defined once in the catalog.

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE GLOBAL TEMPORARY TABLE SESSION.WORK_EMP
  (EMPNO CHAR(6) NOT NULL,
   SALARY DECIMAL(9,2))
  ON COMMIT PRESERVE ROWS;
 
INSERT INTO SESSION.WORK_EMP
  SELECT EMPNO, SALARY
    FROM DSN8C10.EMP
   WHERE WORKDEPT = P_DEPT;
 
FOR W AS SELECT EMPNO, SALARY FROM SESSION.WORK_EMP DO
  SET V_TOTAL = V_TOTAL + W.SALARY;
END FOR;

SESSION qualifier is the usual schema for DGTTs. ON COMMIT DELETE ROWS versus PRESERVE ROWS decides whether a COMMIT inside the CALL (or COMMIT ON RETURN) wipes the work table. CGTT (CREATE GLOBAL TEMPORARY TABLE) is a cataloged description; each session still gets its own empty instance. Neither is a substitute for a real history table.

SQL PL arrays

An array is an ordered set of elements of one built-in type. On z/OS you typically CREATE TYPE an array type, then DECLARE SQL variables or parameters of that type. Arrays are for routine parameters, SQL variables, and some function returns — not for a column of a base table.

1
2
3
4
5
6
7
8
CREATE TYPE HR.INTARRAY AS INTEGER ARRAY[100];
 
-- inside a procedure:
DECLARE INTA HR.INTARRAY;
DECLARE I INT DEFAULT 1;
SET INTA[1] = 10;
SET INTA[2] = 20;
SET I = CARDINALITY(INTA);

• Ordinary array — CREATE TYPE t AS int ARRAY[100]; indexes are ordinal positions (IBM examples start at 1). MAX_CARDINALITY is 100.
• Associative array — CREATE TYPE t AS char(10) ARRAY[VARCHAR(10)] or ARRAY[INTEGER]; you look up by key, not only by dense ordinal.

You cannot SELECT a column directly into an array. Use ARRAY_AGG in a SELECT INTO or SET, then optionally UNNEST the array as a table in another query. TRIM_ARRAY removes elements from the end of an ordinary array; ARRAY_FIRST / ARRAY_NEXT walk associative indexes.

The GETWEEKENDS IBM sample is the teaching picture: IN array of dates, OUT array of weekend dates, WHILE over CARDINALITY, SET OUT[i] = IN[j]. That is SQL PL arrays without a cursor.

SQL PL recursion

A native SQL procedure may CALL itself (or CALL a sibling that CALLs back). Each CALL is a nested stored-procedure invocation and counts toward Db2’s maximum nest depth. There is no infinite call stack. Always code a base case:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE HR.WALK_MGR
     (IN  P_EMPNO CHAR(6),
      IN  P_DEPTH INTEGER,
      OUT P_TOP   CHAR(6))
     LANGUAGE SQL
     READS SQL DATA
   BEGIN
     DECLARE V_MGR CHAR(6);
     IF P_DEPTH <= 0 OR P_EMPNO IS NULL THEN
       SET P_TOP = P_EMPNO;
     ELSE
       SELECT MGRNO INTO V_MGR
         FROM DSN8C10.EMP
        WHERE EMPNO = P_EMPNO;
       IF V_MGR IS NULL OR V_MGR = P_EMPNO THEN
         SET P_TOP = P_EMPNO;
       ELSE
         CALL HR.WALK_MGR(V_MGR, P_DEPTH - 1, P_TOP);
       END IF;
     END IF;
   END

For bill-of-materials explosions, a WHILE plus DGTT of “keys still to visit” is usually clearer and does not burn nest depth. Recursive common table expressions are a different SQL feature (query recursion), not SQL PL CALL recursion — use the tool that matches the problem. ASUTIME LIMIT still cancels a runaway recursive procedure.

Explain It Like I'm Five

A cursor is your finger moving down a printed list. WITH RETURN means you leave the list on the table for the person who called you, instead of putting it away. Dynamic SQL is writing the homework question on a blank sheet at the last minute, then asking the teacher (Db2) to read it — but you must draw blank boxes (?) for the numbers instead of scribbling variable names in the sentence. A temporary table is a whiteboard only your class can see. An array is a numbered ice-cube tray: slot 1, slot 2, slot 3. Recursion is calling yourself on the phone; you must hang up when the list is empty or the phone company (nest limit) cuts you off.

Exercises

1. Write DECLARE CURSOR WITH RETURN and the CREATE PROCEDURE header needed to return one result set of employees in a department.
2. Convert a static SELECT INTO that uses P_DEPT into PREPARE/EXECUTE with a parameter marker. Explain why P_DEPT must not appear inside the quotes.
3. Declare a DGTT, INSERT three rows, and FOR-loop them. What ON COMMIT option would you choose if the procedure COMMITs on return?
4. CREATE TYPE an ordinary INTEGER array of maximum 20, DECLARE it, SET two elements, and print CARDINALITY in an OUT parameter.
5. Give two reasons a WHILE + DGTT might beat recursive CALL for walking a manager chain.