Recovering to a point in time before pending definition changes were materialized

You can recover a partition-by-growth (UTS) table space, a partition-by-range table space, a LOB table space, or an XML table space to a point in time before you materialized pending definition changes.

To recover a table space to a point in time that is before materialization of pending definition changes:

1. Run the RECOVER utility to recover the data to the point in time that you want.

   If you specify the TOCOPY, TOLASTCOPY, or TOLASTFULLCOPY option, you need to use an image copy that was taken with the SHRLEVEL REFERENCE option. If no appropriate image copies are available, you can run RECOVER with the TOLOGPOINT or TORBA option.

   For most types of pending definition changes, the table space is placed in REORG-pending (REORP) status after the RECOVER utility runs. Changes to partition limit keys and column definitions are exceptions that require no subsequent REORG.

   Restrictions: After you complete this step, and before you complete the next step, you cannot perform any of the following actions:

   • Execute any of the following statements on the table space, on any objects in the table space, on indexes that are related to tables in the table space, or on auxiliary objects that are associated with the table space:
     • CREATE TABLE
     • CREATE AUXILIARY TABLE
     • CREATE INDEX
     • ALTER TABLE
     • ALTER INDEX
     • RENAME
     • DROP TABLE
   • Execute SQL statements that result in pending definition changes on any of the following objects:
     • The table space
     • Tables in the table space
     • Auxiliary table spaces that are related to the table space
     • Indexes on tables in the table space
   • Run any utilities that are not in this list:
     • RECOVER to the same point in time
     • REORG
     • REPAIR DBD
     • REPORT RECOVERY
2. If the table space is in REORG-pending (REORP) status, run the REORG TABLESPACE utility with SHRLEVEL REFERENCE on the entire table space to complete the point-in-time recovery process.

   If there are pending definition changes on a base table space and on the LOB table space for a LOB column in the base table space, run REORG on the LOB table space first, and then run REORG on the base table space.

Example

The following example provides a scenario that shows how you can recover a table space to a point in time before pending definition changes were materialized, and then use the REORG TABLESPACE utility with SHRLEVEL REFERENCE to complete recovery.

1. You execute the following ALTER TABLESPACE statement to change the buffer pool page size. This change is a pending definition change.

   ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20 ;

2. You run REORG to materialize the pending definition change.
3. You run the following RECOVER control statement to recover the table space to point in time 2012-10-09-07.15.22.216020.

   RECOVER TABLESPACE DB1.TS1
           TOLOGPOINT X'00000551BE7D'

   When this statement runs, the table space is placed in REORG-pending (REORP) state, and an entry is inserted into the SYSPENDINGDDL table with OBJTYPE = 'S', for table space.

4. You run the following SELECT statement to query the SYSIBM.SYSPENDINGDDL catalog table:

   SELECT DBNAME, TSNAME, OBJSCHEMA, OBJNAME, OBJTYPE, OPTION_SEQNO,
   OPTION_KEYWORD, OPTION_VALUE, CREATEDTS
   FROM SYSIBM.SYSPENDINGDDL
   WHERE DBNAME = 'DB1'
   AND TSNAME = 'TS1'
   ;

   This query results in the following output:

   Table 1. Output from the SELECT statement for the SYSPENDINGDDL catalog table after RECOVER to a point in time before materialization of pending definition changes

   DBNAME	TSNAME	OBJSCHEMA	OBJNAME	OBJTYPE
   DB1	TS1	DB1	TS1	S

   Table 2. Continuation of output from the SELECT statement for the SYSPENDINGDDL catalog table after RECOVER to a point in time before materialization of pending definition changes

   OPTION_SEQNO	OPTION_KEYWORD	OPTION_VALUE	CREATEDTS
   1	TOLOGPOINT	00000551BE7D	2012-10-04- 07.14.20.204010

   

5. Now, you run the REORG TABLESPACE utility with SHRLEVEL REFERENCE on the entire table space. For example:

   REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE

   The REORG utility completes point-in-time recovery. After the REORG utility runs, the REORG-pending (REORP) state is cleared, and all entries in the SYSPENDINGDDL table for the table space are removed.