Recovery of tables that contain identity columns

When you recover a table that contains an identity column, consider the point in time to which you recover. Your recovery procedure can depend on whether that identity column existed, or was not yet defined, at the point in time to which you recover.

The following considerations apply for each of these two cases.

Identity column already defined

If you recover to a point in time at which the identity column existed, you might create a gap in the sequence of identity column values. When you insert a row after this recovery, Db2 produces an identity value for the row as if all previously added rows still exist.

For example, assume that at time T1 an identity column that is incremented by 1 contains the identity column values 1 through 100. At T2, the same identity column contains the values 1 through 1000. Now, assume that the table space is recovered back to time T1. When you insert a row after the recovery, Db2 generates an identity value of 1001. This value leaves a gap from 101 to 1000 in the values of the identity column.

Begin general-use programming interface information.To prevent a gap in identity column values, use the following ALTER TABLE statement to modify the attributes of the identity column before you insert rows after the recovery: 

ALTER TABLE table-name 
 ALTER COLUMN identity-column-name
 RESTART WITH next-identity-value
End general-use programming interface information.
Tip: To determine the last value in an identity column, issue the MAX column function for ascending sequences of identity column values, or the MIN column function for descending sequences of identity column values. This method works only if the identity column does not use CYCLE.

Identity column not yet defined

If you recover to a point in time at which the identity column was not yet defined, that identity column remains part of the table. The resulting identity column no longer contains values.

A table space that contains an identity column is set to REORG-pending (REORP) status if you recover the table space to a point in time that is before the identity column was defined. To access the recovered table, you need to remove this status.