Recovering a dropped table in a segmented table space containing multiple tables

Your challenge is how to recover this dropped table without regressing the updates that occurred to the other tables.

Using the BMC AMI Recover OBIDXLAT and INDEPENDENT OUTSPACE options, your problem is solved. To preserve the updates to the other tables, this process recovers the entire original table space into a temporary table space at the point just prior to dropping the table. The rows from the temporary table space table that corresponds to the dropped table will then be inserted into the newly created table within the original table space.

To recover a dropped table in a segmented table space containing multiple tables

1. Obtain data definitions.Obtain the definitions for the table space and all of its tables. If you also want to create the indexes, obtain the definitions of the indexes. (Though it is not necessary to include the indexes in this process, you might want to create them so that you can more easily execute verification queries later.)

   Warning

   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 you obtained in Step 1, create the temporary table space with table and table column definitions identical to the original table space. (Do not change any characteristics of the table space that affect its internal structure.) Also, create the dropped table, along with its indexes, within the original table space.

3. Obtain internal object identifiers for the original table space.Obtain the DBID and PSID of the original table space. Obtain the OBIDs of each of the tables within the table space, including the OBID of the table that was dropped. To obtain this information, print the first two pages of the image copy by using the IBM Db2 stand-alone utility DSN1PRNT. See the following figures for an example of DSN1PRNT JCL and of DSN1PRNT output.

   The DBID, PSID, and OBIDs (except those for the original dropped table) are also available from the Db2 system catalog. See Step 4 for more information about obtaining object identifiers from the catalog.

   //DSN01 EXEC PGM=DSN1PRNT,PARM='PRINT(0,1),FORMAT'
   //STEPLIB   DD DSN=SYS2.DB2.PROD.DSNLOAD,DISP=SHR
   //          DD DSN=SYS3.DB2R.DSNEXIT,DISP=SHR
   //SYSPRINT  DD SYSOUT=*
   //SYSUT1    DD DISP=SHR,DSN=RDADMB.COPY8.G0028V00       <= IMAGE COPY DSN

   1DSN1999I START OF DSN1PRNT FOR JOB RDAMSMPR RUNPRNT
   DSN1998I INPUT DSNAME = RDAMSM.ICDBAJ.CRITTS01.G0003V00 . SEQ
   
   HEADER PAGE: PGCOMB='10'X PGLOGRBA='000000000000'X PGLOGID='FF'X PGNUMBER='000000'X
   PGFLAGS='38'X HPGOBID='014C0002'X HPGHPREF='00000001'X HPGREL='C6'X
   HPGTORBA='000000000000'X HPGTSTMP='00010101000000000000'X HPGSSNM='DBAJ'
   HPGFOID='0001'X HPGPGSZ='1000'X HPGSGSZ='0008'X HPGPARTN='0000'X
   HPGZPNUM='000000'X HPGZNUMP='00'X HPGTBLC='0001'X HPGROID='0003'X HPGMAXL='0039'X
   HPGNUMCO='0004'X HPGFLAGS='00'X HPGCONTM='19941212081533515711'X
   HPGSGNAM='SYSDEFLT' HPGVCATN='DBAJCAT ' HPGRBRBA='0003082F5630'X FOEND='N'
   SPACE MAP PAGE: PGCOMB='00'X PGLOGRBA='000000000000'X PGLOGID='FF'X PGNUMBER='000001'X
   PGFLAGS='30'X SEGNUM='0152'X SEGFREE='0151'X SEGENT='0002'X SEGSIZE='0008'X
   SEGLENT='00000001'X FOEND='E'
   FIRST PART OF SEGMENTED SPACE MAP:
   SEG 0001 000000000003C0 30000000
   
   SECOND PART OF SEGMENTED SPACE MAP:
   RELPG 00  20  40  60  80  A0  C0  E0
   0000 00000000
   
   DATA PAGE: PGCOMB='10'X PGLOGRBA='000000000000'X PGLOGID='FF'X PGNUMBER='000002'X PGFLAGS='00'X
   PGFREE=3034 PGFREE='0BDA'X PGFREEP=1028 PGFREEP='0404'X PGHOLE1='0000'X
   PGMAXID='10'X PGNANCH=0
   PGTAIL: PGIDFREE='00'X PGEND='N'
   ID-MAP FOLLOWS:
   01 0014 0053 0092 00D1 0110 014F 018E 01CD
   09 020C 024B 028A 02C9 0308 0347 0386 03C5
   
   RECORD: OFFSET='0014'X PGSFLAGS='00'X PGSLTH=63 PGSLTH='003F'X PGSOBD='0003'X PGSID='01'X
   80002711 D1D6C540 40404040 40404040 40404040 40404040 E2D4C9E3 C8404040 ....JOE SMITH
   40404040 40404040 40404040 40404040 40404040 4040C9D5 C4    IND
   
   RECORD: OFFSET='0053'X PGSFLAGS='00'X PGSLTH=63 PGSLTH='003F'X PGSOBD='0003'X PGSID='02'X
   80002712 D1C1D5C5 40404040 40404040 40404040 40404040 C4D6C540 40404040 ....JANE DOE
   40404040 40404040 40404040 40404040 40404040 4040D4C7D9    MGR

   The first two bytes of HPGOBID are the hexadecimal representation of the DBID (X‘014D’ in the example output in the preceding example). The last two bytes of HPGOBID are the hexadecimal representation of the PSID of the table space (X‘000A’ in the example output in the preceding example).

   Notice that the OBIDs X‘000B’, X‘000E’ and X‘0011’ are found in the segment entries. By comparing these to the OBIDs in SYSIBM.SYSTABLES for this table space, you can determine which tables are still present and which table has been dropped.

4. Obtain internal object identifiers for the temporary table space.Obtain the DBID and PSID values of the temporary table space. Obtain the OBID values of each of the tables within this table space. You can query the Db2 system catalog to obtain these identifiers. For an example query, see the following figure.

    DSN1999I START OF DSN1PRNT FOR JOB RDADMB08 DSN01
    DSN1998I INPUT DSNAME = RDADMB.COPY8.G0028V00                       , SEQ
   
    HEADER PAGE:  PGCOMB='00'X  PGLOGRBA='000000000000'X  PGLOGID='FF'X  PGNUMBER='000000'X
    PGFLAGS='38'X  HPGOBID='014D000A'X  HPGHPREF='00000001'X  HPGREL='C6'X
    HPGTORBA='000000000000'X  HPGTSTMP='00010101000000000000'X  HPGSSNM='DBAJ'
    HPGFOID='0009'X  HPGPGSZ='1000'X  HPGSGSZ='0010'X  HPGPARTN='0000'X
    HPGZPNUM='000000'X  HPGZNUMP='00'X  HPGTBLC='0002'X  HPGROID='0000'X  HPGMAXL='0000'X
    HPGNUMCO='0000'X  HPGFLAGS='00'X  HPGCONTM='19941215123751855917'X
    HPGSGNAM='DMBDROPR'  HPGVCATN='DBAJCAT '  HPGRBRBA='0003509C1EAE'X  FOEND='E'
    SPACE MAP PAGE:  PGCOMB='10'X  PGLOGRBA='0003509C159D'X  PGLOGID='01'X  PGNUMBER='000001'X
    PGFLAGS='30'X  SEGNUM='00EF'X  SEGFREE='00EC'X  SEGENT='0004'X  SEGSIZE='0010'X
    SEGLENT='00000021'X  FOEND='N'
    FIRST PART OF SEGMENTED SPACE MAP:
    SEG 0001   00000000000BC0 3000000000000000
    SEG 0002   00000000000EC0 3330000000000000
    SEG 0003   000000000011C0 3000000000000000
   
    SECOND PART OF SEGMENTED SPACE MAP:
    RELPG  00       20       40       60       80       A0       C0       E0
    0000   00000000 00000000
   
   
    DSN1994I DSN1PRNT COMPLETED SUCCESSFULLY,  00000002 PAGES PROCESSED

   SELECT A.DBNAME, A.TSNAME, A.NAME,
          HEX(A.DBID), HEX(B.PSID), HEX(A.OBID)
     FROM SYSIBM.SYSTABLES A,
          SYSIBM.SYSTABLESPACE B
     WHERE A.TSNAME = B.NAME
           AND A.DBNAME = B.DBNAME
           AND A.TSNAME = 'DMBSPAC2'
           AND A.DBNAME = 'DMBDROPS'
     ORDER BY A.NAME;
   
   ---------+---------+---------+---------+---------+---------+---------+--
   DBNAME     TSNAME     NAME
   ---------+---------+---------+---------+---------+---------+---------+--
   DMBDROPS   DMBSPAC2   DMBTBL1           015E  0002  0003
   DMBDROPS   DMBSPAC2   DMBTBL2           015E  0002  0006
   DMBDROPS   DMBSPAC2   DMBTBL3           015E  0002  0009

5. Find the log record sequence number (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 use the LRSN of this copy for the DSN1LOGP STARTRBA or STARTLRSN. The log output from the DSN1LOGP utility is then searched to find the update to the SYSDBASE table space, DBID(0006) and OBID(0009), which removed the definition for the dropped table. The URID of this event (or for data sharing, the LRSN of the log record at the URID RBA) is used for the TORBA/TOLOGPOINT value in the recovery. Refer to figures in Step 3 of Recovering-a-dropped-table-from-a-simple-table-space for sample JCL and output.

   You could also use Log Master to find the LRSN at which the table was dropped.

6. Create and run BMC AMI Recover JCL.The original table space is recovered into the temporary table space using the BMC AMI Recover INDEPENDENT OUTSPACE option. The OBIDXLAT feature translates the internal identifiers found in the image copy to the identifiers of the temporary table space. Because the table space was not dropped, the SYSCOPY and SYSLGRNX information is still available and the DROPRECOVERY option is not required. For an example of BMC AMI Recover drop recovery JCL, see the following figure.

   //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 *
     RECOVER TABLESPACE DMBDROPR.DMBSPAC1
           OBIDXLAT RESET
             DBID( X'14D', X'152') PSID( X'A', X'2')
             OBID( X'B', X'3') OBID( X'E', X'6') OBID( X'11', X'9')
           INDEP OUTSPACE
             MODEL DBAJCAT.DSNDBC.DMBDROPS.DMBSPAC2.I0001.A001
           TORBA X'00031D928776'

7. Insert rows from the temporary table into the newly created table.Use SQL to insert the rows from the temporary table into the original table.

   INSERT INTO ORIGINAL.DMBTBL2
      SELECT * FROM TEMP.DMBTBL2;