Restart implications for table spaces that are not logged

Even if all tables that are modified by a transaction reside in table spaces that are not logged, a unit of recovery is established before any of those updates are performed. Undo processing continues to read the log in the backward direction, looking for undo log records that need to be applied. Undo processing stops when it detects the beginning of this unit of recovery as recorded on the log.

Therefore you still need to perform frequent commits to limit the distance that undo processing might need to go backward on the log to find the beginning of the unit of recovery. If a transaction that is not logged does not do frequent commits, it is subject to being reported as a long-running unit of recovery in message DSNR035I. This means that if such a unit of recovery persists for a duration that qualifies as a long-running unit of recovery, message DSNR035I is issued to identify it.

If, during restart, you need to undo work that has not been logged because of the NOT LOGGED attribute, the table space loses its data integrity, and therefore must be recovered. Recovery can be accomplished by using the RECOVER utility or by reinserting the appropriate data. For example, a summary table can be re-created by one or more INSERT statements; a materialized query table can be rebuilt by using a REFRESH TABLE SQL statement.

To mark the need for recovery, the table space or partition is marked with RECOVER-pending status. To prevent any access to the corrupt data, the table space or partition is placed in the LPL. When undo processing places a table space or partition in the logical page list (LPL) and marks it with RECOVER-pending status, it also places all of the updated indexes on all tables in the table space in the LPL. The corresponding partitions of data-partitioned secondary indexes (DPSIs) are placed in the LPL, which prevents other processes that use index-only access from seeing data whose integrity is in doubt. These indexes are also marked with REBUILD-pending status.

After restart, when Db2 is operational, if undo processing is needed for a unit of recovery in which modifications were made to the table space that was not logged, the entire table space or partition is placed in the LPL, and the table space is marked with RECOVER-pending status. This can happen, for example, as a result of a rollback, abort, trigger error, or duplicate key or referential constraint violation. The LPL ensures that no concurrently running agent can see any of the data whose integrity is in doubt.

Avoid duplicate key or referential constraint violations in table spaces that are not logged because the result is an unavailable table space that requires manual action.

When a table space or partition is placed in the LPL because undo processing is needed for a table space that is not logged, either at restart time or during rollback processing, automatic LPL recovery is not initiated, and a START DATABASE command identifying this table space has no effect on its LPL status.