DB2 10.5 for Linux, UNIX, and Windows

CLOSE statement

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. When invoked using the command line processor, some options cannot be specified. For more information, refer to "Using command line SQL statements and XQuery statements".

Authorization

If a global variable is referenced, the privileges held by the authorization ID of the statement must include one of the following authorities:
  • READ privilege on the global variable that is not defined in a module
  • EXECUTE privilege on the module of the global variable that is defined in a module
For the authorization required to use a cursor, see "DECLARE CURSOR".

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CLOSE--+-cursor-name----------+--+--------------+-----------><
          '-cursor-variable-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.
cursor-variable-name
Identifies the cursor to be closed. The cursor-variable-name must identify a cursor variable. When the CLOSE statement is executed, the underlying cursor of cursor-variable-name must be in the open state (SQLSTATE 24501). A CLOSE statement using cursor-variable-name can only be used within a compound SQL (compiled) statement.
WITH RELEASE
The release of all locks that have been held for the cursor is attempted. Note that not all of the locks are necessarily released; these locks may be held for other operations or activities.

Notes

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;