CURSOR is used to process a set of rows that are retrieved and process one by one by the application program.

The CURSOR can divide into various types based on its definition and usage in the application program. Those are -

  • Data modification based cursors
  • Data modification statement based cursors
  • Positioning based cursors
  • Modified data reflection-based cursors
  • Processing stability based cursors
  • Mixed feature cursors

Data modification based cursors -

The ability to modify the data in the database separates the whole set of cursors into two categories. Those are -

Cursor Decription
Read Only Cursor The read-only cursor is a cursor that is used to perform only read operations on the table or result table. FOR FETCH ONLY option used to declare READ ONLY Cursors.
Updatable Cursor The updatable cursor is used to perform updates to the columns in the retrieved row(s). Positioned UPDATE and positioned DELETE are allowed in updatable cursors.

Every cursor in the DB2 falls under any one of the above two categories.

Data modification statement based cursors -

The ability of data modification or deleting data from the database separates the cursors into two categories. Those are -

Cursor Decription
Cursor for UPDATE The cursor for the update is used to perform updates to the columns in the retrieved row(s). Positioned UPDATE is allowed in the cursor for the update.
Cursor for DELETE The cursor for DELETE is used to delete the rows from the table or result table. Positioned DELETE is allowed in the cursor for DELETE.

These two types of cursors are the type of updatable cursor.

Positioning based cursors -

Placing the cursor dynamically or sequentially at the resulting table rows separates the cursor into two types. Those are -

Cursor Decription
Non-scrollable Cursor / Serial Cursor The non-scrollable cursor processes the row(s) one by one from the table or result table. NO SCROLL keyword used to specify the cursor is non-scrollable.
Scrollable Cursor The scrollable cursor fetches row(s) many times from the result table. The cursor moved to through the result table using the position specified on the FETCH statement.

Modified data reflection-based cursors -

After opening the specific cursor, the modified data reflection into the cursor result table separates the cursor into two types. Those are -

Cursor Decription
Sensitive Cursor The cursor is sensitive to insert, update, or delete operations performed on the database table after generating the result table. For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.
Insensitive Cursor The cursor is not sensitive to insert, update, or delete operations performed on the database table after generating the result table. If INSENSITIVE is specified, the cursor is read-only.

Processing stability based cursors -

The ability of opened cursor processing data without closing after the COMMIT/ROLLBACK/SYNCPOINT commands execution separates the cursors into two types. Those are -

Cursor Decription
Cursor With Hold WITH HOLD prevents the cursor from closing when the COMMIT operation is executed before the CLOSE CURSOR. If WITH HOLD option specifies, COMMIT only commits the current unit of work.
Cursor Without Hold The cursor can be closed if any COMMIT operation is performed before the CLOSE CURSOR. If the HOLD option is not specified, this is the default.

Mixed feature cursors -

The cursors having more than one feature from the above comes under this category. Those are -

  • Sensitive Scroll Cursor
  • Sensitive Static Scrollable Cursor
  • Sensitive Dynamic Scrollable Cursor
  • Insensitive Scroll Cursor