Now that you created the table in your physical data model, you
can generate the DDL statements that describe the table in SQL and then run
the DDL to promote the table to the data server.
Before generating the SQL, check that you are still connected to
the data server. The status bar below the Database Explorer should say:
<Live Connection> name_of_connection
To promote the table to the data server:
- In the Data Project Explorer, right-click the table and select Generate
DDL. The Generate DDL wizard
opens. The following check boxes appear on the first page:
- Fully qualified name
- The table will have a two-part name: schema.table_name.
In this case, the name will be SCHEMA.EMPLOYEE.
- Quoted identifiers
- Quotation marks will surround identifiers such as the schema and the name
of the table.
- DROP statements
- This option generates DROP statements for the objects that you are generating
DDL for. For now, you can leave this check box unselected. Later in the tutorial,
you will drop and re-create the table after modifying it.
- CREATE statements
- This option generates CREATE statements for the objects that you are generating
DDL for.
- ALTER statements
- This option generates ALTER statements for objects that support these
statements.
- COMMENT ON statements
- This option adds comments in the descriptions of various objects in the
metadata catalog in the data server.
- GRANT statements
- This option generates GRANT statements for any privileges that you
set up on an object. Later in the tutorial, you will grant privileges
on this table.
- Accept the defaults on the first page of the wizard and click Next.
- On the next page, you select the objects that you want to generate
DDL for and click Next.
Although
you did not create an index on your table, you do not need to deselect the Index check
box. Classic Data Architect does not try to generate DDL for objects that
do not exist in a physical data model.
- On the Save and Run DDL page, notice these things:
- The Folder field shows that the wizard will create
the table in the Tutorial project.
- The File name field shows the name of the file
that will contain the DDL statements. By default, Classic Data Architect names
each file script. If there is already a file with the same
name in the SQL Scripts folder in the selected project,
Classic Data Architect adds a number to the name.
- The Preview DDL pane shows the CREATE statement for the table and any
associated statements. You can't edit the statements directly in this pane.
If you want to edit the statements before you run them on the data server,
select the Open DDL file for editing check box and
do not select the Run DDL on server check box.
- The Statement terminator field lets you change
the character that ends the statements. By default, the character is a semicolon.
- The Run DDL on server check box, if selected, tells
the wizard to run the DDL immediately after generating it. You can see the
results in the Database Explorer and in the Data Output view. If you cannot
see the new table in the Database Explorer, right-click the Tables folder
and select Refresh.
- Select the Run DDL on server check box,
the Open DDL file after editing check box, and click Next.
- Select the Use an existing connection radio
button and select the connection that you created to the data server. Then,
click Next.
The final page of
the wizard summarizes what the wizard will do when you click the Finish button.
- Click Finish.
The script.sql file appears in the
SQL Scripts folder
in the Data Project Explorer and an editor opens with the content of the file.
Figure 1. The script.sql file in the Data Project Explorer and the editor. Note
that the default high-level qualifier appears for the data set. The high-level
qualifier might be different in your installation.
In the Database Explorer, if you expand the connection that you created
to the data server, you can see the table listed in the
Tables folder
in the
SCHEMA folder under the
Schemas folder.
Figure 2. The EMPLOYEE table on the data server
In the Data Output view, you should see these results:
Figure 3. Results
in the Data Output view after promoting the table to the data server