CA RC/Migrator for DB2 for Z/OS : Extract all ddl of all my databases

book

Article ID: 223475

calendar_today

Updated On:

Products

CA RC/Migrator for DB2 for z/OS

Issue/Introduction

How to extract all ddl of all my databases.  I have succeeded to extract DDL with HDDL on one database but how can I extract all or multiple database in the same batch job? 
I have tried DB% but it doesn't works. I have hundreds of database ddl to extract. 

Environment

DB2 for Z/OS

Release : R20

Resolution

For the purpose of this exercise the list of databases will be as listed by CA RC/Query below:

RQDBL  20.0   ------------- RC/Q Data Base List ------------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> DB                      Option  ===> L    Where => N
  Data Base ===> DB%                   > Creator ===> AUTHID1                >
  Qualifier ===> *                     > N/A     ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------authid1 -          LINE 1 OF 8    >
CMD      DATABASE CREATOR  STOGROUP CREATEDB   DBID BPOOL    INDEXBP  IMP REL
________ DBCORP   AUTHID1  SYSDEFLT AUTHID1    4922 BP0      BP2      N   Q
________ DBCORP1  AUTHID1  SYSDEFLT AUTHID1   12376 BP1      BP1      N   Q
________ DBCORP2  AUTHID1  SYSDEFLT AUTHID1   12384 BP0      BP0      N   Q
________ DBCORP4  AUTHID1  SYSDEFLT AUTHID1   13418 BP0      BP3      N   Q
________ DBCORP6  AUTHID1  SYSDEFLT AUTHID1    2307 BP0      BP0      N   Q
________ DBCORP9  AUTHID1  SYSDEFLT AUTHID1   13415 BP1      BP2      N   Q
________ DBPBRRPN AUTHID1  SYSDEFLT AUTHID1   12358 BP32K    BP2      N   Q
________ DBPF     AUTHID1  SYSDEFLT AUTHID1   13431 BP1      BP2      N   Q

To obtain all the DDL for all the above databases an RC/Migrator DDL only migration analysis can be used.

1. Create a MIGRATION type(M) strategy.

RMS1 20.0   -------------- RC/M Strategy Services -------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

DB2 SSID ===> ssid
STRATEGY ===> DDLONLY  CREATOR ===> authid1  TYPE ===> M    SRC SSID ===> *
--------------------------------------------------------------------- authid1

                                              T S SRC  +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION               CREATOR  P O SSID   USER     DATE   TIME
c DDLONLY_ DDL only Migration_______ authid1  M U ssid <== STRATEGY CREATION

On the next screen select 'E' on the database object, for this example a wildcard (DB%) is used for the database name to obtain only those database names starting with DB for creator authid1.  

'E'            Strategy consists of the primary objects selected
               and all their dependent objects (Explode). 

---------------- RC/M Create Migration Strategy --------------- yy/mm/dd hh:mm
COMMAND ===>

Strategy ===> DDLONLY     Description  ===> DDL ONLY MIGRATION
Creator  ===> AUTHID1     Share Option ===> U (U,Y,N,X,L)   SRC SSID ===> ssid
-------------------------------------------------------------------------------

PRIMARY OBJECT TYPE SPECIFICATION. ( Select one type with 'A' , 'S', or 'E' )
        _ Storage Group
        e Database
        _ Tablespace
        _ Table
        _ Materialized Query Table
        _ Index
        _ View
        _ Synonym/Alias
        _ Trigger
        _ Routine
        _ Sequence

PRIMARY OBJECT SELECTION SPECIFICATION.
 Name ==> DB%                 >  Creator ==> authid1   > Where ==> N

This will create a strategy that automatically selects the primary database objects and all their dependent objects without the need to manually select them.

After returning this screen will be displayed as the strategy has been created.

RMS1 20.0   -------------- RC/M Strategy Services -------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

DB2 SSID ===> ssid
STRATEGY ===> DDLONLY  CREATOR ===> authid1  TYPE ===> M    SRC SSID ===> *
--------------------------------------------------------------------- authid1

                                              T S SRC  +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION               CREATOR  P O SSID   USER     DATE   TIME
_ ________ _________________________ authid1  _ N ____ <== STRATEGY CREATION
_ DDLONLY  DDL ONLY MIGRATION        authid1  M U ssid authid1  yy/mm/dd hh:mm


2. Analysis

Having created the strategy it can now be analysed to generate the desired DDL using the 'a' line command on the strategy name.


RMS1 20.0   -------------- RC/M Strategy Services -------------- yy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

DB2 SSID ===> ssid
STRATEGY ===> DDLONLY  CREATOR ===> authid1  TYPE ===> M    SRC SSID ===> *
--------------------------------------------------------------------- authid1

                                              T S SRC  +---- LAST UPDATE ----+
O STRATEGY DESCRIPTION               CREATOR  P O SSID   USER     DATE   TIME
_ ________ _________________________ authid1  _ N ____ <== STRATEGY CREATION
a DDLONLY  DDL ONLY MIGRATION        authid1  M U ssid authid1  yy/mm/dd hh:mm


The first consideration is that only DDL is required, not any utility statements!!

On the analysis screen there are update options to set: UPDATE OPTIONS    ===> Y

Under the Exclusive Options section, the BND/DAT/STA/SQL/GRNT/RI option should be set to "S"

BND/DAT/STA/SQL/GRNT/RI ==> S

S          Generate SQL only, with no CA Batch processor commands.  Provides
           a DDL/DCL snapshot.

Also set COMMIT ASAP to Y

COMMIT ASAP (SQL ONLY)  ==> Y

Y          COMMIT all statements as soon as possible after the execution
           of each DDL or DCL statement.  Not needed for CA Batch Processor
           jobs, where implicit COMMITs are made at each .SYNC command.

This will generate just DDL statements followed by COMMIT statements. Return from update options and submit the analysis.

Submission in Batch would be recommended if there are a large number of objects to be found in the databases. Try and select a reasonable number but not too many databases all at once as a job with huge numbers of catalogue objects may cause problems on the environment due to the large amount of DB2 catalogue accesses that will necessarily be required to complete the work. Further filtering of the primary objects selected , in this case databases, can be done using the WHERE clause provided or by further modifying the wildcard used or both.

The performance increase may be alleviated if an ALTERNATE catalogue is in use that does not impact the main DB2 catalogue on the subsystem.

The resulting analysis output contains no utility statements and no CA Batch Processor commands which allows it to be used/ported to other environments and executed using other purely DB2 utilities like SPUFI or DSNTEP2 without any modifications. 

RC/Migrator will also inform if problems have been found such as primary key or RI constraint errors that might prompt corrective action before the DDL is used/ported elsewhere. 

The resulting analysis output will list the dependent objects that are auto-selected so that the user can check on the results. 

This is a small snippet of the object list that was produced for the analysis showing the objects in an indented layout:

--     P DB authid1.DBCORP6
--     X   TS DBCORP6.TSTEST6
--     X     T authid1.CARS4
--     X       I authid1.CARS4_IX2
--     X       I authid1.CARS4_IX1
--     X     T authid1.CARS3
--     X       I authid1.CARS3_IX2
--     X       I authid1.CARS3_IX1
--     X     T authid1.CARS1
--     X       I authid1.CARS_IX3
--     X       I authid1.CARS_IX1
--     X     T authid1.CARS
--     X       I authid1.CARS_IX2
--     X       I authid1.CARS_IX
--     P DB authid1.DBCORP9
--     X   TS DBCORP9.TSXYX12
--     X     T authid1.ACTY_LOG_ARC
--     P DB authid1.DBPBRRPN
--     X   TS DBPBRRPN.PBRRPN
--     X     T authid1.TABLE_UTS_PBR7_RELATIVE_ADDR
--     X       I authid1.INDEX_UTS_PBR7_RELATIVE_ADDR
--     X       I authid1.INDEX_UTS_PBR7_RELATIVE_ADDR2
--     P DB authid1.DBPF
--     X   TS DBPF.EBPFBRMR
--     X     T  BPF.BBPFBRMR
--     X       I  BPF.IBPFBRMR1
.
.
.
.
a snippet of the start of the DDL showing the CREATE DATABASE statements with their following COMMIT statements:

-- **************************************************************
-- *                                                            *
-- * DATABASE CREATE AND ALTER STATEMENTS                       *
-- *                                                            *
-- **************************************************************

-- DBCORP WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.

    SET CURRENT SQLID = 'authid1';

CREATE  DATABASE DBCORP
        STOGROUP SYSDEFLT
           BUFFERPOOL BP0
           INDEXBP    BP2
           CCSID EBCDIC
    ;

    SET CURRENT SQLID = USER;


   COMMIT WORK;
-- DBCORP1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.

    SET CURRENT SQLID = 'authid1';

CREATE  DATABASE DBCORP1
        STOGROUP SYSDEFLT
           BUFFERPOOL BP1
           INDEXBP    BP1
           CCSID EBCDIC
    ;

    SET CURRENT SQLID = USER;


   COMMIT WORK;

The layout of the rest of the DDL will be in Database, Tablespace, Table, Index order and so forth.