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

Document ID : KB000011405
Last Modified Date : 14/02/2018
Show Technical Document Details
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.

Question:

Where is Recovery DDL stored after a drop of a table with the RO option in RC/Update? How can I generate the DDL to recover the table? What else do I have to consider?

Environment:
DB2 for Z/OS
Answer:

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

The next screen is the "RC/Update Object Recovery List" screen. A table called authid.TABLE6 has been dropped using the RO option.

Using the "R" menu item you can find the recovery information as below...

RURLIST1 --------- RC/Update Object Recovery List -------- YYYY/MM/DD HH:MM
COMMAND ===> SCROLL ===>

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


---------- OBJECT INFORMATION ----------- ---- DROP INFORMATION -----
S  T NAME   CREATOR VERSION DATE TIME     USERID
S_ T TABLE6 AUTHID  2016-04-07   19:26:23 AUTHID

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.
If you then press enter you will be able to send the Analysis Output to a Dataset of your choice and from there you can simply use Batch Processor to submit the job to batch.
Be careful about the selection of the MODEL when you generate the DROP Analysis. The Analysis will generate an Unload of the Data from the table and so if the Utility is CA 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 done in batch due to the Utility then you would have to change the Mode => B BATCH on the "RC/Update Drop Table Selection" screen so that 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 you have generated the Drop Analysis ONLINE as you already have seen, when you are on the page where you can see "THE DDL NECESSARY TO RECREATE ALL DROPPED OBJECTS WILL BE SAVED" , there is a COMMAND called LISTR. 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.

You are essentially generating 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 and 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 off the Analysis Output line. Every time an Analysis is executed with analysis option RECOVERY ===> Y then 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.