Skip to Content

Recovering a dropped table space when image copies are available

A DBA new to your company has accidentally dropped a production table space instead of the test table space that he intended to drop.

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.

Related topic

Recovering a dropped table space or table

 

Warning

Important

Use the procedure in this example when the table space has been dropped. If you want to recover a table from a table space that has more than one table in it, see Recovering-a-dropped-table-in-a-segmented-table-space-containing-multiple-tables. If you want to recover a table that is the only table in the table space, see Recovering-a-dropped-table-from-a-simple-table-space.

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 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 might 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. If the space is in extended RBA format, use HPGBIGRBRBA instead of HPGRBRBA. (For 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 logs 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 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 condition code 4.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Recover for Db2 13.1