Generating DDL statements for database objects - IBM DB2 9.7 for Linux, UNIX, and Windows

Use the Generate DDL notebook to generate DDL (Data Definition Language) statements, SQL, and statistics in a script file to recreate database objects and their statistics in another database.

About this task

To generate DDL for database objects, you need SELECT privilege on the system catalogs.

Opening the Generate DDL notebook:

To open the Generate DDL notebook from a database, from the Control Center, expand the object tree until you find the database that contains the objects for which you want to create DDL. Right-click the database and click Generate DDL in the pop-up menu.
To open the Generate DDL notebook from selected tables, from the Control Center, expand the object tree until you find the Tables folder of the database with which you are working. Click the Tables folder. Any existing tables are displayed in the pane on the right side of the window (the contents pane). Select the tables with which you want to work. Even if you are not going to generate DDL to recreate the tables, select the tables that relate to the objects for which you want to generate DDL. Right-click the selected tables, and click Generate DDL in the pop-up menu.
To open the Generate DDL notebook from a selected schema, from the Control Center, expand the object tree until you find the Schema folder of the database with which you are working. Click the Schema folder. Any existing schemas are displayed in the pane on the right side of the window (the contents pane). Select the schema with which you want to work. Right click the schema and click Generate DDL in the pop-up menu.

From the Objects page, specify which statements you want to generate.

Specifying statement types

On the Statement page and select the appropriate check boxes, as follows:

Database objects: Generates DDL statements for the database objects, such as tables, indexes, views, triggers, aliases, UDFs, data types, and sequences, excluding any table spaces, database partition groups, and buffer pools that are user-defined.
Table spaces, database partition groups, and buffer pools: Generates the DDL statements for these objects, excluding any of these objects that are user-defined.
Authorization statements: Generates SQL authorization (GRANT) statements for the database objects.
Database statistics: Generates SQL update statements for updating the statistics tables in the database.
Update statistics: Only available if you have selected Database statistics. Generates the RUNSTATS command, which updates the statistics on the database that is generated.
Note: Choosing not to update the statistics allows you to create an empty database that the optimizer will treat as containing data.
Include COMMIT statements after every table: Generates a COMMIT statement after the update statements for each table. The COMMIT statements are generated only when you select Database statistics.
Gather configuration parameters: Gathers any configuration parameters and registry variables that are used by the SQL optimizer.
XML Schema Repository (XSR) objects: XML schemas, DTDs, external entities: Generates statements to re-create XSR objects. If you select this check box, you must also specify the directory into which the generated XSR objects will be recreated.

Select the objects on which you want to base the generated DDL:

If you accessed this notebook by selecting a table or multiple tables in the contents pane, then the object page displays all selected tables in a confirmation list. Clear the check boxes of any incorrect tables.
If you accessed this notebook by selecting a database in the object tree, then the default is to generate DDL statements for all objects under the current user ID and schema within the database.
If you accessed this notebook by selecting a schema in the contents pane, then the Schema field is prefilled with the name of the schema.

Limiting generation scope

To limit the scope of the generation, and use the following options on the Object page:

To limit DDL generation to objects created by a particular user, specify that user's ID in the User field.
To limit the DDL generation to objects in a particular schema, specify that schema in the Schema field.
To limit the DDL generation to objects related to specific tables, select the Generate DDL for selected tables only check box. Then, select the tables you want in the Available tables list box and move them to the Selected tables list box.

Run, save, and schedule options

On the Schedule page, do one of the following:

To run the task now, without creating a task in the Task Center or saving the task history to the Journal, select Run now without saving task history.
To create a task for generating the DDL script and saving it in the Task Center, select Create this as a task in the Task Center. Then, specify the task information and options:
- Specify the name of the system on which you want to run the task, in the Run system box. This system must be online at the time the task is scheduled to run.
- Select the system where you want to store the task and the schedule information, in the Scheduler system drop-down box.
  This system will store the task and notify the run system when it is time to run the task. The drop-down list contains any system that is cataloged and has the scheduler enabled. The scheduler system must be online so that it can notify the run system.
  
  If the DB2® tools catalog is on a remote system, you will be asked for a user ID and password in order to connect to the database
- Optional: If you want to select a different scheduling scheme, click Advanced. The Advanced Schedule Settings window opens where you can select between server scheduling or centralized scheduling.
- To save the task in the Task Center, but not actually run the task, select Save task only.
- To save the task in the Task Center and run the task now, select Save and run task now.
- To save the task to the Task Center, and schedule a date and time to run the task later, specify Schedule task execution. The Change button is enabled.
  Select a task in the table, and click Change. A window opens where you can enter the date and time that you want to run the task.
  
  The task table displays the task name suffix and the schedule information.
- To run a task in the Task Center you must supply a user ID and password. Type the user ID and password that you want to use to run the task.

You can use the Scheduler Settings page of the Tools Settings notebook to set the default scheduling scheme. Note that if you set a default scheduling scheme, you can still override it at the task level.

Optional: If you want to view the db2look command that is used to generate the DDL script, click Show Command.

Click Generate to generate DDL script. From the window that opens, you can do the following:

Copy the script to the Command Editor
Save the script to the file-system
Run the script, and optionally save it to the Task Center.