Data consistency for point-in-time recoveries

The RECOVER utility can automatically detect uncommitted transactions that are running at the recover point in time and roll back the changes on the recovered objects. After recovery, the objects are left in their transactionally consistent state.

RECOVER TOLOGPOINT and RECOVER TORBA have the recover with consistency as their default behavior. However, RECOVER TOCOPY, TOLASTCOPY, and TOLASTFULLCOPY using SHRLEVEL CHANGE image copy do not ensure data consistency.

RECOVER TOLOGPOINT and RECOVER TOCOPY can be used on a single:

  • Partition of a partitioned table space
  • Partition of a partitioning index space
  • Data set of a simple table space
Tip: If you take SHRLEVEL CHANGE image copies and need to recover to a prior point in time, then you can use the RBA or LRSN (the START_RBA syscopy column value) associated with image copy as the TOLOGPOINT value.

All page sets must be restored to the same level; otherwise the data is inconsistent.

Point-in-time recovery can cause table spaces to be placed in CHECK-pending status if they have table check constraints or referential constraints that are defined on them. When recovering tables that are involved in a referential constraint, you should recover all the table spaces that are involved in a constraint. This is the table space set.

To avoid setting CHECK-pending status, you must perform both of the following tasks:

  • Recover the table space set to a quiesce point.
    If you do not recover each table space of the table space set to the same quiesce point, and if any of the table spaces are part of a referential integrity structure:
    • All dependent table spaces that are recovered are placed in CHECK-pending status with the scope of the whole table space.
    • All table spaces that are dependent on the table spaces that are recovered are placed in CHECK-pending status with the scope of the specific dependent tables.
  • Establish a quiesce point or take an image copy after you add check constraints or referential constraints to a table.

    If you recover each table space of a table space set to the same quiesce point, but referential constraints were defined after the quiesce point, the CHECK-pending status is set for the table space that contains the table with the referential constraint.

The RECOVER utility sets various states on table spaces. The following point-in-time recoveries set various states on table spaces:

  • When the RECOVER utility finds an invalid column during the LOGAPPLY phase on a LOB table space, it sets the table space to auxiliary-warning (AUXW) status.
  • When you recover a LOB or XML table space to a point in time that is not a quiesce point or to an image copy that is produced with SHRLEVEL CHANGE, the LOB or XML table space is placed in CHECK-pending (CHKP) status.
  • When you recover the LOB or XML table space, but not the base table space, to any previous point in time, the base table space is placed in auxiliary CHECK-pending (ACHKP) status, and the index space that contains an index on the auxiliary table is placed in REBUILD-pending (RBDP) status.
  • When you recover only the base table space to a point in time, the base table space is placed in CHECK-pending (CHKP) status.
  • When you recover only the index space that contains an index on the auxiliary table to a point in time, the index space is placed in CHECK-pending (CHKP) status.
  • When you recover partitioned table spaces with the RECOVER utility to a point in time that is prior to the redistribution of data across partitions, all affected partitions are placed in REORG-pending (REORP) status.
  • When you recover a table space to point in time prior to when an identity column was defined with the RECOVER utility, the table space is placed in REORG-pending (REORP) status.
  • If you do not recover all objects that are members of a referential set to a prior point in time with the RECOVER utility, or if you recover a referential set to a point in time that is not a point of consistency with the RECOVER utility, all dependent table spaces are placed in CHECK-pending (CHKP) status.
  • When you recover a table space to a point in time prior to the REORG or LOAD REPLACE that first materializes the default value for the row change timestamp column, the table space that contains the table with the row change timestamp column is placed in REORG-pending (REORP) status.
Important: The RECOVER utility does not back out CREATE or ALTER statements. After a recovery to a previous point in time, all previous alterations to identity column attributes remain unchanged. Because these alterations are not backed out, a recovery to a point in time might put identity column tables out of sync with the SYSIBM.SYSSEQUENCES catalog table. You might need to modify identity column attributes after a recovery to resynchronize identity columns with the catalog table.

If a table space or partition that is in reordered row format is recovered to a point in time when the table space or partition was in basic row format, the table space or partition will revert to basic row format.

Note: The default behavior for a RECOVER utility job with the TOLOGPOINT option or the TORBA option is to skip unnecessary recoveries. See the SCOPE option in Syntax and options of the RECOVER control statement.