A cursor allows you to work with rows retrieved from a database one at a time. This is useful when the result of a query contains multiple rows, and you need to process each row sequentially in a COBOL program. Instead of working with the entire result set at once, the cursor acts as a pointer that 'scrolls' through each row, making it easier to handle large datasets efficiently.

Why Use a Cursor in DB2 on Mainframe?

Cursors are used to manage data retrieved from a DB2 table, particularly when:

• The query returns multiple rows, and each row needs to be processed individually.
• You need to retrieve data row-by-row, especially for batch processing or detailed handling.
• Direct access to all result rows at once may not be feasible due to memory or performance considerations.

Using a cursor enables controlled row-by-row data access, critical for COBOL applications that perform complex processing on each row of data.

What is a Cursor in DB2 Mainframe?

A cursor in DB2 is a database object that acts as a pointer to the result set of a query. Once defined, it enables the program to retrieve and work with one row at a time. This allows applications to loop through the result set and perform specific actions for each row.

Cursor Lifecycle

A cursor in DB2 goes through the following sequence:

1. Declare the Cursor -

Define the cursor with a SELECT statement specifying the columns and conditions for rows to be fetched.

EXEC SQL
	DECLARE C1 CURSOR FOR
	    SELECT EMP_NAME, 
		       DEPT_NAME
          FROM EMPLOYEE
         WHERE DEPT_ID = :DEPT-ID
END-EXEC.

2. Open the Cursor -

Open the cursor to start accessing the result set.

EXEC SQL
	OPEN C1
END-EXEC.

3. Fetch Rows -

Retrieve rows one by one, storing values in COBOL host variables.

EXEC SQL
	FETCH C1 INTO :EMP-NAME, :DEPT-NAME
END-EXEC.

4. Close the Cursor -

Close the cursor after processing all required rows to free resources.

EXEC SQL
	CLOSE C1
END-EXEC.

Basic Types of Cursors

1. Read-Only Cursor -

A read-only cursor retrieves data without allowing updates to the rows fetched.

EXEC SQL
	DECLARE C1 CURSOR FOR
	 SELECT EMP_ID, 
	        EMP_NAME 
	   FROM EMPLOYEE
	    FOR READ ONLY
END-EXEC.

2. Updateable Cursor -

An updateable cursor allows updates to the rows retrieved. We can use UPDATE or DELETE to modify or remove the current row.

EXEC SQL
	DECLARE C2 CURSOR FOR
     SELECT EMP_ID, 
	        EMP_NAME 
	   FROM EMPLOYEE
        FOR UPDATE
END-EXEC.

Error Handling for Cursors

DB2 uses SQLCODE values to indicate the status of cursor operations. Common handling steps include:

• SQLCODE = 0: Successful execution.
• SQLCODE = 100: No more rows to fetch (end of rows).
• SQLCODE < 0: An error occurred.

---