Generate DDL

Use the Generate DDL in IBM Developer for z/OS (IDz) to create Data Definition Language (DDL) scripts to recreate and drop elements that already exist in the catalog. DDL is a subset of SQL commands that are used to create and modify database objects. You can generate DDL to completely re-create a database or only certain parts of it. For example, by generating DDL for database objects you can:
  • Keep a snapshot of the database structure.
  • Set up a test system where the database acts like the production system but contains no data.
  • Produce templates for new objects based on existing ones.
You can generate DDL for the following Db2 objects:
  • Databases
  • Indexes
  • Tables
  • Table spaces
  • Views

Prerequisites

To generate DDL for Db2 objects you need the SYSADM authority, or you can use the following statements to grant the required privileges:
GRANT MONITOR1 TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSTABLES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSTABLESPACE TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSDATABASE TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSSTOGROUP TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSVOLUMES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSTABLEPART TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSDATATYPES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSSEQUENCES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSSEQUENCESDEP TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSFIELDS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSINDEXES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSKEYS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSCHECKS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSKEYTARGETS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSINDEXPART TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSRELS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSVIEWDEP TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSDEPENDENCIES TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSSYNONYMS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSVIEWS TO {user ID}; 
GRANT SELECT ON SYSIBM.SYSENVIRONMENT TO {user ID}; 
GRANT SELECT ON SYSIBM.DSN_PROFILE_TABLE TO {user ID}; 
GRANT SELECT ON SYSIBM.DSN_PROFILE_ATTRIBUTES TO {user ID};

Launching and Using the Generate DDL Wizard

  1. Accessing the Feature:
    • In the Remote Systems View, navigate to the supported Db2 object (e.g., Table, Database, Index, Table Space, or View).
    • Right-click the object and select Generate DDL.

  2. Wizard Overview:
    • The Generate DDL wizard guides you through the process of creating DDL scripts.
    • The wizard consists of two pages where you can specify options for the DDL generation, the Options page and the Objects page.
  3. Options Page:

    On the Options page, choose whether to generate CREATE or DROP statements. One selection is mandatory to proceed. Press Next to continue.

  4. Objects Page:

    On the Objects page, select which types of Objects the generated DDL will Drop or Create. Press Finish to generate the DDL content.

  5. After completion, the generated results are displayed in the SQL Editor as an unsaved file. Optionally, you can use the Save As command to save the contents to a local or remote location.