The CLOSE CURSOR statement is used to release a cursor and the resources associated with it once all necessary rows have been retrieved. Closing the cursor is the final step in the cursor’s lifecycle, ensuring the program efficiently frees up resources and avoids memory issues. After the cursor is closed, it can no longer be used to fetch rows until it is re-declared and re-opened.

The CLOSE CURSOR statement deactivates a cursor after all required rows have been processed. This step is crucial because:

• It releases any resources or locks DB2 held for that cursor.
• It prevents memory and resource issues, especially in programs that handle large datasets or run for extended periods.
• It prepares the cursor to be re-opened if necessary, although the cursor would need to be re-initialized (re-declared and re-opened) to use again.

Important Note: After closing, you cannot fetch from the cursor again until it’s re-declared and re-opened.

Syntax -

CLOSE cursor_name;

• cursor_name: The name of the cursor you want to close. This name must match the one used in the corresponding DECLARE and OPEN statements.

Example -

Scenario1 - Closing cursor.

Code -

EXEC SQL 
	CLOSE CS1
END-EXEC.

Scenario2 - Closing cursor with release option.

Code -

  EXEC SQL 
	CLOSE CS2 WITH RELEASE
END-EXEC.

---