Share:|

Are you in DB2 9 or beyond and do you still have a bunch of simple Tablespaces? Are you worried about accidentally dropping one of them and not being able to recreate it again?

 

Of course, the first thing to do is ALTER RESTRICT ON DROP of all Tables residing in Simple TS, to avoid an accidental Drop.

 

But, what happen if even thou, any Simple TS is dropped? Even if you got the DDL, you will not be able to recreate it. You could recreate it as segmented, but then any attempt to recover the data, will fail.

 

How could you do it? I have developed and tested the procedure below, using BMC Log Master for DB2 and BMC Unload Plus for DB2, but you can try to use similar products from another vendor with similar characteristics, you only need any Log analysis product capable of extracting all info from the dropped TS and an Unload utility capable of unloading from an unregistered copy.

 

First, use BMC Log Master for DB2 with the DROPRECOVERY syntax, to get the DDL and details from the last FIC and RBA's in the report:

                                                                       

  OPTION                                                               

     FILTERREL AND                                                     

                                                                       

  SORTOPTS                                                             

     FILSZ NONE                                                        

     HISTORY WRITE                                                     

                                                                       

  DROPRECOVERY                                                         

     TABLESPACE NAME ISPRXMDB.ISPRXMAX OUTCOPY NO                      

     RECREATE DATASET ISPRXM.DROPREC.RECREATE.DDL NEW                  

        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE                       

     REPORT DATASET ISPRXM.DROPREC.REPORT NEW                   

        CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE                

     FROM  DATE(aaaa-mm-dd) TIME(03.44.00.000000)               

     TO  DATE(aaaa-mm-dd) TIME(03.46.00.000000)                 

 

Then, use BMC Log Master for DB2 to populate the repository with information about the old object, because it is not in the Catalog anymore, using OVERTIME MODE:

 

OPTION                                                   

    FILTERREL AND

    EXECUTION MODE OVERTIME                 

                                                          

SORTOPTS                                                 

    FILSZ NONE                                            

    HISTORY WRITE                                         

                                                          

LOGSCAN                                                  

    REPOS UPDATE                                          

    INCLUDE DICTIONARY                                    

    DB2CATALOG YES                                        

    FROM  DATE(aaaa-mm-dd) TIME(03.44.00.000000)          

    TO  DATE(aaaa-mm-dd) TIME(03.46.00.000000)            

     WHERE TABLESPACE NAME = ISPRXMDB.ISPRXMAX            

 

Then, use BMC Log Master for DB2 to extract any SQL activity after the copy, also using OVERTIME MODE. Include Image copy information extracted from the first step for completion and scan range from RBA information (between copy and drop point):

                                                        

  OPTION                                                

     FILTERREL AND                                      

     EXECUTION MODE OVERTIME                            

          ATTEMPT COMPLETION                            

                                                        

  IMAGECOPY TABLESPACE NAME ISPRXMDB.ISPRXMAX           

       DATASET ISPRXM.ISPRXMDB.ISPRXMAX.P0.C011         

       IMRBA X'C92C2B68DDAF'                            

                                                        

  SORTOPTS                                              

     FILSZ NONE                                         

     HISTORY WRITE                                      

                                                        

  LOGSCAN                                                        

    REPORT                                                      

       TYPE SUMMARY                                             

       SYSOUT                                                   

          CLASS(*) NOHOLD                                       

    SQL                                                         

     MIGRATE                                                    

       DATASET &SYSUID..D&DATE..T&TIME..MIGRATE.SQL NEW         

          CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE              

       TEMPLATE &SYSUID..D&DATE..T&TIME..MIGRATE.TEMPLATE NEW   

          CYLINDERS SPACE(1,1) UNIT(SYSDA) RELEASE              

       INCLUDE TRIGGER YES                                      

    DB2CATALOG NO                                               

    FROM RBA X'C92C2B68DDAF'                                    

    TO   RBA X'C92C2E51B5CF'                                                  

    WHERE TABLESPACE NAME = ISPRXMDB.ISPRXMAX                   

 

Then, using BMC UNLOAD PLUS for DB2, UNLOAD data from the unregistered copy:

 

     UNLOAD                       

         INFILE IN001 FULL        

         DDLDDN DDL0001

 

Then, you got all information needed. Recreate the object and its dependents, using the DDL extracted, but adding the SEGSIZE clause. Load data extracted in the previous step that will bring data back to the time of the last copy and apply generated MIGRATE SQL to include all activity after the copy.

 

Of course, this is an emergency process, I recommend you using BMC Recovery Management for DB2 solution and the TRANSFORM process to change all your existing TS to UTS without impact.

 

Good luck!