RC/Migrator for Db2 for Z/OS: Create the DDL for all selected objects in a database to be used as a backup or for comparison in bulk?
Article ID: 52939
Updated On:
Products
Issue/Introduction
RC/Migrator provides a DDL Only option that generates the necessary DDL statements needed to re-create a DB2 structure.
The generated DDL re-creates the DB2 structure including any referential integrity rules or security.
Environment
Release: R20
Component: RCM
Resolution
Create a Migration strategy which specifies the Name and Creator of the Database on the RC/M Create Migration Strategy screen. It is best to use the 'A' explode option.
PRIMARY OBJECT TYPE SPECIFICATION. ( Select one type with 'A' , 'S', or 'E' )
_ Storage Group
A Database
_ Tablespace
_ Table
_ Materialized Query Table
_ Index
_ View
_ Synonym/Alias
_ Trigger
_ Routine
_ Sequence
PRIMARY OBJECT SELECTION SPECIFICATION.
Name ==> DBCORP > Creator ==> authid1 > Where ==> N
Once the required object(s) are selected and the Strategy is saved, carry out a Strategy Analysis.
When in the Update Options screen under the label "Exclusive Options" BND/DAT/STAT/SQL/GRNT/RI ==> , select S for SQL.
Exclusive Options
NO .AUTHS ==> Y
BND/DAT/STA/SQL/GRNT/RI ==> S <------------------
(B,D,R,S,G,I/N)
COMMIT ASAP (SQL ONLY) ==> N
The 'S' option means:
S Generate SQL only, with no CA Batch processor commands. Provides a DDL/DCL snapshot.
This option will generate SQL only with no Batch Processor commands included. The result will contain no commands starting with a dot '.' such as .SYNC , .AUTH , .CONNECT and .SYSTEM.
It means that unlike a normal analysis output , the DDL generated can be executed by utilities like SPUFI or DSNTEP2 without any modifications.
In addition, ensure that the Update Options, Control Options , RI (foreign keys) ==> is set to "L" or "G" according to the requirements of the DDL to be generated so that RI are not missed.
Secondly, if the current security is also required to be part of the snapshot, ensure that Update Options, Control Options, SECURITY ==> is set to 'Y'.
'Y' means include all security so there will be GRANT statements generated.
Lastly, you may wish to consider the analysis option TABLE OBID ==> Y , that adds the OBID of the table to the DDL. If the generated DDL could be used in an OBIDXLAT recovery situation then having the original OBID might be of use.
A Use Case
Drop and recreate a large number of Indexes in order to allow DB2 to update them for the current release.
Using a Migration Strategy utilising a custom EQF predicate to select the indexes in question and using RC/Migrator exclusive options to generate only SQL, generate the DDL as described above in this article but instead of Database level , select on Index level.
This will generate the selected INDEX DDL but no utility code.
The DDL SOURCE generated can then be manually modified to include additional DDL such as DEFER YES if required so that the underlying vsam lds is only created when needed.
The objective is to drop and recreate particular selected indexes and leave the database in a consistent state.
This SOURCE is then used in a COMPARE strategy in order to generate the DDL to correctly drop and recreate the indexes including all the utilities and correction of any related primary keys on tables. When an object is created DB2 creates it under the current release functionality.
This procedure can be used when there are large numbers of objects involved and process them in bulk.
This procedure can be used to rectify problems with old DB2 indexes as described in IBM APAR UI74962.
Additional Information
Feedback
