SQL Rollback: How to Understand Its Background Process

In DB2 or any database, Rollback initiates if any exception occurs. A Rollback automatically sets the data to the previous commit. Moreover, below you will find what happens to the cursors and locks.

Rollback

Here is the process that triggers after the Rollback enacts in SQL.

The ROLLBACK causes:

All open cursors are closed.
All locks acquired during the unit of work are released.
Freed all LOB locators
When you specify the “WITH HOLD” option in the SELECT statement, the cursors will not be closed. It always puts the cursor at the next logical row.
Open cursors defined WITH HOLD remain open.
All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors.
After committing, if we define the “DISCONNECT” unless the cursor is explicitly closed, the DISCONNECT will be failed. It is needed once the cursor is closed to re-open it after ROLLBACK.