How Db2 rolls back work

If failure occurs within a unit of recovery, Db2 rolls back (backs out) any changes to data. Rolling back returns the data to its state at the start of the unit of recovery; that is, Db2 undoes the work.

For a partition-by-growth table space, if a new partition was added in the unit of recovery, any uncommitted updates can be backed out, but the physical partition is not deleted.

The events are shown in the following figure.

Figure 1. Unit of recovery (rolling back)
Begin figure description. This figure depicts a unit of recovery relative to time. End figure description.
Begin general-use programming interface information.The possible events that trigger "Begin rollback" in this figure include:
  • SQL ROLLBACK statement
  • Deadlock (reported as SQLCODE -911)
  • Timeout (reported as SQLSTATE 40001)

The effects of inserts, updates, and deletes to large object (LOB) values are backed out along with all the other changes that were made during the unit of work that is being rolled back, even if the LOB values that were changed reside in a LOB table space that has the LOG NO attribute.

An operator or an application can issue the CANCEL THREAD command with the NOBACKOUT option to cancel long-running threads without backing out data changes. Db2 backs out changes to catalog and directory tables regardless of the NOBACKOUT option. As a result, Db2 does not read the log records and does not write or apply the compensation log records. After CANCEL THREAD NOBACKOUT processing, Db2 marks all objects that are associated with the thread as refresh-pending (REFP) and puts the objects in a logical page list (LPL).

The NOBACKOUT request might fail for either of the following two reasons:
  • Db2 does not completely back out updates of the catalog or directory (message DSNI032I with reason 00C900CC).
  • The thread is part of a global transaction (message DSNV439I).
End general-use programming interface information.