Generating DDL statements for database objects

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

For information on generating DDL statements in DB2 Universal Database for OS/390 see the DB2 Universal Database for OS/390 online help.

Data Definition Language (DDL) is a subset of SQL. It is a language for describing data and its relationships in a database.

You might want to generate DDL, SQL and statistics for database objects for the following reasons :.

Note:You can only generate DDL statements for DB2 Universal Database Version 6 and DB2 Universal Database Version 7 databases.

Fields and controls

Authorities and privileges



To generate DDL statements for database objects:

  1. Open the Generate DDL window.
  2. Optional: Either accept the default or select a different value for Schema. Selecting the value All Schemas will generate DDL statements for all schemas in the database.
  3. Optional: Select the Database objects check box to generate DDL statements for database objects such as tables, views and indexes.
  4. Optional: Select the Tablespaces, nodegroups, and bufferpools check box to generate DDL statements for these objects.
  5. Optional: Select the Authorization statements check box to generate SQL authorization statements (GRANT) for database objects.
  6. Optional: Select the Database statistics check box if you want to generate SQL statements for table statistics in the database.
  7. Optional: If you selected the Database statistics check box, select the Update statistics check box if you want to generate a command to update the statistics catalog tables in the database that is generated.
  8. Optional: Select Include COMMIT statements after every table check box if you want to include COMMIT statements after every table.
  9. Optional: Select the Gather configuration parameters check box if you want configuration parameters to be gathered for use by the SQL Optimizer.
  10. Optional: To schedule the job that will generate DDL statements, click Schedule. The Schedule window opens.
  11. Optional: To view and save the SQL command that will generate the DDL statements, click Show Command. The Show Command window opens.
  12. To generate DDL statements, click Generate. The Run Script window opens. Type a user ID and password and click OK. A job is created with the contents of the DB2LOOK command. A DB2 Message window appears with the job ID of the new job. The job creates a script generated by the DB2LOOK command. Click OK to close the message window.
  13. Use the Job History page of the Journal notebook to view the results of this job, and to view the contents of a saved script associated with the job. Right-click the job and select Show results from the pop-up menu. The Job Results window opens. The output of the db2look command is shown in the Job output pane. The User comment pane identifies where the output file is saved. Select Create Script to create a script of the results. The New Command Script window appears.


Related information