Generate DDL

Use this window to generate DDL statements for your database objects.

DDL (Data Definition Language) refers to the SQL statements that create the objects (tables, views etc) in your database. Many people find it useful to generate and save the DDL for their database objects, as it gives them the ability to recreate their objects on that or another database.

DDL is part of the Administration Component of AQT.

There are two ways to start the DDL function. From the Database Explorer window:

• click on a object, then click on the DDL button in the Administration toolbar. This will allow you to generate the DDL for all those objects in that Schema.
• for some databases there will be a DDL button in the main menu. This will allow you to generate the DDL for all objects in the database, irrespective of the Schema.

  If you have a lot of objects in your database, it can take a few seconds for AQT to retrieve the list of the objects to be displayed in the DDL window.

The Generate DDL function:

• generates complete DDL for tables, including tablespace and other clauses
• includes DDL for related objects such as primary key, indexes, foreign keys, constraints, views, aliases and triggers
• can also include the Grant statements that establish the security rights for the table
• can generate DDL for other objects than tables. Any object whose definition can be obtained from the system tables can have its DDL generated. This includes tablespaces, functions, procedures, bufferpools, users.

When you start the Generate DDL function, you will be shown all the tables (or other objects) within the current schema. You can select the tables you wish to generate the DDL for.

Options

There are a number of options for the way the DDL is generated.

Send Output to

This specifies where the generated DDL is to be written to.

• Window. The DDL is displayed in a message-box. This is the only option available if you are in evaluation mode of the Administration Component.
• Single file. The DDL is written to a file. You can specify whether the DDL is to Replace or Appended to the file.
• One file per object. This option allows you to generate your DDL into separate files per table/object. This is useful when you are generating the DDL for multiple objects, and you want the DDL for each object in a separate file.
• Directory. When you have specified One file per object, this gives the directory where the files are created.
• File Mask. When you have specified One file per object, this determines the names of the files that will be created for the object DDL. AQT will substitute the object name (excluding the schema name) where <obj> is specified in this mask. For instance, if AQT is generating the DDL for table dbo.customers, <obj>.ddl will create this in a file named customers.dll.

Delimiter

This specifies the character used to delimit the multiple SQL statements in the DDL script.

Include DDL for

This list will contain the different parts of the DDL that can be generated. These will be:

• your main object (eg. table)
• the related objects (eg.primary key, indexes, foreign keys)
• authorities for the object (if appropriate)

You can select which of these parts of the DDL are to be generated. At least one option can be checked. It is OK to generate DDL for a related object only (eg. for only the indexes and not the tables).

Warning: you should only include the options you need - every option you select will increase the number of queries AQT needs to run against your system tables, and will increase the time to generate the DDL.

Generate foreign key DDL at end of script

By default, the DDL for a foreign key will appear in the DDL script after the DDL for the source table. This can cause a problem when the script is run, as the foreign key cannot be created until after both the source and referenced tables have been created.

Avoid that problem by selecting this option. When this is done, the DDL for the foreign keys is placed at the end of the DDL script.

If you are generating DDL to separate files, the file name for the foreign key DDL will be taken from the File Mask, with fk replacing <obj>.

Generate View DDL at end of script

This is similar to the previous option. If you have multi-table views, these cannot be created until after the creation of all the tables that the views are based on. For this reason you may wish to have the view DDL appear at the end of the DDL script.

If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with view replacing <obj>.

If you have multi-table views, the views may appear more than once in the DDL script.

Generate Trigger DDL at end of script

This is similar to the previous options. If your triggers refer to several tables, these cannot be created until after the creation of all the tables. For this reason you may wish to have the trigger DDL appear at the end of the DDL script.

If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with trigger replacing <obj>.

Include commented-out Drop statement

When this option is checked, AQT will include a commented-out drop statement before the definition of each object in your DDL script. It is often useful to have such a drop statement in your scripts, as it makes it easy to drop and redefine your objects.

Include full DDL for related objects

The Include DDL for list contains your main object (eg. table) and all the related objects (indexes, views, triggers etc). When generating the DDL for the related objects the question arises as to how much if their DDL is to be generated. With Include full DDL for related objects is selected, AQT will generate the full DDL for the related objects.

For example: if you are generating the DDL for tablespaces, and you have selected that the DDL for the tables is also to be generated, this option will generate the complete DDL for the tables.

Generate For

This specifies the database for which the DDL is to be generated. By default this is the database from which you are generating the DDL. You can change this to another database type - AQT will then generate the DDL in the format required for that database.

This is a complex thing for AQT to do - it will need to convert table definitions from the format of one database to the format of another. It will also need to convert data types between different database types. There are limits to how well AQT can do this.

Type Mapping

This is used when the DDL is being generated for another type of database (Generate For is specified). This specifies how data types are mapped from the database to the Generate For database.

Example: specifying datetime->timestamp will ensure that datetime columns (in the database) are generated as timestamp columns in the DDL.

You can specify multiple mappings, separated by commas. Example: datetime->timestamp,bigint->double

Ensure lines are less than 72 bytes

When this option is selected, AQT will ensure that the generated DDL has a maximum line length of 71 bytes. This is useful if you are generating DDL which is to be transferred to a DB2 z/OS system.

This function will split lines at either a blank or a comma, unless these are within a string. If your DDL has large text fields, AQT will split the line in the middle of the text, which may not give correct results.

Format View definitions

When this option is specified, AQT will reformat the view definition into a more readable format. This is the same as using the Format SQL function of the Run SQL window.

Include a Commit after each object

When this option is specified, AQT will include a Commit statement at the end of the DDL for every object.

Generate

Use this button to generate the DDL for the objects you have selected. You can cancel the processing by clicking on Abort.

Saving and Loading your DDL Options

Once you have set up your options, you can save these with File > Save Options. The options will be saved to file on disk. These options include the Include DDL for... items, so will be specific for a type of object.

Once you have save the options, you can load them with File > Open Options.

Running DDL in Batch

You can run the DDL as scripting statement. To generate a script statement, use File > View Script Statement. You can copy this statement and use it in the Run SQL window or incorporate this in a batch file.

The script statement will generate the DDL for all objects in your Schema. You can specify a Filter to limit this to a given set of tables.