Making catalog definitions consistent with your data after recovery to a prior point in time

Avoiding point-in-time recovery of the catalog is easier than attempting to correct the inconsistencies that this kind of recovery causes.

To make catalog definitions consistent with your data after a point-in-time recovery:

1. Run the DSN1PRNT utility with the PARM=(FORMAT, NODATA) option on all data sets that might contain user table spaces. The NODATA option suppresses all row data, which reduces the output volume that you receive. Data sets that contain user tables are of the following form, where y can be either I or J:

   catname.DSNDBC.dbname.tsname.y0001.A00n

2. Execute the following SELECT statements to find a list of table space and table definitions in the Db2 catalog:

   SELECT NAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE;
   SELECT NAME, TSNAME, DBID, OBID FROM SYSIBM.SYSTABLES;

   
3. For each table space name in the catalog, look for a data set with a corresponding name. If a data set exists, take the following additional actions:
   1. Locate the DBID, PSID, and OBID values for the table space in the DSN1PRNT output.

      DBID

      The first two bytes in the 4-byte field HPGOBID of the header page contain the DBID for the database.

      PSID

      The last two bytes in field HPGOBID of the header page contain the PSID for the table space.

      OBID

      For universal (UTS), partitioned (non-UTS), or LOB table spaces, the HPGROID field of the header page contains the OBID for the single table in the table space.

      For segmented (non-UTS) table spaces, field SEGOBID in the space map page contains the OBIDs. If the table space contains more than one table, you must specify all OBIDs from the data set as input to the DSN1COPY utility.

   2. Check if the corresponding table space name in the Db2 catalog has the same DBID and PSID.
   3. If the DBID and PSID do not match, execute DROP TABLESPACE and CREATE TABLESPACE statements to replace the incorrect table space entry in the Db2 catalog with a new entry. Be sure to make the new table space definition exactly like the old one. If the table space is segmented, SEGSIZE must be identical for the old and new definitions.
      You can drop a LOB table space only if it is empty (that is, it does not contain auxiliary tables). If a LOB table space is not empty, you must first drop the auxiliary table before you drop the LOB table space. To drop auxiliary tables, you can perform one of the following actions:

      • Drop the base table.
      • Delete all rows that reference LOBs from the base table, and then drop the auxiliary table.
   4. If any of the OBIDs in the table space do not have matching table definitions, examine the DSN1PRNT output to determine the structure of the tables that are associated with these OBIDs. If you find a table whose structure matches a definition in the catalog, but the OBIDs differ, proceed to the next step. The OBIDXLAT option of DSN1COPY corrects the mismatch. If you find a table for which no table definition exists in the catalog, re-create the table definition by using the CREATE TABLE statement. To re-create a table definition for a table that has had columns added, first use the original CREATE TABLE statement, and then use ALTER TABLE to add columns, which makes the table definition match the current structure of the table.
   5. Use the DSN1COPY utility with the OBIDXLAT option to copy the existing data to the new tables in the table space, and translate the DBID, PSID, and OBIDs.

   If a table space name in the Db2 catalog does not have a data set with a corresponding name, one of the following events has probably occurred:

   • The table space was dropped after the point in time to which you recovered. In this case, you cannot recover the table space. Execute DROP TABLESPACE to delete the entry from the Db2 catalog.
   • The table space was defined with the DEFINE(NO) option. In this case, the data set is allocated when you insert data into the table space.
4. For each data set in the DSN1PRNT output, look for a corresponding Db2 catalog entry. If no entry exists, follow the instructions in Recovering an accidentally dropped table space to re-create the entry in the Db2 catalog.
5. If you recover the catalog tables SYSSEQ and SYSSEQ2, identity columns and sequence objects are inconsistent. To avoid duplicate identity column values, recover all table spaces that contain tables that use identity columns to the point in time to which you recovered SYSSEQ and SYSSEQ2. To eliminate gaps between identity column values, use the ALTER TABLE statement. For sequence objects, use the ALTER SEQUENCE statement to eliminate these gaps.
6. Ensure that the IPREFIX values of user table spaces and index spaces that were reorganized match the IPREFIX value in the VSAM data set names that are associated with each table space or partition. If the IPREFIX that is recorded in the Db2 catalog and directory is different from the VSAM cluster names, you cannot access your data. To ensure that these IPREFIX values match, complete the following procedure:
   1. Query the SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART catalog tables to determine the IPREFIX value that is recorded in the catalog for objects that were reorganized.
   2. Compare this IPREFIX value to the IPREFIX value in the VSAM data set name that is associated with the table space or index space.
   3. When IPREFIX values do not match for an object, rename the VSAM data set to specify the correct IPREFIX.
      Important: For objects involved in cloning, rename the base and clone objects at the same time.

   Example: Assume that the catalog specifies an IPREFIX of J for an object but the VSAM data set that corresponds to this object is .

   catname.DSNDBC.dbname.spname.I0001.A001

   You must rename this data set to:

   catname.DSNDBC.dbname.spname.J0001.A001

7. Delete the VSAM data sets that are associated with table spaces that were created with the DEFINE NO option and that reverted to an unallocated state. After you delete the VSAM data sets, you can insert or load rows into these unallocated table spaces to allocate new VSAM data sets.