Generating SQL to re-create a Db2 object (reverse engineering)

Before making changes to a Db2 object, you might find it useful to generate the SQL statements that are required to re-create that object. Generating this SQL ensures that the changes are applied to the current definition and that the original object definitions are available for fallback purposes.

To generate SQL to re-create a Db2 object:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, specify any filtering criteria at the bottom of the panel and one of the following options, and press Enter:
   • D - Databases
   • S - Table spaces
   • T - Tables, views, and aliases
   • A - Aliases for tables and views
   • Y - Synonyms
   • H - Schemas
   • E - User defined data types
   • F - Functions
   • G - Storage groups
   • O - Stored procedures
   • J - Triggers
   • Q - Sequences and aliases
   • GV - Global variables

   These options support the GEN command to reverse engineer objects.

3. On the relevant object panel, generate SQL by issuing one of the following commands:
   • To generate SQL for all of the listed objects, issue the GEN primary command.
   • To generate SQL for a single object, issue the GEN line command or the DDL line command against that object in the list.

     The DDL command does not provide the additional options that the GEN command provides for extracting more information, such as constraints, authorizations, or dependent objects, including triggers, labels, or comments. Instead, the DDL line command uses the default values of the GEN options. For example, pending changes are always included. For a complete list of GEN options and their default values, see Generate SQL from DB2 catalog (ADB2GENB) panel. The DDL line command is valid everywhere that the GEN line command is valid except for the Schemas (ADB21H) panel (option 1.H).

   The following figure shows the GEN line command issued against a database:

   

    DB2 Admin ------------------- DB2X Databases ---------------------- Row 1 of 4 
    Command ===>                                                  Scroll ===> PAGE  
                                                                                    
    Commands: GRANT  MIG  DIS  STA  STO  UTIL       MOVETB                                       
    Line commands:                                                                  
     T - Tables  S - Table spaces  X - Indexes  G - Storage group  ICS - IC status  
     DIS - Display database  STA - Start database  STO - Stop database  A - Auth    
     ? - Show all line commands                                                     
                             Storage  Buffer          Created      Index           
    Select Name     Owner    Group    Pool       DBID By       T E BPool    I      
           *        *        *        *             * *        * * *        *      
    ------ -------- -------- -------- -------- ------ -------- - - -------- -      
    GEN    DSN8D81A DSCGDB2  DSN8G810 BP0         258 ISTJE      E BP2      Y      
           DSN8D81E DSCGDB2  DSN8G810 BP1         260 ISTJE      U BP2      Y      
           DSN8D81P DSCGDB2  DSN8G810 BP0         259 ISTJE      E BP2      N      
           DSN8D81U DSCGDB2  DSN8G81U BP1         261 ISTJE      E BP2      N      
    ******************************* END OF DB2 DATA *******************************
   

   

4. If you specified the GEN line command or primary command, complete the fields on the Generate SQL from DB2 catalog (ADB2GENB) panel, and press Enter.
   For more information about this panel, see Generate SQL from DB2 catalog (ADB2GENB) panel.

   Note: For views that were created prior to DB2® 9 for z/OS® and for views with unqualified synonyms or aliases, you must enable the GEN function to generate SET CURRENT SQLID statements. To do so, take the following additional steps:

   1. Issue the G primary command.
   2. On the resulting Additional Generate Parameters (ADBPGENO) panel, specify a value in the View CURRENT SQLID method field. This value determines which qualifier GEN is to use for the generated SET CURRENT SQLID statement if it finds multiple objects in the Db2 catalog with the unqualified name. You can specify one of the following values:

      O

      Use the qualifier of the dependent table.

      C

      Use the qualifier of the view.

   This specified value is used to set the IMPLQUALMETHOD parameter in the batch job.

5. If you requested that a batch job be generated, you can optionally edit the batch job to add wildcard characters or make any other necessary changes. Then submit the job to generate the requested SQL.
   For information about editing the batch job, see GEN batch jobs.
6. If you requested TSO mode, read the displayed messages and press PF3 to exit. Then take one of the following actions depending on whether you requested that the SQL be added to a WSL:
   • If you did not request that a WSL be used, the SQL statements are displayed. Review them and run them as needed by using the EXECUTE or EXECUTENF commands.
   • If you requested that a WSL be used, navigate to the WSL to view and run the statements. For instructions, see Running a WSL.

   Note: If when you execute these statements, the Statement Execution Prompt (ADB2PSTM) panel is displayed (depending on your prompt options), option 1A (runs all statements) does not apply to Change Management. If you specify option 1A and request to use Change Management [on the Change Management Prompt (ADB2CMPR) panel], you must register each statement individually. You need to navigate through the Change Management Prompt (ADB2CMPR) panel and the Register Change (ADB2CMRG) panel for each statement. However, you can make each statement part of the same change by specifying the same change owner and name combination for each statement.