Example 1: Automated drop recovery of a single table

For this example, assume that a DBA at your company mistakenly dropped a table from an accounting database three days ago.

Because the table was stored in a large table space with several dozen tables, you noticed the mistake only this morning. The accounting department runs a weekly application that will update the table tonight.

Your task is to recover the table and restore the data to the point when the drop occurred. The following information is available to use for the recovery:

  • Table space name: ACCTG.WEEKLY
  • Table name: ACCTG.PAYROLL_STATS
  • Time frame: 01/18/2022, between noon and 4 p.m.

Related topics

Recovering-dropped-objects

Adding-an-automated-drop-recovery-step-to-a-work-ID

Step 1: Create the automated drop recovery job for a single table

Specify the values to recover the table.

Use the procedures in Recovering-dropped-objects to perform this step. An example is shown in the following figure. Note the following details:

  • The Drop Recovery report is not required for this example.
  • When you specify a single object to be recovered, and that object is a table or a table space, BMC AMI Log Master displays the Options field under Specify Dropped Object(s). To display a panel on which you can define whether BMC AMI Log Master uses temporary objects to recover your table, and the physical and logical location of the temporary objects, type E in the Options field.

Values for automated drop recovery of a single table

                       Generate Automated Drop Recovery                        
Command ===>                                                                   
                                                                    DB2 : DB2A
Work ID . . : USER2.$$WORKID0003                                               
Description : USER2 2022-01-19 09.10.26 DROP RECOVERY                          
------------------------------------------------------------------------------
                                                                              
Specify Dropped Object(s)                                                      
   Object Type . . . TB            (DB=Database, TS=Table Space, TB=Table)     
   Object Name . . . DB2DBA.YEAR_END_FINANCIAL_S>>                             
                                                                              
   Additional Objects  . . . . . _ (E=Enter additional object names)           
                                                                              
Specify when DROP occurred                                                     
   Start of Range - Date . . . . 2022-01-18 Time . . . 09.10.27.000000         
   End of Range   - Date . . . . 2022-01-18 Time . . . 09.10.27.000000         
   Specify Other Range . . . . . _ (E=Edit)

Specify Outputs Desired                                                        
   Drop Recovery Report  . . . . _ (E=Edit, D=Delete)                          
      DSN: USER2.DROPREC.REPORT
      Report Template. . . . . . _ (E=Edit, D=Delete)      
                                                                              
   Recreate DDL  . . . . . . . . _ (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.RECREATE.&SYSUID..DDL(MEMBER)                                          
      Execute DDL  . . . . . . . Y (Y=Yes, N=No)   
      Bind Owner . . . . _________ (AUTHID)                      
      SET SQLID  . . . . _________ (Generate SET SQLID)       
      SET SCHEMA . . . . ________________ (Generate SET SCHEMA)
  TRANSFER OWNERSHIP . . . . N (Y=Yes, N=No)
New Owner Type . . . . . _ (U=USER, R=ROLE)
New Owner . . . . ___________________(New Owner/Role Name)                                   
                                                                              
   BMC RECOVER . . . . _ (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.RECOVER.CNTL                                          
      Execute Recover  . . . . . Y (Y=Yes, N=No)                               
      Use LOGONLY option . . . . N (Y=Yes (for Non-DB2 restore only), N=No)    
      OUTCOPY specification  . . A (A=ASCODED, B=BYPART)
  Outcopy Outputs . . . . .  _ (E=Edit outcopy output dataset options)  
      
   DSN1COPY. . . . . . . . . . .   (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.DSN1COPY.CNTL                                         
      Execute Copy . . . . . . . N (Y=Yes, N=No)                               
   Post Recover SQL  . . . . . . _ (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.MIGRATE.SQL                                           
      Execute SQL  . . . . . . . Y (Y=Yes, N=No)
   Post Recover Rebinds  . . . . _ (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.REBIND.CMDS                                           
      Execute Rebinds  . . . . . Y (Y=Yes, N=No)                               
   Post Recover Checks . . . .   _ (E=Edit data set name, D=Delete)            
      DSN: USER2.DROPREC.CHECK.CMDS                                            
      Execute Checks   . . . . . Y (Y=Yes, N=No)                               
                                                                              
   Post Recover Runstats . . . .   (E=Edit data set name, D=Delete)
      DSN: USER2.DROPREC.RUNSTATS.CMDS                            
      Execute Runstats . . . . . Y (Y=Yes, N=No)

   Post Recover Repairs  . . . .   (E=Edit data set name, D=Delete)   
      DSN: USER2.DROPREC.REPAIR.CMDS                             
      Execute Repairs  . . . . . Y (Y=Yes, N=No)  
                 
Specify Recovery Type                                               
   Recovery Type . . . . . . . . R (R=BMC RECOVER, D=DSN1COPY)
   
Specify Additional Options . . .                                               
   Non-DB2 Restore . . . . . . . ________  (User defined JCL skeleton)
       
Press PF3 to save the workid or to generate JCL.

Step 2: Review the JCL

After you save the work ID and generate the JCL, review the job.

The generated JCL contains multiple job steps, and, if necessary, you can modify the JCL to adapt it to your environment. For example, you can stop or restart the automated drop recovery action between job steps. The following figure shows an example of the JCL created for this example.

Sample JCL: Automated drop recovery of a table space with a clone table

//DB2DBALM JOB (XXXX),'AUTO DROP RECOVERY',CLASS=A,MSGCLASS=X.
//             NOTIFY=&SYSUID
//**********************************************************************
//*
//*               BMC AMI Log Master for Db2 V13.01.00
//*
//*     DSN: DB2DBA.DEV.DROPREC.JCL(EXMPL01)
//*
//*     GENERATED BY USER: DB2DBA3
//*               ON DATE: 2022/01/19
//*               AT TIME: 10:38
//*
//**********************************************************************
//*
//**********************************************************************
//* ALLOCATE THE DATASETS FOR THE AUTOMATED DROP RECOVERY PROCESS
//**********************************************************************
//*
//ALLOC   EXEC PGM=IEFBR14
//RECDS     DD DSN=DB2DBA3.DROPREC.RECOVER.CNTL,
//             DISP=(NEW,CATLG,DELETE),
//             SPACE=(CYL,(1,1),RLSE),
//             UNIT=SYSDA,
//             RECFM=FB,LRECL=80
//RECDS2    DD DSN= DB2DBA3.DROPREC.RECOVER.CNTL.R2,
//             DISP=(NEW,CATLG,DELETE),'
//             SPACE=(CYL,(1,1),RLSE),
//             UNIT=SYSDA,
//             RECFM=FB,LRECL=80
//REBDS     DD DSN=DB2DBA3.DROPREC.REBIND.CMDS,
//             DISP=(NEW,CATLG,DELETE),
//             SPACE=(CYL,(1,1),RLSE),
//             UNIT=SYSDA,
//             RECFM=FB,LRECL=80
//CHKDS     DD DSN=DB2DBA3.DROPREC.CHECK.CMDS,                       
//             DISP=(NEW,CATLG,DELETE),                                 
//             SPACE=(CYL,(1,1),RLSE),                                  
//             UNIT=SYSDA,                                              
//             RECFM=FB,LRECL=80                                        
//REPAIR    DD DSN=DB2DBA3.DROPREC.REPAIR.CMDS,                          
//             DISP=(NEW,CATLG,DELETE),                                 
//             SPACE=(CYL,(1,1),RLSE),                                  
//             UNIT=SYSDA,                                              
//             RECFM=FB,LRECL=80
//RUNDS     DD DSN=DB2DBA3.DROPREC.RUNSTATS.CMDS,      
//             DISP=(NEW,CATLG,DELETE),               
//             SPACE=(CYL,(1,1),RLSE),                
//             UNIT=SYSDA,                            
//             RECFM=FB,LRECL=80                      
//*                                                                     
//**********************************************************************
//* EXECUTE LOGSCAN STEP
//**********************************************************************
//*
//LOGMSTR EXEC PGM=ALPMAIN,
//             PARM='DHA,,MSGLEVEL(2),ALPOPTS(ALP$OPTS)',
//             COND=(4,LT),
//             REGION=0M
//STEPLIB   DD DISP=SHR,DSN=product.libraries
//          DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//ALPPRINT  DD SYSOUT=*
//SQLPRINT  DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//ALPDUMP   DD SYSOUT=*
//SYSTERM   DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSIN     DD *,DLM=##
 /* DB2DBA3.TBRCV01                                                  */
 /* DB2DBA3 2022-01-19 10.12.27 DROP RECOVERY                        */

  OPTION
     FILTERREL AND EXECUTION MODE CURRENT

  SORTOPTS
     FILSZ NONE
     HISTORY WRITE

  WORKID DB2DBA3.TBRCV01
     DESC 'DB2DBA3 2022-01-19 10.12.27 DROP RECOVERY'

  DROPRECOVERY
     TABLE NAME ACCTG.PAYROLL_STATS
        TEMPORARY OBJECTS
           STOGROUP SYSDEFLT
           DATABASE RECOVER TABLESPACE RCVYTS
           OWNER ACCTG
     RECREATE DATASET DB2DBA3.DROPREC.RECREATE.DDL NEW
        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE
        EXECUTE
     RECOVER DATASET DB2DBA3.DROPREC.RECOVER.CNTL OLD
        OUTCOPY ASCODED
     CHECK DATASET DB2DBA3.DROPREC.CHECK.CMDS OLD
     REPAIR DATASET DB2DBA3.DROPREC.REPAIR.CMDS OLD
     MIGRATE DATASET DB2DBA3.DROPREC.MIGRATE.SQL NEW
        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE
     REBIND DATASET DB2DBA3.DROPREC.REBIND.CMDS OLD
     FROM  DATE(2022-01-18) TIME(12.00.00.000000)
     TO    DATE(2022-01-18) TIME(16.00.00.000000)
##
//SQLCODES DD DUMMY
//*
//**********************************************************************
//* THIS STEP ISSUES DB2 -STOP COMMANDS FOR THE OBJECTS BEING RECOVERED
//**********************************************************************
//*
//STOPCMDS EXEC PGM=IKJEFT01,
//             COND=(4,LT)
//STEPLIB        DD DISP=SHR,DSN=DB2.DSNEXIT
//               DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSTSIN   DD *
  DSN SYSTEM(DHA)
    -STOP DATABASE(RECOVER) SPACENAM(RCVYTS)
  END
/*
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//*
//**********************************************************************
//* THIS STEP EXECUTES BMC RECOVER TO RECOVER THE DROPPED OBJECTS.
//**********************************************************************
//*
//RECOVER EXEC PGM=AFRMAIN,
//             PARM='DHA,,NEW/RESTART,MSGLEVEL(1)',
//             REGION=0M,
//             COND=(4,LT)
//STEPLIB   DD DISP=SHR,DSN=product.libraries
//          DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSIN     DD DISP=SHR,DSN=*.ALLOC.RECDS
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//ALPDUMP   DD SYSOUT=*
//*
//**********************************************************************
//* THIS STEP EXECUTES BMC RECOVER TO RECOVER THE ADDITIONAL DROPPED
//* OBJECTS SUCH AS CLONE TABLES
//**********************************************************************
//*
//RECOVER2 EXEC PGM=AFRMAIN,
//             PARM='DHV,,NEW/RESTART,MSGLEVEL(1)',
//             REGION=0M,
//             COND=(4,LT)
//STEPLIB   DD DISP=SHR,DSN=product.Libraries
//          DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSIN     DD DISP=SHR,DSN=*.ALLOC.RECDS2
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSERR    DD SYSOUT=*
//*
//**********************************************************************
//* THIS STEP ISSUES DB2 -START COMMANDS FOR THE OBJECTS BEING RECOVERED
//**********************************************************************
//*
//STRTCMDS EXEC PGM=IKJEFT01,
//             COND=(4,LT)
//STEPLIB        DD DISP=SHR,DSN=DB2.DSNEXIT
//               DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSTSIN   DD *
  DSN SYSTEM(DHA)
    -START DATABASE(RECOVER) SPACENAM(RCVYTS)
  END
/*
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//*
//*******************************************************************   
//* REPAIR FOR RECOVERED TABLESPACE                                     
//*******************************************************************   
//*                                                                     
//REPAIR   EXEC PGM=DSNUTILB,PARM='DHA',                               
//             REGION=0M,                                               
//             COND=(4,LT)  
//STEPLIB   DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//UTPRINT   DD SYSOUT=*,HOLD=YES
//SYSPRINT  DD SYSOUT=*,HOLD=YES                                            
//SYSIN     DD DISP=SHR,DSN=DB2DBA3.DROPREC.REPAIR.CMDS                  
//*                                                                                                                                        
//*
//*******************************************************************
//* CHECK FOR RECOVERED TABLESPACE                                   
//*******************************************************************
//*                                                                  
//CHECK   EXEC PGM=DSNUTILB,PARM='DHA',                              
//             REGION=0M,                                            
//             COND=(4,LT)                                           
//STEPLIB   DD DISP=SHR,DSN='DHA.DSNEXIT'                       
//          DD DISP=SHR,DSN='DB2.DSNLOAD'                   
//          DD DISP=SHR,DSN='DB2.DSNLOAD'                   
//UTPRINT   DD SYSOUT=*,HOLD=YES                                     
//SYSPRINT  DD SYSOUT=*,HOLD=YES                                     
//SYSUT1    DD SPACE=(CYL,(500,100)),UNIT=SYSALLDA
//SORTOUT   DD SPACE=(CYL,(500,100)),UNIT=SYSALLDA
//SYSERR    DD SPACE=(CYL,(500,100)),UNIT=SYSALLDA
//SYSUDUMP  DD SYSOUT=*                                              
//SYSIN     DD DISP=SHR,DSN=DB2DBA3.DROPREC.CHECK.CMDS                
//*
//**********************************************************************
//* POPULATE RECOVERED TABLE(S) VIA SELECTS FROM TEMPORARY TABLE(S).
//* THE DATASET CONTAINS SQL ONLY IF DROPRECOVERY STATEMENT SPECIFIES
//* RECOVERY OF INDIVIDUAL TABLES.
//**********************************************************************
//*
//MIGRATE EXEC PGM=ALPMAIN,
//             PARM='DHA,,MSGLEVEL(2),ALPOPTS(ALP$OPTS)',
//             COND=(4,LT),
//             REGION=0M
//STEPLIB   DD DISP=SHR,DSN=product.libraries
//          DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//ALPPRINT  DD SYSOUT=*
//SQLPRINT  DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//ALPDUMP   DD SYSOUT=*
//SYSTERM   DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSIN     DD *,DLM=##
 /* DB2DBA3.TBRCV01                                                  */
 /* DB2DBA3 2022-01-19 10.12.27 DROP RECOVERY                        */

  OPTION
     FILTERREL AND EXECUTION MODE CURRENT

  SORTOPTS
     FILSZ NONE
     HISTORY WRITE

  WORKID DB2DBA3.TBRCV01
     DESC 'DB2DBA3 2022-01-19 10.12.27 DROP RECOVERY'

  EXECSQL DB2DBA3.DROPREC.MIGRATE.SQL
##
//SQLCODES DD DUMMY
//*
//**********************************************************************
//* EXECUTE RUNSTATS ON THE DROPPED OBJECTS                             
//**********************************************************************
//*                                                                     
//RUNSTAT EXEC PGM=DSNUTILB,PARM='DHA',                                
//             REGION=0M,                                               
//             COND=(4,LT)                                              
//STEPLIB   DD DISP=SHR,DSN=DB2.DSNEXIT
//          DD DISP=SHR,DSN=DB2.DSNLOAD
//UTPRINT   DD SYSOUT=*,HOLD=YES                                        
//SYSPRINT  DD SYSOUT=*,HOLD=YES                                        
//SYSIN     DD DISP=SHR,                                                
//          DSN=DB2DBA3.DROPREC.RUNSTATS.CMDS                                
//*
//**********************************************************************
//* REBIND THE PROGRAMS AFFECTED BY DROPPING THE OBJECTS
//**********************************************************************
//*
//REBINDS EXEC PGM=IKJEFT01,
//             COND=(4,LT)
//STEPLIB        DD DISP=SHR,DSN=DB2.DSNEXIT
//               DD DISP=SHR,DSN=DB2.DSNLOAD
//SYSTSIN   DD *
  DSN SYSTEM(DHA)
//          DD DISP=SHR,DSN=DB2DBA3.DROPREC.REBIND.CMDS
//          DD *
  END
/*
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*

Results of the job to recover a single table

When you run the JCL that BMC AMI Log Master generates, it accomplishes the following actions:

  • Creates an empty version of the dropped table in the original table space.
  • Creates a temporary table space, and depending on the values that you specified, BMC AMI Log Master might also create a temporary database.
  • Creates a temporary table (in the temporary table space) with the same structure as the dropped table.
  • Recovers data into the temporary table.
  • Migrates data from the temporary table to the empty table in the original table space.
  • Drops the temporary table, temporary table space, and temporary database, if you specified one.
  • Gathers statistics for the dropped object.
  • Rebinds any application plans that were invalidated when the table was dropped.
  • Takes the table spaces out of check pending status.

 

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