Generating a data definition statement for a database

Use this notebook to re-create database objects by generating the SQL statements necessary to duplicate the database environment. The data definition language (DDL) statements generated are for the selected database and its dependents defined at the current server.

You can use this notebook to:

Extract the DDL for an object before changes are made. You then have a copy available for fallback purposes.

Extract a database environment to move your DB2 objects to another DB2 subsystem. If you modify some of the options on the Options page of this notebook, you can generate the DDL to create a duplicate environment that can be used in another subsystem.

Authorities and privileges

To generate a DDL statement:

Open the Generate DDL notebook.
From the Objects page, select which objects and dependent objects that you want generated. The available choices include the database as the first option, and then all database dependents. If you clear the check boxes for certain objects, other related objects become unavailable (including some on the Options page). You must choose at least one object.
Optional: From the Options page, select one or more options to change the statement from the default state:
1. In the Overrides group, select one or more of the check boxes to alter the database environment definition. Available selections depend on the selections from the Objects page. You can:
  - Select the Database check box and then type a new name for your generated database. The database name is a short identifier (8 characters). The identifier must start with a letter and must not include special characters. The name must not start with DSNDB.
  - Select the Storage group for table space check box and then type a new storage group name for the table spaces. This check box is available if, on the Objects page, you selected to generate the table spaces with this database. A storage group name is a short identifier (8 characters) that designates a storage group. The identifier must start with a letter and must not include special characters.
  - Select the Storage group for index check box and type a new storage group name for the indexes. This check box is available if, on the Objects page, you selected to generate the indexes with this database. A storage group name is a short identifier (8 characters) that designates a storage group. The identifier must start with a letter and must not include special characters.
  - In the Owner field, type an 8 character authorization ID of the owner of the database. For example, if you type NEWOWN in the field, the DDL that is generated will include a "SET CURRENT SQLID='NEWOWN'" statement before the CREATE DATABASE statement.
1. In the Options group, specify new values for the generated DDL. Available fields depend on the selections from the Objects page.
  - In the Space type field specify how the space for an index or table space is actually allocated.
    
    As currently allocated
    
    The generate DDL function will use column SPACE in catalog table SYSIBM.SYSTABLESPACE or SYSIBM.SYSINDEXES. If the value of column SPACE is 0, the generate DDL function will use columns PQTY and SQTY in catalog table SYSIBM.SYSTABLEPART or SYSIBM.SYSINDEXEPART.
    
    As currently used
    
    The generate DDL function will use columns SPACE and PERCACTIVE in catalog table SYSIBM.SYSTABLEPART. For indexes, use the same information as the As defined in DB2 option.
    
    As defined in DB2
    
    The generate DDL function will use columns PQTY and SQTY in catalog table SYSIBM.SYSTABLEPART or SYSIBM.SYSINDEXEPART.
  - In the Target DB2 version field, specify a release of DB2 for OS/390 for which you want the generated DDL to be used. If you select a release earlier than version 5, the subsystem is DB2 for MVS.
  - In the Commit frequency field, specify how often you want to commit the statements.
  - In the Default handling field, specify the default handling to be used. You can specify to use the defaults as defined in the original database, or remove any defaults in the new database definition wherever possible.
From the Output page, designate the file (mainframe or workstation) that will contain your new DDL statement. You can define a separate data set or file for the report and the generated statements. You must designate at least one output file name or data set name to generate the DDL.
- In the Output report to data set field and Output DDL to data set field, type an identifier of 1 to 44 characters that identifies a cataloged name for a data set defined in the OS/390 system and used by the DB2 for OS/390 subsystem.
- In the Output report to file field and Output DDL to file field, type a file name that will be saved on your workstation. You can also click the push button to open a Save As window to select a file name.
Click OK to generate the DDL for this object.