DB2 cursor types and advanced options

SENSITIVE STATIC

SENSITIVE STATIC SCROLL (scroll is typical) builds a result whose membership is fixed at OPEN. If another transaction commits an update to a row that is in your result, a SENSITIVE FETCH can show the new values. If that row is deleted, FETCH can return a hole (no data, a warning/SQLCODE that the row is gone). Inserts that would have qualified are not added to a STATIC result.

1
2
3
4
5
DECLARE C_STAT SENSITIVE STATIC SCROLL CURSOR FOR
  SELECT EMPNO, LASTNAME, SALARY
    FROM DSN8C10.EMP
   WHERE WORKDEPT = :DEPT
   FOR UPDATE OF SALARY;

FETCH SENSITIVE vs FETCH INSENSITIVE on this cursor chooses whether this FETCH sees the latest base-table values or the original result-table copy. Default follows the cursor’s sensitivity.

SENSITIVE DYNAMIC

SENSITIVE DYNAMIC does not freeze membership. The cursor behaves like a moving window on the base tables. Committed inserts can appear on a later FETCH; deleted rows disappear. This is closer to “live query” and closer to ordinary isolation-level visibility. It cannot be INSENSITIVE. Updatable dynamic scrollable cursors have extra restrictions—read the SQL Reference before mixing DYNAMIC with positioned UPDATE in a complicated join.

WITH HOLD

WITH HOLD means COMMIT does not close the cursor. After commit, the cursor sits after the last row you fetched. You FETCH next to continue. You cannot positioned-UPDATE until you FETCH again.

1
2
3
4
5
6
7
8
       EXEC SQL
            DECLARE EMPLUPDT CURSOR WITH HOLD FOR
             SELECT EMPNO, LASTNAME, PHONENO, JOB, SALARY, WORKDEPT
               FROM DSN8C10.EMP
              WHERE WORKDEPT < 'D11'
              ORDER BY EMPNO
              FOR UPDATE OF PHONENO
       END-EXEC.

Restrictions: do not leave held cursors on a thread that might go inactive—locks can stick. IMS MPP and message-driven BMP: each message is a new user; WITH HOLD does not carry across messages. CICS: WITH HOLD survives SYNCPOINT COMMIT inside a task, but EOT (pseudo-conversational RETURN) closes the story—you reopen after the next task. SQL COMMIT in CICS/IMS is the wrong lever; use the transaction manager’s syncpoint (next page).

WITH RETURN

In a stored procedure, DECLARE CURSOR ... WITH RETURN TO CALLER (or TO CLIENT) marks a result-set cursor. OPEN it, do not CLOSE it before RETURN; the caller FETCHes. TO CALLER is the nested caller; TO CLIENT is the outermost client. You can return multiple result sets from one procedure (multiple WITH RETURN cursors left open).

1
2
3
4
5
DECLARE C_OUT CURSOR WITH RETURN TO CALLER FOR
  SELECT EMPNO, LASTNAME
    FROM DSN8C10.EMP
   WHERE WORKDEPT = P_DEPT
   ORDER BY EMPNO;

Positioned UPDATE

After FETCH positions the cursor on a row:

1
2
3
UPDATE DSN8C10.EMP
   SET PHONENO = :NEW-PHONE
 WHERE CURRENT OF EMPLUPDT;

Rules of thumb:

• Cursor must be open and on a row (not before first / after last / on a hole)
• DECLARE with FOR UPDATE OF the columns you SET (or FOR UPDATE)
• The SELECT must identify a row of a single updatable table (no arbitrary joins / DISTINCT / column functions unless Db2 documents an exception)
• After WITH HOLD COMMIT, FETCH again before WHERE CURRENT OF

Positioned DELETE

1
2
DELETE FROM DSN8C10.EMP
 WHERE CURRENT OF C_EMP;

Deletes the current row. The cursor is left in a position from which the next FETCH NEXT gets the following row (you do not skip one). Same updatability rules as positioned UPDATE. Prefer searched DELETE (WHERE key = ...) when you already have the primary key and do not need cursor position.

Rowset positioning

WITH ROWSET POSITIONING plus FETCH NEXT ROWSET FOR n ROWS positions on a current rowset, not a single row. Positioned statements then use:

• WHERE CURRENT OF c — often the whole rowset for rowset-positioned cursors (check your FETCH/UPDATE form)
• FOR ROW n OF ROWSET — one row in that rowset (1-based index into the arrays you just fetched)

1
2
3
4
5
6
7
8
FETCH NEXT ROWSET FROM C1 FOR 20 ROWS
  INTO :HVA-EMPNO, :HVA-SALARY;
 
-- Update only the 5th row of that rowset
UPDATE DSN8C10.EMP
   SET SALARY = SALARY * 1.03
 WHERE CURRENT OF C1
   FOR ROW 5 OF ROWSET;

Host arrays used on FETCH must stay aligned with n. If SQLERRD(3) returned 7 rows, FOR ROW 8 is invalid. Dimension arrays to the maximum FOR n ROWS you will request.

FOR n ROWS and multi-row INSERT

FOR n ROWS appears on FETCH (how many to retrieve) and on INSERT (how many array rows to insert):

1
2
3
4
INSERT INTO DSN8C10.EMP (EMPNO, LASTNAME)
  FOR :N ROWS
  VALUES (:HVA-EMPNO, :HVA-LASTNAME)
  ATOMIC;

n is at most 32767. ATOMIC means one row failure fails the statement; NOT ATOMIC CONTINUE ON SQLEXCEPTION can keep inserting remaining rows (diagnostics in GET DIAGNOSTICS). This is the bulk-insert twin of rowset FETCH—not a cursor option, but the same host-array skill.

FETCH FIRST n ROWS ONLY on the SELECT limits the result table size. FOR n ROWS on FETCH limits this call. If both appear, FETCH FIRST n ROWS ONLY dominates the result cardinality.

FOR ROW n

FOR ROW n OF ROWSET qualifies positioned UPDATE/DELETE (and some FETCH forms) to a single row inside the current rowset. n must be between 1 and the number of rows in that rowset. Use it when you FETCHed 50 rows, the program decided only row 12 should change, and you do not want to UPDATE all 50.

Putting a batch pattern together

1
2
3
4
5
6
7
8
9
10
       EXEC SQL
            DECLARE C_BAT CURSOR WITH HOLD WITH ROWSET POSITIONING FOR
             SELECT EMPNO, SALARY
               FROM DSN8C10.EMP
              WHERE WORKDEPT = :WS-DEPT
              FOR UPDATE OF SALARY
       END-EXEC.
       EXEC SQL OPEN C_BAT END-EXEC.
      * Fetch, update selected rows with FOR ROW n, COMMIT every 500 rows.
      * WITH HOLD keeps C_BAT open across COMMIT.

That is the industrial pattern: rowset FETCH for speed, positioned UPDATE for the rows that need change, WITH HOLD so COMMIT frequency does not restart the scan, FOR READ ONLY instead when the job is extract-only.

Explain It Like I'm Five

A normal bookmark falls out of the book when you take a snack break (COMMIT). WITH HOLD is tape on the bookmark so it stays. ROLLBACK throws the book on the floor—tape or not, you lost the place. SENSITIVE STATIC is a photocopy of the pages; scribbles on the real book might show through on those pages, but new pages added to the library do not appear in your photocopy. SENSITIVE DYNAMIC is reading the real bookshelf. WHERE CURRENT OF is “erase the sentence my finger is on.” FOR ROW 5 OF ROWSET is “I picked up five cards; change only the fifth.” WITH RETURN is handing the rest of the stack to mom when you are done picking.

Exercises

1. Declare a WITH HOLD cursor FOR UPDATE OF SALARY on EMP filtered by :DEPT.
2. Write positioned UPDATE that sets SALARY = SALARY * 1.05 WHERE CURRENT OF that cursor.
3. Why must you FETCH after COMMIT before another positioned UPDATE on a held cursor?
4. Contrast SENSITIVE STATIC and SENSITIVE DYNAMIC for a row inserted by another committed transaction after your OPEN.
5. Write FETCH NEXT ROWSET FOR 10 ROWS and an UPDATE FOR ROW 3 OF ROWSET.