RC/Migrator : Extract all ddl of all my databases
Article ID: 223475
Updated On:
Products
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 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 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 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 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.
Additional Information
Feedback
