Recovering user-managed data sets for accidentally dropped table spaces

You can recover dropped table spaces that reside in user-managed data sets. You must copy the data sets that contain the data from the dropped table space to redefined data sets.

About this task

To copy the data sets, you can use the OBID-translate function of the DSN1COPY utility.

Procedure

To recover a dropped data set:

Find the DBID for the database, the PSID for the dropped table space, and the OBIDs for the tables that are contained in the dropped table space.
1. Run the DSN1PRNT utility for the data set that contains the dropped table space, with the FORMAT and NODATA options to locate the following values for the table space:
  
  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. Convert the hex values in the identifier fields to decimal so that they can be used as input for the DSN1COPY utility.
Rename the data set that contains the dropped table space by using the IDCAMS ALTER command. Rename both the CLUSTER and DATA portion of the data set with a name that begins with the integrated catalog facility catalog name or alias.
Redefine the original Db2 VSAM data sets.
Use the access method services LISTCAT command to obtain a list of data set attributes. The data set attributes on the redefined data sets must be the same as they were on the original data sets.
Use SQL CREATE statements to re-create the table space, tables, and any indexes on the tables.
To allow the DSN1COPY utility to access the Db2 data sets, stop the table space by using the following command:
```
-STOP DATABASE(database-name) SPACENAM(tablespace-name)
```
This step is necessary to prevent updates to the table space during this procedure in the event that the table space has been left open.
Find the target identifiers of the objects that you created in step 4 (which consist of a PSID for the table space and the OBIDs for the tables within that table space) by querying the SYSIBM.SYSTABLESPACE and SYSIBM.SYSTABLES catalog tables.
The following statement returns the object ID for a table space; this is the PSID.
```
SELECT DBID, PSID FROM SYSIBM.SYSTABLESPACE
  WHERE NAME='tablespace_name' and DBNAME='database_name'
    AND CREATOR='creator_name';
```
The following statement returns the object ID for a table:
```
SELECT NAME, OBID FROM SYSIBM.SYSTABLES
  WHERE NAME='table_name'
    AND CREATOR='creator_name';
```
These values are returned in decimal format, which is the format that you need for the DSN1COPY utility.
Run DSN1COPY with the OBIDXLAT and RESET options to perform the OBID translation and to copy the data from the renamed VSAM data set that contains the dropped table space to the newly defined VSAM data set. Specify the VSAM data set that contains data from the dropped table space as the input data set SYSUT1 in the control statement. Specify each of the input records in the following order in the SYSXLAT file to perform OBID translations:
1. The DBID that you recorded in step 1 as both the translation source and the translation target
2. The PSID that you recorded in step 1 as the translation source and the PSID that you recorded in step 6 as the translation target
3. The original OBIDs that you recorded in step 1 as the translation sources and the OBIDs that you recorded in step 6 as the translation targets
Be sure that you have named the VSAM data sets correctly by checking messages DSN1998I and DSN1997I after DSN1COPY completes.
Use DSN1COPY with the OBIDXLAT and RESET options to apply any incremental image copies to the recovered table space. You must apply these incremental copies in sequence, and specify the same SYSXLAT records that step 7 specifies.
Important: After you complete this step, you have essentially recovered the table space to the point in time of the last image copy. If you want to use log records to perform forward recovery on the table space, you must use the IBM® Db2 UDB Log Analysis Tool for z/OS®.

For more information about point-in-time recovery, see Recovery of data to a prior point in time.
Start the table space for normal use by using the following command:
```
-START DATABASE(database-name) SPACENAM(tablespace-name)
```
Rebuild all indexes on the table space.
Execute SELECT statements on each table in the recovered table space to verify the recovery. Include all LOB columns in these queries.
Make a full image copy of the table space.
See Page set and data set copies for more information about the COPY utility.
Re-create the objects that are dependent on the table.
See step 11 of Recovering an accidentally dropped table for more information.