Recovering a dropped table from a simple table space

To recover a dropped table from a simple table space

1. Obtain data definitions.

   Obtain the definition for the dropped table. You cannot change any of the table’s characteristics that affect the internal structure. If you also want to create the indexes, obtain the definitions of the indexes.

   Important

   You can obtain the object definitions by using a tool such as one of the following BMC products: BMC AMI Catalog Manager for Db2, ALTER for Db2, or BMC AMI Change Manager for Db2.

2. Create Db2 objects.

   Using the DDL obtained in Step 1, create the table and indexes.

3. Find the LRSN prior to dropping the table.

   You can use the DSN1LOGP utility to find the LRSN at which the table was dropped. Because the table was dropped after the last copy, you supply this LRSN for the DSN1LOGP STARTRBA. You could also use Log Master to find the LRSN.

   Search the log output from the DSN1LOGP utility to find the update to the SYSDBASE table space, DBID(0006), and OBID(0009), which removed the definition for the dropped table. Use the URID of this event (or for data sharing, the LRSN of the log record at the URID RBA) for the TORBA/TOLOGPOINT value in the recovery. Locate the time frame of an archive log that would contain the DROP TABLE statement for coding in this example. (DSNJU003 produces a print log map that might help you locate this information.)

   The following figure shows an example JCL for the DSN1LOGP utility.

   //DSN01 EXEC PGM=DSN1LOGP 
   //STEPLIB  DD DSN=SYS2.DB2.PROD.DSNLOAD,DISP=SHR 
   //         DD DSN=SYS2.DB2.PROD.DSNSAMP,DISP=SHR 
   //         DD DSN=SYS3.DBAJ.DSNEXIT,DISP=SHR 
   //SYSPRINT DD SYSOUT=* 
   //SYSSUMRY DD SYSOUT=* 
   //ARCHIVE  DD DSN=DBAJCAT.ARCHLOG1.A0000567,DISP=SHR 
   //SYSIN    DD * 
     STARTRBA(00039F08E8D2) DBID(0006) OBID(0009)

   The following figure shows example DSN1LOGP utility output.

   =======================================SEARCH CRITERIA======================================== 
    STARTRBA(00039F08E8D2)  ENDRBA(FFFFFFFFFFFF) 
    SUMMARY(NO) 
    DATAONLY(NO) 
    SYSCOPY(NO) 
    ALL URIDS     === YOU MAY SPECIFY URID(XXXXXXXXXXXX) 
    ALL LUWIDS    === YOU MAY SPECIFY LUWID(NNNNNNNN.LLLLLLLL.XXXXXXXXXXXX.XXXX) 
    DBID(0006)    OBID(0009) 
    ALL PAGES     === YOU MAY SPECIFY PAGE(XXXXXX) MANY TIMES 
    ALL TYPES     === YOU MAY SPECIFY TYPE(XX) 
    ALL SUBTYPES  === YOU MAY SPECIFY SUBTYPE(XX) 
   =33============================================================================================ 
   
   DSN1212I DSN1LGRD FIRST LOG  RBA ENCOUNTERED 00039F08E8D2 
   
   (LINES OMITTED) 
   
   00039F0AA80E  URID(00039F0A578A)  LRSN(AE68AC13F86E)  DBID(0006) 
                 OBID(0009)  PAGE(000243)  TYPE( UNDO  REDO ) 
                 SUBTYPE(UPDATE IN-PLACE IN A DATA PAGE)  CLR(NO) 
                 PROCNAME(DSNILGBW) 
        *LRH* 004A007D 06000001 0E800000 C386CDD9 0000C386 D41E0126 0000C386 D41EAE68  * ¢ ' Cf R  CfM     CfM 
              AC13F86E 0000                                                            *  8> 
        *LG** 10000600 09000243 27000000 C386D115 0000                                 *            CfJ 
        0000  0012000C 00170900 00160000 00000002 4312                                 * 
   
   00039F0AA959  URID(00039F0A578A)  LRSN(AE68AC13F871)  DBID(0006) 
                 OBID(0009)  PAGE(000243)  TYPE( UNDO  REDO ) 
                 SUBTYPE(DELETE IN A DATA PAGE)  CLR(NO) 
                 PROCNAME(DSNIDILS) 
        *LRH* 00D5004A 06000001 0E800000 C386CDD9 0000C386 D49B0126 0000C386 D49BAE68  * N ¢ Cf R  CfM     CfM 
              AC13F871 0000                                                            *  8 
        *LG** 10000600 09000243 39000000 C386D49B 0000                                 *            CfM 
        0000  009D2012 00170000 00009500 17120002 438C0002 438C0002 43140002 43130008  *          n 
        0020  C3D9C9E3 C9E7F0F2 E7E8E9C4 C2F0F140 0008C3D9 C9E3E3C2 F0F2E7E8 E9C4C2F0 *DMBTABLEORIGINAL DMBTABLEORIGIN
        0040  F140E480 01E8E881 34800780 08E7E8E9 C4C2F0F1 40C3D9C9 E3C9E7F0 F27FFFFF  *ALU  YYa DMBTABLEORIGINAL
        0060  FF7FFFFF FF7FFFFF FF7FFFC2 D7F04040 40404090 00D54040 40404040 4040E880  *           BP0 N Y 
        0080  000000C7 8000D9C4 C1C4D3C2 40407FFF 7FFF0001 01010000 00000000 F2        *   G  RDADMB 2 
   
   (LINES OMITTED) 
   
   DSN1213I LAST LOG RBA ENCOUNTERED 00039F0AF68C 
   
   DSN1214I NUMBER OF LOG RECORDS READ 0000000000000666

4. Create and run BMC AMI Recover JCL.

   Recover the table space using the OBIDXLAT option to translate the OBID found in the image copy to the OBID of the newly created table. Because the table space was not dropped, the SYSCOPY and SYSLGRNX information is still available and the DROPRECOVERY option is not required.

   BMC AMI Recover generates an image copy to allow for recoverability after the translation. The BMC AMI Recover OUTPUT command causes dynamic allocation of the image copy. (See OUTPUT command for details regarding the OUTPUT command and dynamic allocation of output data sets.) The following figure provides example BMC AMI Recover JCL for this step.

   //RECOVER  EXEC PGM=AFRMAIN,REGION=5M, 
   //            PARM='DBAJ,DMBBASIC,NEW,MSGLEVEL(1)' 
   //STEPLIB  DD DISP=SHR,DSN=product.libraries
   //         DD DISP=SHR,DSN=DB2.DSNEXIT
   //         DD DISP=SHR,DSN=DB2.DSNLOAD
   //SYSIN    DD * 
     OUTPUT CP00001 
        DSNAME RDADMB.COPY8(+1) 
        UNIT SYSDA 
        CATLG YES 
        MODELDCB SYS1.MODEL 
        SPACE (20,1) CYL 
     RECOVER TABLESPACE DMBDROPR.DMBSPAC1 
        OUTCOPY YES 
        OUTCOPYDDN CP00001 
        OBIDXLAT OBID( X'3', X'4') 
        TORBA X'000343F5F6EC'