SQL Reference

CLOSE

The CLOSE statement closes a cursor. If a result table was created when the cursor was opened, that table is destroyed.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that cannot be dynamically prepared.

Authorization

None required. See DECLARE CURSOR for the authorization required to use a cursor.

Syntax

>>-CLOSE--cursor-name----+---------------+---------------------><
                         '-WITH RELEASE--'
 

Description

cursor-name

Identifies the cursor to be closed. The cursor-name must identify a declared cursor as explained in the DECLARE CURSOR statement. When the CLOSE statement is executed, the cursor must be in the open state.

WITH RELEASE

The release of all read locks that have been held for the cursor is attempted. Note that not all of the read locks are necessarily released; these locks may be held for other operations or activities.

Notes

• At the end of a unit of work, all cursors that belong to an application process and that were declared without the WITH HOLD option are implicitly closed.
• CLOSE does not cause a commit or rollback operation.
• The WITH RELEASE clause has no effect for cursors that are operating under isolation levels CS or UR. When specified for cursors that are operating under isolation levels RS or RR, WITH RELEASE terminates some of the guarantees of those isolation levels. Specifically, if the cursor is opened again, an RS cursor may experience the 'nonrepeatable read' phenomenon and an RR cursor may experience either the 'nonrepeatable read' or 'phantom' phenomenon. Refer to Appendix I, Comparison of Isolation Levels for more details.

  If a cursor that was originally either RR or RS is reopened after being closed using the WITH RELEASE clause, then new read locks will be acquired.

• Special rules apply to cursors within a stored procedure that have not been closed before returning to the calling program. See Notes for more information.

Example

A cursor is used to fetch one row at a time into the C program variables dnum, dname, and mnum. Finally, the cursor is closed. If the cursor is reopened, it is again located at the beginning of the rows to be fetched.

   EXEC SQL DECLARE C1 CURSOR FOR
      SELECT DEPTNO, DEPTNAME, MGRNO
      FROM TDEPT
      WHERE ADMRDEPT = 'A00';
 
   EXEC SQL OPEN C1;
 
   while (SQLCODE==0) {                                             .
      EXEC SQL FETCH C1 INTO :dnum, :dname, :mnum;
         .
         .
   }
 
   EXEC SQL CLOSE C1;