Reverse engineering

Reverse engineering lets you reverse engineer the Db2® objects in your database catalog.

Reverse engineering generates the SQL statements necessary to re-create a Db2® object.

Use the GEN line command on the corresponding object list panel to reverse engineer any of the following objects:
  • Database
  • Table space
  • Table or view
  • Index
  • Schema
  • Distinct type
  • Function
  • Stored procedure
Note: The GEN line command applies to objects at the current server only. If you enter the GEN line command against an object at a remote location, the location information is discarded when the Generate SQL From Db2® Catalog panel is displayed.

To generate DDL for objects at a remote location, first connect to that location, then use the GEN line command.

GEN line command entered on Databases object list panel shows the GEN line command entered on a database object list panel:
Figure 1. GEN line command entered on Databases object list panel
  Process   Options   Utilities   Help
 ______________________________________________________________________________
 FM/Db2 (DFG2)                     Databases                 Row 51 to 54 of 54

 Location:

                                                               Index
                         Storage  Buffer          Created      Buffer
 Cmd   Name     Owner    Group    Pool       DBID By     + T E Pool
       *_______ *______+ *_______ *_______ _____* *_______ * * *_______
 _____ CONVDB4  OSPEED   SYSDEFLT BP0         302 OSPEED     E BP0
 GEN__ JOHNLEDP SIROED   JOHNLEG0 BP0         304 SIROED     E BP0
 _____ GPCOPYL  GRAHAMP  GRAHAMP0 BP0         276 GRAHAMP    E BP0
 _____ UTILBIG1 OSPEED   DB2BIG   BP0         306 OSPEED     E BP0
 ******************************* END OF Db2 DATA *******************************






 Command ===> _____________________________________________________ Scroll PAGE
  F1=Help      F2=Split     F3=Exit      F4=Expand    F7=Backward  F8=Forward
  F9=Swap     F10=Left     F11=Right    F12=Cancel
Typical uses for reverse engineering include:
  • Extracting the DDL for an object before changes are made, so that the changes are applied to the current definition and are available for fallback purposes.
  • Move Db2® objects to another Db2® subsystem. By using reverse engineering (together with the Db2® table unload and load facilities), objects can be moved after a few manual modifications to the generated SQL and batch jobs.

When extracting database, table space, and table objects, all dependent objects can also be generated; this includes table spaces, tables, indexes, views, synonyms, aliases, referential constraints, table checks, and table triggers. When extracting objects in schemas, reverse engineering can extract the dependent distinct types, functions, and stored procedures. All authorizations to these objects can also be generated.

You can generate the SQL statements using a batch or online job. If you are using FM/Db2 to extract several objects from a large catalog, batch jobs are recommended.

If you use the GEN line command, FM/Db2 displays the Generate SQL From Db2® Catalog panel.

On the Generate SQL From Db2® Catalog panel, you can:
  • As an option, specify new values for the :
    • Storage group (possibly using a different storage group for table spaces and index spaces).
    • Database.
    • Specify a new object owner. If specified, the new owner is used whenever an object is created.
    • Specify a new schema name (where applicable). If specified, the new schema is used whenever an object is created.
    • Specify the data set in which FM/Db2 places the generated SQL.
    • Specify whether the SQL generation runs as a batch or online job.
    • Specify how often reverse engineering adds an SQL COMMIT statement to the generated SQL.
    • Specify whether Db2® default parameters are removed or kept in the generated SQL.

If you specify an execution mode of BATCH, FM/Db2 generates a batch job and displays the job in an ISPF Edit session, ready for any modifications you need to make before submitting the job for execution. If you specify TSO, FM/Db2 generates the SQL statements online and displays the results.

Related tasks

Related references