Generating a data definition statement for a schema

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

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 schema.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 window.
From the Objects page select which objects and dependent objects that you want generated. The available choices include the Distinct type, User defined function, Procedure, and the Schema privileges associated with the schema. 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.
- In the Overrides group, select one or more of the check boxes to alter the schema name or owner.
  - Select the Schema check box and type a schema name for your generated schema. Two different schemas can each contain a function with the same name that have the same data types for all of their corresponding data types. However, a schema must not contain two functions with the same name that have the same data types for all of their corresponding data types. Therefore, to create a duplicate procedure, function or distinct type, you need to change the schema name.
  - Select the Owner check box and type a new owner name for the schema environment. This name can be the authorization ID of the process.
- In the Options group, specify new values for the generated DDL. Available fields depend on the selections from the Objects page.
  - In the Target DB2 version field, specify a release of DB2 for OS/390 for which you want the generated DDL to be used.
  - 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 schema, or remove any defaults in the new schema 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.