Db2 in practice
| The cursor loop | |
| Checking SQLCA | |
| Commit and rollback |
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.
Next: Capstone: CLI calculator
Article author: Arthur Isaev
