Generate Drop and Create DDL for a Table with RI

Products

• RC/Migrator for DB2 for z/OS

Issue/Introduction

What is the best method for generating the Drop and Create DDL for a Table that has a large number of dependent Views and Foreign Keys?

How best to do this with minimal impact to other objects.

Environment

Release : 20.0

Resolution

An RC/Migrator Migration Strategy can be used for this requirement, as it will generate the required Unload and Load utilities. If this is a one-off requirement, then use a Quick Migration.

Below are the steps to generate a Quick Migration Strategy and Analysis.

Before you start you will need to pre-allocate the following two libraries.

- JCL library
- Analysis output library

Both should be allocated as FB 80 PDSs.

Use the following procedure to specify the table for migration along with the dependent objects and RI. This will generate Drop and Create DDL along with Unload and Load utilities.

1. On the RC/Migrator Main Menu:

• Specify the source Db2 subsystem that contains the Db2 objects to be migrated in the SSID field.
• Specify 4 (Quick Migration) in the OPTION line and press Enter.

The Quick Migration Services panel appears.

2. Complete the following fields to specify the objects for migration:

• For Object, specify the object type, TB in this case.
• For Name, specify the table name.
• For Creator, specify the table creator.

3. Press Enter to process your entries.

4. The table is auto-selected and the Quick Migration Services screen reappears and displays the selected object.

5. Enter X in the Cmd column to explode to all dependent objects. Press ENTER and the explode fields for the object are automatically set to Y.

6. Press PF3 to process the selections.

The Quick Migration Analysis panel appears.

Specify the analysis options for the quick migration.

7. Under the OUTPUT DATASET SPECIFICATIONS, enter the DATASET NAME of the pre-allocated analysis PDS that you created, including a member name. The name should be entered in quotes. This is where the generated DDL script will be written.

8. Under the OPTION SPECIFICATIONS, specify Y in the UPDATE OPTIONS field.

9. Press ENTER.

The Migrate Strategy Analysis Options panel appears.

10. Specify the required Analysis Options for this migration. The following values are recommended for this scenario.

 

The key options are:

• RI ==> L – this includes any foreign keys but does not include the parent objects.
• DROP OBJECTS ==> Y – generates the DROP for the table.
• ALL ROWS ==> Y – generates unload and load utilities.

11. Press ENTER.

The General Model Services panel appears.

12. Select the @DEFAULT model and press ENTER. 

The Migrate Strategy Analysis Options panel reappears.

13. Enter SAVE on the COMMAND line and then press PF3.

The Quick Migration Analysis panel reappears.

14. Under DB2 SSID SPECIFICATIONS specify the same SSID for the TARGET SSID as is specified for SOURCE SSID.

15. Press ENTER and your specifications are saved.

16. Specify the execution mode to perform the analysis, in this case B -  for Batch mode.

The Batch JCL Specification panel appears.

17. Enter the information for the batch job, specifying a valid Jobcard and also the pre-allocated JCL library that you created earlier.

18. Under EXECUTION SPECIFICATIONS, specify D for Dataset to save the JCL to the specified JCL library.

19. You can now submit this JCL to generate the DDL.