Recovering an accidentally dropped table

Tables in a partitioned table space cannot be dropped without dropping the table space.

To perform this procedure, you need a full image copy or a DSN1COPY file that contains the data from the dropped table.

For segmented or universal table spaces, the image copy or DSN1COPY file must contain the table when it was active (that is, created). Because of the way space is reused for segmented table spaces, this procedure cannot be used if the table was not active when the image copy or DSN1COPY was made. For nonsegmented table spaces, the image copy or DSN1COPY file can contain the table when it was active or not active.

To recover a dropped table:

1. If you know the DBID, the PSID, the original OBID of the dropped table, and the OBIDs of all other tables in the table space, go to step 2.

If you do not know all of the preceding items, use the following steps to find them. For later use with DSN1COPY, record the DBID, the PSID, and the OBIDs of all the tables in the table space, not just the dropped table.

1. For the data set that contains the dropped table, run DSN1PRNT with the FORMAT and NODATA options. Record the HPGOBID field in the header page and the PGSOBD field from the data records in the data pages.

For the auxiliary table of a LOB table space, record the HPGROID field in the header page instead of the PGSOBD field in the data pages.

• Field HPGOBID is 4 bytes long and contains the DBID in the first 2 bytes and the PSID in the last 2 bytes.
• Field HPGROID (for LOB table spaces) contains the OBID of the table. A LOB table space can contain only one table.
• Field PGSOBD (for non-LOB table spaces) is 2 bytes long and contains the OBID of the table. If your table space contains more than one table, check for all OBIDs. By searching for all different PGSOBD fields. You need to specify all OBIDs from the data set as input for the DSN1COPY utility.
1. Convert the hex values in the identifier fields to decimal so that they can be used as input for the DSN1COPY utility.
1. Use the SQL CREATE statement to re-create the table and any indexes on the table.
2. To allow DSN1COPY to access the DB2 data set, stop the table space using the following command:

-STOP DATABASE(database-name) SPACENAM(tablespace-name)

Stopping the table space is necessary to ensure that all changes are written out and that no data updates occur during this procedure.

4. Find the OBID for the table that you created in step 2 by querying the SYSIBM.SYSTABLES catalog table.

The following statement returns the object ID (OBID) for the table:

SELECT NAME, OBID FROM SYSIBM.SYSTABLES

  WHERE NAME='table_name'

    AND CREATOR='creator_name';

This value is returned in decimal format, which is the format that you need for DSN1COPY.

5. Run DSN1COPY with the OBIDXLAT and RESET options to perform the OBID translation and to copy data from the dropped table into the original data set. You must specify a previous full image copy data set, inline copy data set, or DSN1COPY file 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 both the translation source and the translation target
3. The original OBID that you recorded in step 1 for the dropped table as the translation source and the OBID that you recorded in step 4 as the translation target
4. OBIDs of all other tables in the table space that you recorded in step 2 as both the translation sources and translation targets

Be sure that you have named the VSAM data sets correctly by checking messages DSN1998I and DSN1997I after DSN1COPY completes.

6. Use DSN1COPY with the OBIDXLAT and RESET options to apply any incremental image copies. You must apply these incremental copies in sequence, and specify the same SYSXLAT records that step 5 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 Log Analysis Tool for z/OS at this point in the recovery procedure.

7. Start the table space for normal use by using the following command:

-START DATABASE(database-name) SPACENAM(tablespace-name)

8. Rebuild all indexes on the table space.
9. Execute SELECT statements on the previously dropped table to verify that you can access the table. Include all LOB columns in these queries.
10. Make a full image copy of the table space.
11. Re-create the objects that are dependent on the recovered table.

When a table is dropped, objects that are dependent on that table (synonyms, views, indexes, referential constraints, and so on) are dropped. (Aliases are not dropped.) Privileges that are granted for that table are also dropped. Catalog reports or a copy of the catalog taken prior to the DROP TABLE can make this task easier.

Sample JCL:

//H136830$ JOB MSGCLASS=X,MSGLEVEL=(1,1),CLASS=A,

// REGION=0M,NOTIFY=&SYSUID

//EXECUTE EXEC PGM=DSN1COPY,PARM='OBIDXLAT,RESET,INLCOPY'

//*EXECUTE EXEC PGM=DSN1COPY,PARM='OBIDXLAT,RESET'

//STEPLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR

//SYSPRINT DD SYSOUT=A

//SYSUT1 DD DSN=full image copy dataset,DISP=SHR

//SYSUT2 DD DSN=tablespace VSAM file,DISP=SHR

//SYSXLAT DD *

273,273

42,42

43,43

44,44

60,61 (60-> OBID of source and 61-> OBID of target)

/*

//