DB2 FETCH Cursor

The FETCH CURSOR statement is used to retrieve rows from a previously opened cursor, one row at a time. After the cursor has been opened, the FETCH statement points to each row in the result set sequentially, making the row’s data available to the COBOL program.

The FETCH CURSOR statement in DB2 retrieves the next row in the result set managed by the cursor. Each time FETCH is executed, the cursor moves to the following row, retrieving data from each column as defined in the SELECT statement. The COBOL program can then access this data using host variables.

Key Features:

  • Fetches one row at a time from the result set.
  • Moves the cursor to the next row after each FETCH.
  • Can be used in a loop to process all rows in the result set.
  • Stops fetching when there are no more rows (indicated by SQLCODE = 100).

Syntax -

EXEC SQL
  FETCH cursor_name 
  INTO host_variable1, host_variable2, ...
END-EXEC.
  • cursor_name: The name of the cursor that was previously declared and opened.
  • host_variable1, host_variable2, ...: COBOL host variables that store the retrieved column values from the current row in the cursor’s result set. These variables must match the data types of the columns they correspond to.

Cursor positioning -

An open cursor has the below three possible positions -

  • Before a row
  • On a row
  • After the last row.

If a cursor is on a row of the result table, that row is called the cursor's current row. The current row is referenced in an UPDATE or DELETE statement. If the cursor is positioned on any row except the last row and the FETCH is executed, then -

  • The SQLCODE is set to 0.
  • It repositions on the next row, and the values of that row are assigned to the host variables.

If the cursor is currently positioned on or after the last row of the result table and the FETCH is executed, then -

  • The SQLCODE is set to +100, and SQLSTATE is set to '02000'.
  • No Changes in cursor position.
  • Values are not assigned to the target variables.

Example -

Scenario1 - Fetch cursor for retrieving two columns.

Code -

 ...
   EXEC SQL 
	FETCH F1
	 INTO :HV1,
	      :HV2
   END-EXEC.
   ...

Scenario2 - Fetch cursor for retrieving two columns with NULL indicator check.

Code -

  ...
   EXEC SQL 
	FETCH F2
	 INTO :HV1
	      :HV1-NI,
	      :HV2
	      :HV2-NI
   END-EXEC.
   ...
More Examples