DB2 ROLLBACK these things you need to know

Rollback is an important object in DB2. If exception occurred DB2 automatically Rollback to previous commit.

For units of work ending with ROLLBACK:

  • All open cursors are closed.
  • All locks acquired during the unit of work are released.
  • LOB locators are freed.

When “WITH HOLD” option is defined in select statement, the cursors will not be closed. It always puts the cursor at 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.

Also Read:

After commit if we define the “DISCONNECT”, unless cursor is explicitly closed, the DISCONNECT will be failed.

Once cursor is closed, we need to re-open the cursor after ROLLBACK occurred. In my next post I will give important LOAD options

Until then cheers…


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.