Advanced Cursors in DB2

                             CURSORS IN DB2

Use of DB2 Cursors in Application Program

• Mechanism that allows the SELECT statement to find a set of rows from table
• It return rows one at a time
• Used as similar to a File which can be Opened, Read and Closed
• ‘Fetch’ terminology is used instead of ‘Read’
• Can be declared in both ‘Working Storage’ and ‘Procedure Division’
• Should always be declared before the ‘Open’ Cursor statement

Types of Cursors

- Row Positioned Cursor
- RowSet Positioned Cursor
- Scrollable
- Non Scrollable
- Held
- Non-Held
- Returnable
- Non-Returnable

 

Brief Description for cursors 

Row Positioned Cursor : Standard common cursor used for DB2-COBOL programs. Returns on row from the table at a time into host variables. The steps for this cursor are Declare, Open, Fetch and Close operations.

 

RowSet Positioned Cursor: RowSet positioned cursor retrieves 0, 1 or many records at a time into host variables array. This array will be declared within application program. The steps for this cursor are Declare, Open, Fetch and Close operations.

Scrollable: This is identified with SCROLL keyword in the Declare statement of cursor. The behaviour of the scrollable cursor is governed based on the sensitivity with cursor is declared. It can be row positioned or row set positioned. The steps for this cursor are Declare, Open, Fetch and Close operations. There are subtypes to this one as insensitive and sensitive.

Non-Scrollable: Do not have key word SCROLL. A simple Row or rowset cursor postioned cursor without a scroll clause is a non-scrollable cursor

Held: This type of cursor is not closed explicitly after commit operation is complete. It returns only 1 row at a time into host variables.The steps for this cursor are Declare, Open, Fetch and Close operations. Application programmer can issue close or rollback operation to the held cursor; if not issued cursor is closed when the application program terminates.

Non-Held: This is similar to row positioned cursor. This is closed after a Commit operation is complete.

Returnable: This type of cursors are generally used in stored procedures. This are mainly used to return set of records directly in application program. All the rows selected by the cursor are output at the same time.The steps for this cursor are only open and close statements. More than 1 cursor can be present in single application program.

Non-Returnable: This is similar to non-held cursor. It returns only 1 row at a time.