General rules for retaining cursor positions for all program calls

 

When using programs compiled with either CLOSQLCSR(*ENDPGM) or CLOSQLCSR(*ENDMOD), a cursor must be opened every time the program or module is called, in order to access the data. If the SQL program or module is going to be 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 may 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 will be able to use a reusable ODP. This means that, as long as SQL remains active between the calls to this program, the OPEN statement will 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 option or the *ENDACTGRP option and the activation group remains active, the cursor position is maintained. The cursor position is also maintained when the following occurs:

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. You can do this by using a FETCH statement first, and then running 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 the correct CLOSQLCSR option was chosen. Of course, if the statement can change between calls to the program, it should perform the PREPARE in all cases. The main program might also control this by sending a special parameter on the first call only. This special parameter value indicates that because it is the first call, the subprogram should perform the OPENs, PREPAREs, and LOCKs.

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.

 

Parent topic:

Retain cursor positions