Db2 in practice

The cursor loop

The workhorse of mainframe reporting: declare a cursor, open it, fetch rows in a loop until SQLCODE 100, then close. Every statement checks SQLCODE before the next one runs:

EXEC SQL DECLARE C1 CURSOR FOR SELECT EMP-ID, SALARY FROM EMPLOYEE WHERE DEPT = :WS-DEPT END-EXEC. EXEC SQL OPEN C1 END-EXEC. PERFORM UNTIL SQLCODE = 100 EXEC SQL FETCH C1 INTO :WS-EMP-ID, :WS-SALARY END-EXEC IF SQLCODE = 0 PERFORM PROCESS-ROW END-IF END-PERFORM. EXEC SQL CLOSE C1 END-EXEC.

Checking SQLCA

SQLCA is the communication area copied into WORKING-STORAGE with INCLUDE SQLCA. SQLCODE 0 is success, 100 is no more rows, and any negative code means the statement failed and the transaction should roll back.

Commit and rollback

End a unit of work with EXEC SQL COMMIT END-EXEC, and undo it with EXEC SQL ROLLBACK END-EXEC. Commit at a business boundary, not after every row, so the database stays consistent if the job fails halfway.

The code on this page was not run: Db2 embedded SQL needs a precompiler and a database.

Article author: Arthur Isaev