CURSOR is used by the application program to retrieve and process the rows from the result set one by one. It 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 tables separates the complete set of cursors into two types. Those are -

Cursor	Decription
Read Only Cursor	The Read-Only cursor performs 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 on the columns in the retrieved row(s). Positioned UPDATE and positioned DELETE (FOR UPDATE OF clause) are allowed.

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

Data modification statement based cursors -

Modifying or deleting rows from the database table 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 table 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. 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 divides 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 beginning. NO SCROLL keyword used to specify the cursor is non-scrollable.
Scrollable Cursor	The scrollable cursor fetches row(s) as many times as required from the result table. The cursor moved 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 Scrollable Cursor	After generating the result table, the cursor is sensitive to insert, update, or delete operations performed on the database table. For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.
Insensitive Scrollable 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 divides 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 theWITH HOLD option is not specified, this is the default.

Mixed feature cursors -

The cursors with more than one of the above features come under this category. Those are -

Cursor	Decription
Sensitive Static Scrollable Cursor	Sensitive static scroll cursor is the cursor that is sensitive to UPDATE or DELETE operations performed on the database table after generating the result table. i.e., after opening the cursor.
Sensitive Dynamic Scrollable Cursor	Sensitive dynamic scroll cursor is the cursor that is sensitive to INSERT, UPDATE, or DELETE operations performed on the database table after generating the result table. i.e., after opening the cursor.

---