General rules for retaining cursor positions for all program calls

Programs compiled with either CLOSQLCSR(*ENDPGM) or CLOSQLCSR(*ENDMOD) must open a cursor every time the program or module is called, in order to access the data. If the SQL program or module is called several times, and you want to take advantage of a reusable ODP, then the cursor must be explicitly closed before the program or module exits.

Using the CLOSQLCSR parameter and specifying *ENDSQL, *ENDJOB, or *ENDACTGRP, you might not need to run an OPEN and a CLOSE statement on every call. In addition to having fewer statements to run, you can maintain the cursor position between calls to the program or module.

The following examples of SQL statements help demonstrate the advantage of using the CLOSQLCSR parameter:

    EXEC SQL
     DECLARE DEPTDATA CURSOR FOR
       SELECT EMPNO, LASTNAME
         FROM CORPDATA.EMPLOYEE
         WHERE WORKDEPT = :DEPTNUM
    END-EXEC.
 
    EXEC SQL
     OPEN DEPTDATA
    END-EXEC.
 
    EXEC SQL
     FETCH DEPTDATA INTO :EMPNUM, :LNAME
    END-EXEC.
 
    EXEC SQL
     CLOSE DEPTDATA
    END-EXEC.

If this program is called several times from another SQL program, it is able to use a reusable ODP. This technique means that, as long as SQL remains active between the calls to this program, the OPEN statement does not require a database open operation. However, the cursor is still positioned to the first result row after each OPEN statement, and the FETCH statement will always return the first row.

In the following example, the CLOSE statement has been removed:

    EXEC SQL
     DECLARE DEPTDATA CURSOR FOR
       SELECT EMPNO, LASTNAME
         FROM CORPDATA.EMPLOYEE
         WHERE WORKDEPT = :DEPTNUM
    END-EXEC.
 
      IF CURSOR-CLOSED IS = TRUE THEN
    EXEC SQL
     OPEN DEPTDATA
    END-EXEC.
 
    EXEC SQL
     FETCH DEPTDATA INTO :EMPNUM, :LNAME
    END-EXEC.

If this program is precompiled with the *ENDJOB or *ENDACTGRP option and the activation group remains active, the cursor position is maintained. The cursor position is also maintained when the following occurs:

The program is precompiled with the *ENDSQL option.
SQL remains active between program calls.

The result of this strategy is that each call to the program retrieves the next row in the cursor. On subsequent data requests, the OPEN statement is unnecessary and, in fact, fails with a -502 SQLCODE. You can ignore the error, or add code to skip the OPEN. Use a FETCH statement first, and then run the OPEN statement only if the FETCH operation failed.

This technique also applies to prepared statements. A program can first try the EXECUTE, and if it fails, perform the PREPARE. The result is that the PREPARE is only needed on the first call to the program, assuming that the correct CLOSQLCSR option was chosen. If the statement can change between calls to the program, perform the PREPARE in all cases.

The main program might also control cursors by sending a special parameter on the first call only. This special parameter value indicates that because it is the first call, the subprogram performs the OPENs, PREPAREs, and LOCKs.

Note: If you are using COBOL programs, do not use the STOP RUN statement. When the first COBOL program on the call stack ends or a STOP RUN statement runs, a reclaim resource (RCLRSC) operation is done. This operation closes the SQL cursor. The *ENDSQL option does not work as you wanted.