Where is Recovery DDL stored after a drop of a table using the RO option to save the DDL using RC/Update

Products

• RC/Update for DB2 for z/OS

Issue/Introduction

When a table is dropped it can be difficult to recover from this if it was done by accident. Being able to save the DDL and the Data before a drop can
give a recovery method in case it is needed. The ability to do this would be impacted by the physical size of the table in question and other relationships
that are associated.

Where is Recovery DDL stored after a drop of a table with the Recoverable drop with options (RO) option in RC/Update for Db2 for z/OS (RCU)?
How to generate the DDL to recover the table? What else must be considered?

Resolution

Assumption: A drop of a table has been executed with the Recoverable drop option (RO) in RC/Update.

One of the options in the RC/Update menu is : R - Drop Recovery

RUPM 20.0   ------------- RC/Update Main Menu ------------
COMMAND ===>

Option      => R                     Object  => T
Item Name   => *                   > Creator => AUTHID >

The next screen is the "RC/Update Object Recovery List" screen.
Using the "R" menu item(Recover a Dropped Object) you can find the recovery information as below...

RURLIST1 --------- RC/Update Object Recovery List -------- yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

Option      => R                     Object  => T
Item Name   => *                   > Creator => authid    > Where => N
SSID: ssid ---------------------------------------------------------- authid
 Enter: 'S' to Select the Object(s) that you want to Recover;
        'E' to Explode (view a list of recoverable dependent objects)

                                                                          DEP.
   ---------- OBJECT INFORMATION ----------- ---- DROP INFORMATION -----  OBJS/
S  T  NAME                 CREATOR  VERSION  DATE       TIME     USERID   VERS
__ T  DROPPED_TABLE_NAME   authid            yyyy-mm-dd hh:mm:ss authid     5

Having found the table to be recovered, select it with an "S" line command.

This will display the Recovery Analysis DDL required to recreate the table and load the data back into the table.
Press enter to be able to send the Analysis Output to a Dataset of your choice and from there simply use Batch Processor to submit the job to batch.

RUPDROPR ------------- RC/Update Recovery DDL ------------ yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

                       DROPPED OBJECT RECOVERY DDL
 Press ENTER to allocate and write DDL to a dataset.  Press PF3/15 to cancel.
SSID: ssid ---------------------------------------------------------- authid

.SYSTEM SQLDDL

.
.
.

Be careful about the selection of the MODEL when the DROP Analysis is generated. The Analysis will generate an Unload of the Data from the table and so if the Utility is FASTUNLOAD it can't be executed online. 
This is a consideration for the Recovery also since that has a Load step for the data back to the table.
If the unload has to be performed in batch due to the Utility then change the Mode => to "B" BATCH on the "RC/Update Drop Table Selection" screen.
Then the DROP Analysis is written to a Dataset so that you can submit it with Batch Processor in Batch.

Another way to get this DDL:

When the  Drop Analysis has been generated ONLINE as above, when on the page where "THE DDL NECESSARY TO RECREATE ALL DROPPED OBJECTS WILL BE SAVED" can be seen, there is a COMMAND called LISTR.

RUPLIST1 20.0   RC/Update Drop Table Selection ---------   yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

Option      => D                     Object  => T           Mode  => O ONLINE
Item Name   => table_to_drop       > Creator => authid    > Where => N
SSID: ssid ------------------------------------------------------ authid    >
Select the object(s) that you want to DROP by entering:

S or SO - Drop without recovery;   R or RO - Drop with recovery.

S  TABLE              CREATOR  TYPE DATABASE TSNAME   COLCOUNT RECLEN  OBID
RO table_to_drop      authid    T   database tblsp         4    103     3

Analysis Options on next page and then PF3 to get the next screen.....

RUPDROP ------- RC/Update Online Drop Confirmation ------- yyyy/mm/dd hh:mm:ss
COMMAND ===> LISTR                                             SCROLL ===> CSR

Press ENTER to perform drops or END/PF3 to cancel.

SSID: ssid  ---------------------------------------------------------- authid

------------------------------------------------------------------------------
--        PRESS ENTER TO ISSUE THE FOLLOWING UNLOAD/DROP STATEMENTS.        --
--                                                                          --
--     THE DDL NECESSARY TO RECREATE ALL DROPPED OBJECTS WILL BE SAVED.     --
------------------------------------------------------------------------------
--                      DROP RECOVERY OPTIONS SUMMARY                       --
--                                                                          --
--  Control Options                            Utility Options              --
--   Security        ==> Y                      Runstats        ==> Y       --
--   Default SQLID   ==>                        Check Data      ==> Y       --
--   Drop Impact     ==> N                      Image Copy      ==> Y       --
--   Set Terminator  ==> #                      (B)ind/(R)ebind ==> B       --
--                                                                          --
--  Data Unload Options                        Model Options                --
--   All Rows        ==> Y                      Model ID      ==> @DEFAULT  --
--   Number Rows     ==>                        Model Creator ==> authid    --
--                                              Update Model  ==> N         --
--  Dataset Delete Options                                                  --
--   Unload          ==> N                                                  --
--   Temporary       ==> N                                                  --
--   VSAM Datasets   ==> N                                                  --
------------------------------------------------------------------------------

See the online help for it below.

COMMAND : LISTR

DESCRIPTION : The LISTR command displays the RC/Update Recover DDL screen when there is recovery DDL that has been generated for the drop. 
Recovery DDL is generated when the object to be dropped is selected with the R or RO drop line command.

Press PF3 (END) on the RC/Update Recover DDL screen to leave and return to the RC/Update Drop Confirmation screen.

To cancel the drop, DO NOT press the enter key. Press PF3 (END)or enter CANCEL on the RC/Update Drop Confirmation screen.

This essentially generates a RECOVERY ANALYSIS which is very much the same as the RC/Migrator Recovery Analysis Option.

Using an RC/Migrator ALTER Strategy 

In fact if you are going to do this we recommend that you create an ALTER Strategy in RC/Migrator and DROP the table in there.
Then on the ANALYSIS OPTIONS select the RECOVERY ===> Y option to generate recovery Analysis which can then be accessed using the
"R Submit the associated Recovery analysis output DDL for batch processor execution." option from the Analysis Output line.
Every time an Analysis is executed with analysis option RECOVERY ===> Y the Recovery DDL is created and stored after the Execution of the initial Analysis.
In this way you have a record of what was done in a named strategy.