Example 2: Automated drop recovery of a partitioned table space

For this example, assume that a table space that should have been dropped from your test environment was mistakenly dropped from your production environment. You need to recover this table space, and you want to use BMC AMI Recover to create output image copies of the recovered table space.

The following information is available for the recovery:

  • Table space name: ACCTGTS
  • Database name: ACCTGDB

Related topic

Recovering-dropped-objects

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

Specify the values to recover the table space as shown in the following example.

Use the procedures described in Recovering-dropped-objects.

                       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.

When you specify a single object to be recovered, and that object is a table or table space, BMC AMI Log Master displays the Options field under Specify Dropped Object(s). To display a panel on which you can define the output image copies, type E in the Options field.

Specify the values as shown in the following figure. Notice that you type 0 in the Partitions in the table space field. This value causes BMC AMI Recover to create one output image copy that contains all of the partitions of the table space, instead of one image copy for each partition.

                        BMC RECOVER Outcopy Specification            
                                                                     
  Tablespace name . . : ACCTDB.ACCTGTS                                
                                                                     
  TABLE SPACE INFORMATION                                             
     Partitions in the table space . . . 0   (0=nonpartitioned,       
                                              1-254=partitioned)
    OUTCOPY specification . . . . . . . A   (A=Ascoded, B=Bypart)
     Clone . . . . . . . . . . . . . . . N   (Y=Yes, N=No)
    
  OUTCOPYDDN specification                                            
     DDNAME1 . . . . BMCCPY   (DDNAME or DDNAME prefix)               
     Register  . . . Y        (Y=Yes, N=No)                           
     Primary . . . . _        (E=Edit data set name, D=Delete)        
        DSN:                                                          
                                                                     
     DDNAME2 . . . . BMCCPZ   (DDNAME or DDNAME prefix)               
     Register  . . . Y        (Y=Yes, N=No)                           
     Secondary . . . _        (E=Edit data set name, D=Delete)        
        DSN:                                                          
                                                                     
  RECOVERYDDN specification                                             
     DDNAME1 . . . . BMCRCY   (DDNAME or DDNAME prefix)                 
     Register  . . . Y        (Y=Yes, N=No)                             
     Primary . . . . _        (E=Edit data set name, D=Delete)          
        DSN:                                                            
                                                                     
     DDNAME2 . . . . BMCRCZ   (DDNAME or DDNAME prefix)                 
     Register  . . . Y        (Y=Yes, N=No)                             
     Secondary . . . _        (E=Edit data set name, D=Delete)          
        DSN:    
                                                       
Press PF3 to save the workid or to generate JCL.

Step 2: Review the JCL for an automated drop recovery of a partitioned table space

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 edit 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 purpose.

//RDARBHLM JOB (XXXX),'AUTO DROP RECOVERY',CLASS=A,MSGCLASS=X.
//             NOTIFY=&SYSUID
//**********************************************************************
//*
//*               BMC AMI Log Master for Db2 V13.1.00
//*
//*     DSN: DB2DBA.DEV.DROPREC.JCL(EXMPL02)
//*
//*     GENERATED BY USER: DB2DBA3
//*               ON DATE: 2022/01/19
//*               AT TIME: 10:43
//*
//**********************************************************************
//*
//**********************************************************************
//* 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.TSRCV02*/
 /* DB2DBA3 2022-01-19 10.39.18 DROP RECOVERY */

  OPTION
     FILTERREL AND EXECUTION MODE CURRENT

  SORTOPTS
     FILSZ NONE
     HISTORY WRITE

  WORKID DB2DBA3.TSRCV02
     DESC 'DB2DBA3 2022-01-19 10.39.18 DROP RECOVERY'

  DROPRECOVERY
     TABLESPACE NAME ACCTGDB.ACCTGTS OUTCOPY NO
     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
     REPORT SYSOUT
        CLASS(*) LRECL(132) NOHOLD
     FROM  DATE(2022-01-18) TIME(09.00.00.000000)
     TO    DATE(2022-01-18) TIME(12.30.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(ACCTGDB) SPACENAM(ACCTGTS)
  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=*
//SYSERR    DD SYSOUT=*
//*
//**********************************************************************
//* THIS STEP EXECUTES BMC RECOVER TO RECOVER THE ADDITIONAL DROPPED
//* OBJECTS SUCH AS CLONE TABLES
//**********************************************************************
//*
//RECOVER2 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.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(ACCTGDB) SPACENAM(ACCTGTS)
  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='DB2.DSNEXIT'                       
//          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=DB2DBA.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.TSRCV02                                                  */
 /* DB2DBA3 2022-01-19 10.39.18 DROP RECOVERY                        */

  OPTION
     FILTERREL AND EXECUTION MODE CURRENT

  SORTOPTS
     FILSZ NONE
     HISTORY WRITE

  WORKID DB2DBA3.TSRCV02
     DESC 'DB2DBA3 2022-01-19 10.39.18 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.DSNEXI
//          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 partitioned table space

After you submit the automated drop recovery job, BMC AMI Log Master and BMC AMI Recover create the table space, recover data into it, gather statistics on the table space, rebind any application plans that were invalidated when the table space was dropped, and take the table spaces out of check pending status.

 

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