DB2 Cursor Types

DB2 CURSOR DB2 Statements in COBOL Program

Advertisements


CURSOR is used to process set of rows one by one from table(s).

CURSOR used to retrieve and process one row from a set of rows retrieved by the application program.

It will process the rows one by one sequentially after retrieved. It’s like the sequential access of the file.

There are two types of cursors based on how the cursor used in the program.


  1. Read-only cursor
  2. Updatable cursor

Read-only Cursor:


The cursor is used for only retrieving the data from tables and it can’t be used to perform any update operation on the database.

The UNION, JOIN and GROUP BY used cursors will not be used for any update purpose.

The cursor will be declared with FOR FETCH ONLY.

If the cursor declared without the FETCH/UPDATE at the end, then that will be treated as read-only.

The result table is purely for read-only purpose.

Positioned DELETE and positioned UPDATE are not allowed with these cursors.

Read only cursors can be executed with isolation level UR (Uncommitted Read).


Syntax:


Declare Cursor:

	EXEC SQL
		DECLARE cursor-name 	[NO SCROLL/SCROLL]
	              			[DYNAMIC/STATIC]
		CURSOR 	[WITH/WITHOUT HOLD]
		FOR select-statement 
		[FOR FETCH ONLY]
	END-EXEC.

Open cursor:
	
	EXEC SQL
		OPEN Cursor-name USING host-variables
	END-EXEC.


Fetch cursor:

	EXEC SQL
		FETCH Cursor-name
		INTO
		:Host-variable-col1,
		:Host-variable-col2,
		.
		.
		:Host-variable-coln
	END-EXEC.

Close cursor:

	EXEC SQL
		CLOSE cursor-name
	END-EXEC.


Updatable Cursor:


The cursor is used for only update of the table after retrieving.

The cursor will be declared with FOR UPDATE OF at the end.

The limitation of updatable cursor is, the declaration of the cursor should be performed on only one table.

The other limitation is, GROUP BY, ORDER BY and JOIN should not be used in the cursor declaration.

If the cursor declared with UPDATE at the end, then it will be treated as updatable cursors.

Positioned DELETE and positioned UPDATE are allowed with these cursors.

WHERE CURRENT OF statement used to update the table row which matches to the current values in cursor.


Syntax:


Declare Cursor:

	EXEC SQL
		DECLARE cursor-name 	[NO SCROLL/SCROLL]
               				[DYNAMIC/STATIC]
		CURSOR 	[WITH/WITHOUT HOLD]
		FOR select-statement 
		[FOR UPDATE OF columns-list]
	END-EXEC.

Open cursor:
	
	EXEC SQL
		OPEN Cursor-name USING host-variables
	END-EXEC.

Fetch cursor:

	EXEC SQL
		FETCH Cursor-name
		INTO
		:Host-variable-col1,
		:Host-variable-col2,
		.
		.
		:Host-variable-coln
	END-EXEC.

Update cursor:

	EXEC SQL
		UPDATE table-name
		SET column-name1 = :Host-variable-col1,
		        column-name2 =:Host-variable-col2,
			.
			.
		       column-namen =:Host-variable-coln
		WHERE CURRENT OF Cursor-name
	END-EXEC.

Close cursor:

	EXEC SQL
		CLOSE cursor-name
	END-EXEC.


DB2 CURSOR DB2 Statements in COBOL Program

Advertisements