Recovering a dropped table space when image copies are available

You have been assigned to recover this table space to the point in time at which it was dropped. You have already identified the most recent image copy data set.

To recover a dropped table space when image copies are available

1. Obtain data definitions.Obtain the definitions of the table space and table that you want to recover. Also, obtain definitions for the indexes because they have also been dropped. You cannot change any characteristics of the table space or table that affect their internal structure.

   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. (If you use ALTER for DB2, it can systematically change object names using a name mask in a migrate profile, which could be helpful if many table spaces are involved.)

   The data definition language (DDL) for this example is shown in the following figure.

    CREATE TABLESPACE CRITTS01 IN XYZDB01
           FREEPAGE 0
           USING STOGROUP SYSDEFLT
           PRIQTY 40 SECQTY 40
           SEGSIZE  8;
    COMMIT;
    CREATE TABLE XYZDB01.CRITTB01(
           EMPNO     INTEGER  NOT NULL
          ,FNAME     CHAR(20) NOT NULL WITH DEFAULT
          ,LNAME     CHAR(30) NOT NULL WITH DEFAULT
          ,JOBCODE   CHAR(03) NOT NULL WITH DEFAULT
          )
        IN XYZDB01.CRITTS01;
    COMMIT;
    CREATE UNIQUE INDEX XYZDB01.CRITIX01
           ON XYZDB01.CRITTB01 (EMPNO)
           USING STOGROUP SYSDEFLT PRIQTY 40 SECQTY 40
           CLUSTER;
    COMMIT;

2. Create Db2 objects.Using the DDL from Step 1, create the table space, table, and indexes. The table space and table must be defined exactly as they were before the drop.

   Warning

   Important

   If you create the table space more than once (for example, if it is not created correctly the first time), an extra step is required to accomplish the recovery. You must specify a TORBA/TOLOGPOINT value in the BMC AMI Recover control cards to tell BMC AMI Recover when to stop applying log records. Use DSN1LOGP to identify the log point of the first CREATE table space after the drop. This is the log point you should specify with the TORBA/TOLOGPOINT option. If you do not specify a TORBA/TOLOGPOINT value in this case, the recovery will result in an empty table space.

3. Obtain internal object identifiers for the dropped objects.To run a recovery using the image copy of the dropped table space, you need the internal object identifiers assigned to the database, table space, and table when they were originally created. Because the table space has been dropped, the IDs for the table space and table are no longer available in the Db2 system catalog. You can use the BMC AMI Log Master for Db2 product to find SYSCOPY entries and IDs.

   You can also find the object identifiers by using the IBM Db2 stand-alone utility DSN1PRNT to print a range of pages from the image copy data set. The JCL to run DSN1PRNT is shown in the following figure.

   //RUNPRNT EXEC PGM=DSN1PRNT,
   //             PARM='FORMAT,FULLCOPY,PRINT(000,003)'
   //STEPLIB   DD DSN=SYS2.DB2.PROD.DSNLOAD,DISP=SHR
   //          DD DSN=SYS3.DBAJ.DSNEXIT,DISP=SHR
   //SYSPRINT  DD SYSOUT=*
   //SYSUT1    DD DSN=RDAMSM.ICDBAJ.CRITTS01.G0003V00,               <== IMAGE COPY DSN
   //          DISP=SHR

   Output from this example job is shown in the following figure.

   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 4040D4C7 D9                                        MGR

   In the output from DSN1PRNT in the preceding example, examine the following fields to find the relevant object identifiers:

   • HPGOBID: The first two bytes of this field are the hexadecimal representation of the database identifier (DBID) of the database (X‘014C’ in the example output displayed in the preceding example). The last two bytes are the hexadecimal representation of the page set identifier (PSID) of the table space (X‘0002’ in the example output displayed in the preceding example).
   • PGSOBD: This field is found in the header information for each row. It is the hexadecimal representation of the object identifier (OBID) of the table for that row (X‘0003’ in the example output in the preceding example).

   Warning

   Important

   In this example, there is only one table. Because it is a segmented table space, the OBID for the table is also found in the space map segment entries. If your table space has several tables, you may have to print several pages to locate all of the IDs so that you can review the lengths of data in order to distinguish between the tables.

4. Obtain internal object identifiers for the newly created objects.You can use SPUFI to query the Db2 system catalog for the new internal object identifiers. See the following figure for an example.

   You can also accept the default values for the target DBID and PSID, as well as for the OBID if you are working with a single-table table space.

   SELECT DBID,PSID
   FROM SYSIBM.SYSTABLESPACE
   WHERE DBNAME = 'XYZDB01'
         AND NAME   = 'CRITTS01';
   ---------+---------+---------+---------+---------+---------+
    DBID    PSID
   ---------+---------+---------+---------+---------+---------+
    332      12
   DSNE610I NUMBER OF ROWS DISPLAYED IS 1
   
   DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
   
   SELECT NAME,OBID
   FROM SYSIBM.SYSTABLES
   WHERE DBNAME = 'XYZDB01'
         AND TSNAME = 'CRITTS01';
   ---------+---------+---------+---------+---------+---------+
   NAME                  OBID
   ---------+---------+---------+---------+---------+---------+
   CRITTB01                13
   DSNE610I NUMBER OF ROWS DISPLAYED IS 1
   DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

5. Create BMC AMI Recover JCL.Use the DROPRECOVERY and OBIDXLAT options with BMC AMI Recover to accomplish this recovery. Use the OBIDs from the old image copy as source OBIDs and the OBIDs from the newly created object as target OBIDs. Specify the log point of the image copy as the starting point for BMC AMI Recover to apply log records. If you do not know the log point of the image copy, use the value of HPGRBRBA in the output from DSN1PRNT. (For an example, see Example DSN1PRNT output with the object identifiers of the dropped table space.)

   Warning

   Important

   If the table space was not updated for a long time after the copy was made, the log point value might be too low. A low value is harmless but causes more log to be read. If this is the case, you can compare values in the output of PRINT LOG MAP to obtain an approximate log point. Ensure that the log point you use is not too high.

   The BMC AMI Recover JCL and control statements used for this example are shown in the following figure.

   //*===================================================================*
   //*                 RECOVER THE DROPPED TABLESPACE
   //*                 USING IMAGE COPY INPUT AND LOG RECORDS
   //*===================================================================*
   //*
   //RECOVER EXEC PGM=AFRMAIN,
   //             REGION=0M,
   //             PARM='DBAJ,CRITRECOV,NEW,MSGLEVEL(1)'
   //STEPLIB   DD DISP=SHR,DSN=product.libraries
   //          DD DISP=SHR,DSN=DB2.DSNEXIT
   //          DD DISP=SHR,DSN=DB2.DSNLOAD
   //SYSUT1    DD UNIT=SYSDA,SPACE=(CYL,(2,1))
   //SYSIN     DD *
    RECOVER TABLESPACE XYZDB01.CRITTS01
       DROPRECOVERY
       INCOPY
       FULL DSNAME
               RDAMSM.ICDBAJ.CRITTS01.G0003V00
               LOGPOINT X'0003082F5630'
       OBIDXLAT DBID(X'014C',X'014C')
            PSID(X'0002',X'000C')
            OBID(X'0003',X'000D')
   
    REBUILD INDEX(ALL) TABLESPACE XYZDB01.CRITTS01
   /*
6. Run the recovery.The job should end with a condition code 4 and a warning indicating that an image copy is required for the table space. If you would like to create a copy at recovery time, you can add OUTCOPY YES to the recovery and avoid the condition code 4.