Generate and Execute DDL

Using the Database Builder you can generate DDL for a specific database object, for a Package in the database, or for the whole database definition (if you have created your model using one of the Database Engineering model patterns, when you select to create DDL for a Package the DDL Generator automatically selects the <<Database>> stereotyped Package).

A default option when you specifically generate DDL for an object or a Package is to generate the DDL directly to the DDL Execution Engine, used through the Execute DDL tab. You can therefore develop your model, generate the DDL and execute it to update a connected database within the one tool, in a smooth sequence of tasks.

You can also generate the DDL to update the database as part of the Show Differences facility, where you compare the database and model and can  update one from the other. This again writes the DDL directly to the Execute DDL tab where you can run it to update the connected database.

In a related but separate facility, you can extract or create individual SQL Queries (script statements) and test them against the live database independent of the model, using the SQL Scratch Pad tab.

Access     Tools | Database Builder | Right-click on component | Generate DDL

 
For details of generating DDL, see the Generate DDL For an Object and Generate DDL for a Package topics.

Execute DDL

If you have selected the Generate to DDL Execution Engine option on the Generate DDL dialog, when the generation is complete the system automatically switches to the Execute DDL tab of the Database Builder. The DDL Execution Engine requires a valid ODBC connection; by default the system will, by default:

·Use the current active connection specified in the Database Builder, or
·Automatically activate the single connection specified in the Database Builder, or
·If there are several connections specified but none active, display a message prompting you to select from the list of possible connections, or
·If there are no connections defined, prompt you to create one

 
 

Field/Option/Button

Action

Execution Queue

Initially lists the items to be executed in the correct execution sequence, showing the object to be acted on, the task to be performed and the action to be taken.

As you execute or skip any or all of the items, they are moved to the bottom panel to indicate that they have been acted on and, if executed, the result of that execution is shown next to the object, task and action.

If you reset a failed or skipped item, it is returned to the Execution Queue.

 

Script

For the selected item in the Execution Queue OR in the bottom panel, shows the script to be executed. This panel provides the facilities of the common code editor, and you can work on the script to change or correct it.

 

Save

Click on this button to save the currently-displayed script as an SQL file.

A source file browser displays on which you type in the new file name and click on the Save button to save the script.

 

Reset Failed Actions

If any items generate errors when they are executed and/or you have omitted (skipped) them from execution, click on this button to restore them to the Execution Queue for checking and editing.

 

Skip

If you do not want to execute an item, or if an item has executed with errors after you have clicked on Execute All, select it and click on this button to move it to the bottom panel with no further processing.

You can restore skipped items to the Execution Queue using the Reset Failed Actions button.

 

Execute

Select an item in the Execution Queue and click on this button to execute that item.

The item is moved to the bottom panel and the results of executing the item are displayed at the end of the item. An item that executes with errors is automatically moved to the bottom panel; compare this with the Execute All button.

 

Execute All

Click on this button to execute all the items in sequence.

·If an item executes successfully, the item is moved to the bottom panel
·If an item generates errors, processing stops and a message displays to identify the error; click on the OK button and either correct the error in the script or 'skip' the item, then click on Execute All again

 

An item that executes with errors is not automatically added to the bottom panel; you must select it and 'skip' it to move it so that you can restart execution.

 

Learn more