Reverse engineering objects
You can extract from the Db2® catalog the DDL that is required to re-create Db2 objects. This process is called reverse engineering the objects.
Before you begin
About this task
The starting point for reverse engineering can be databases, table spaces, tables, aliases, synonyms, schemas, data types, functions, stored procedures, triggers, sequences, or storage groups.
In this stage of the tutorial, you will complete the following steps:
- Reverse engineer a database.
- View the output of reverse engineering that database.
- Issue a rebind and view the output.
Procedure
- On the System Catalog (ADB21) panel, specify option D, and press Enter.
-
On the Databases (ADB21D) panel, specify the
GEN line command against the DSN8D81A database, and press Enter.
The Generate SQL from DB2 catalog (ADB2GENB) panel is displayed, as shown in the following figure:
Figure 1. Generate SQL from DB2 catalog (ADB2GENB) panel ADB2GENB -------------- DD1A Generate SQL from DB2 catalog ------------- 16:46 Option ===> Generate SQL statements for database DSN8D81A DB2 System: DD1A DB2 SQL ID: ADM001 More: + SQL statement types to be generated from the DB2 catalog: CREATE DATABASE . . . . Y (Y,N) GRANT access ON DATABASE . . Y (Y,N,A,R) CREATE TABLESPACE . . . Y (Y,N) GRANT access ON TABLESPACE . Y (Y,N,A,R) CREATE TABLE . . . . . . Y (Y,N) GRANT access ON TABLE . . . Y (Y,N,A,R) CREATE VIEW . . . . . . Y (Y,N) GRANT access ON VIEW . . . . Y (Y,N,A,R) CREATE INDEX . . . . . . Y (Y,N) ALTER TABLE ADD FOREIGN KEY. Y (Y,N,D) CREATE SYNONYM . . . . . Y (Y,N) LABEL ON . . . . . . . . . . Y (Y,N) CREATE ALIAS . . . . . . Y (Y,N) COMMENT ON . . . . . . . . . Y (Y,N) CREATE MASK . . . . . . Y (Y,N) ALTER TABLE ACTIVATE CONTROL Y (Y,N) CREATE PERMISSION . . . Y (Y,N) CREATE TRIGGER . . . . . Y (Y,N,D,A,R) CREATE STORAGE GROUP . . Y (Y,N) GRANT use OF STORAGE GROUP . Y (Y,N,A,R) REBIND PACKAGE . . . . . Y (Y,N,D) New names/values for generated SQL: (leave blank to use current values) Object schema . . . . . > Run SQLID . . . . . . . . ADM001 New names/values for generated SQL: (leave blank to use current values) Object schema . . . . . > Run SQLID . . . . . . . . ADM001 Object grantor . . . . . > Alloc TS size as . . . . DEFINED (DEFINED, USED, or ALLOC) Database name . . . . . Storage group for TS . . > Storage group for IX . . . > Target DB2 version . . . (Current DB2 version: 1215) Target Function Level. . (Current DB2 FL: 508) Use Masking . . . . . . NO (Yes/No) Use Exclude Spec . . . . NO (Yes/No) Target cat qualifier . . > (Default is SYSIBM) Generate catalog stats . NO (Yes,No,Only) Statistics tables . . ALL (All or Select. Default is All) NO (Yes,No,Alter,Only) PBG NUMPARTS value . . . (Defined, Existing) PBG LOB objects . . . . (Computed, Implicit) Generate index cleanup . (Yes,No,Only) Generate hidden ROWID . NO (Always, Only, No. Default is No) Physical part order . . NO (Yes/No) SQL output data set and execution mode: Add to a WSL . . . . . . NO (Yes/No) Data set name . . . . . Data set disposition . OLD (OLD, SHR, or MOD) Execution mode . . . . . TSO (BATCH or TSO) Commit statements per . (Db, tS, Tb, All, None. Default is All) DB2 defaults handling . (Keep, or Remove. Default is Keep) Prompt to run SQL . . . NO (Yes/No. For TSO mode and no WSL) Include SQL comments . . NO (Yes/No) DB2 Command output data set: Data set name . . . . . Data set disposition . OLD (OLD, SHR, or MOD) BP - Change batch job parameters G - Change additional parameters
-
To view the output, press Enter.
The following figure shows part of the result of reverse engineering this database:
Figure 2. Reverse engineering output (1 of 2) ------------------------------------------------------------------------ -- -- -- Database 2 Administration Tool (DB2 Admin) , program 5655-DAT (C) -- -- -- -- ADB2GEN - Extract object definitions from the DB2 Catalog tables -- -- -- -- Input prepared on : DB2X (810) Extract time : 2013-16-04 01:01 -- -- -- -- Catalog values overridden : none -- -- -- -- Generate : SG=Y DB=Y TS=Y TB=Y VW=Y IX=Y SY=Y AL=Y LB=Y CM=Y FK=Y -- -- TG=Y UT=N UF=N SP=N -- -- Grants : SG=Y DB=Y TS=Y TB=Y VW=Y SC=N UT=N UF=N SP=N -- -- -- ------------------------------------------------------------------------ ------------------------------------------------------------------------ -- -- -- ADB2GEN: Generate DDL for Database DSN8D81A -- -- -- ------------------------------------------------------------------------ -- -- ------------------------------------------------------------------------ -- Database=DSN8D81A Stogroup=DSN8G810 ------------------------------------------------------------------------ -- SET CURRENT SQLID='DSCGDB2'; -- CREATE DATABASE DSN8D81A BUFFERPOOL BP0 INDEXBP BP2 CCSID EBCDIC STOGROUP DSN8G810 ; -- GRANT DBADM ON DATABASE DSN8D81A TO PUBLIC; -- COMMIT; --Figure 3. Reverse engineering output (2 of 2) ------------------------------------------------------------------------ -- Database=DSN8D81A Stogroup=DSN8G810 -- Tablespace=DSN8D81A.DSN8S81D ------------------------------------------------------------------------ -- CREATE TABLESPACE DSN8S81D IN DSN8D81A USING STOGROUP DSN8G810 PRIQTY 32 SECQTY 20 ERASE NO FREEPAGE 0 PCTFREE 5 GBPCACHE CHANGED TRACKMOD YES BUFFERPOOL BP0 LOCKSIZE PAGE LOCKMAX SYSTEM CLOSE NO COMPRESS NO CCSID EBCDIC MAXROWS 255; -- GRANT USE OF TABLESPACE DSN8D81A.DSN8S81D TO PUBLIC; -- COMMIT; -- ------------------------------------------------------------------------ -- Table=DSN8810.DEPT In DSN8D81A.DSN8S81D ------------------------------------------------------------------------ -- SET CURRENT SQLID='DSN8810'; -- CREATE TABLE DSN8810.DEPT (DEPTNO CHAR(3) FOR SBCS DATA NOT NULL , DEPTNAME VARCHAR(36) FOR SBCS DATA NOT NULL , MGRNO CHAR(6) FOR SBCS DATA WITH DEFAULT NULL , ADMRDEPT CHAR(3) FOR SBCS DATA NOT NULL , - Return to the Generate SQL from DB2 catalog (ADB2GENB) panel to choose to rebind the plan/package and view the output.
-
In the REBIND PLAN/PACKAGE field, specify Y, and
press Enter.
The output is the same as shown in the previous figures and includes the following output:
Figure 4. Reverse engineering rebind output Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000001 REBIND PACKAGE(DSN8ES81.DSN8ES1) ****** **************************** Bottom of Data ****************************