Create schema and generate DDL

Defining a database

To define a database, you must have a project defined. Once your project is defined, follow these steps:

  1. To access the Data Definition view:

    1. Click Window > Open Perspective > Other to open the Select Perspective dialog.

    2. Select Data and click OK.

    3. Select the Data Definition tab to switch to the Data Definition view if it is not already showing.

  2. From the pop-up menu of a project where you want to create the database, click New > New database definition. The New Database Definition wizard opens.

    New database definition

  3. In the Database name field, type the name of the database that you want to create.

  4. Select the database vendor type. When you generate SQL DDL for your schemas, it conforms to the database type that you select here.

    New database dialog

  5. Click Finish. The wizard closes, and your database appears under the project folder.

Defining a schema

After you have created a database, you can define schemas to organize the tables and other data objects.

To define a schema, follow these steps after you have defined a database:

  1. Switch to the Data perspective's Data Definition view.

  2. From the pop-up menu of your database, click New > New schema definition. The New Schema Definition dialog opens.

    New Schema Definition

  3. In the Schema name field, type the name of the schema that you want to create, and click Finish. The schema is added to the database folder.

    New Schema dialog

  4. Expand the schema to display the folders underneath it. You can use these folders to add the rest of your data objects such as tables.

    New Schema

Defining a table

Tables are logical structures maintained by the database manager. Tables are made up of columns and rows. You can define tables as part of your data definitions in the Data perspective. Before you can define a table, you must first have a database and a schema defined.

To define a table in your database, follow these steps:

  1. In the Data Definition view of the Data perspective, select the Tables folder under your schema.

  2. From the pop-up menu of the folder, click New > New table definition to open the New Table Definition wizard.

    New Table Definition

  3. Follow the prompts in the wizard to define table properties, add columns, and define primary and foreign keys.

    New Table Wizard

    New Table Wizard

    New Table Wizard

  4. Click Finish. The table is added to the Tables folder.

    New Table

You must define at least one column and a primary key for the table. You can define foreign keys if you have other tables already defined.

NOTE: If you delete a primary key (either by removing the constraint, its columns, or the table that defines it), any foreign key constraints that reference the key are also removed.

You can edit the table information, add or delete columns, change the primary key, and add or delete foreign keys afterwards by using the table editor. To open the table editor, double-clicking on the table in the Data Definition view.

Table Editor

Deploying to the database

You can define, generate, and apply SQL Data Definition Language (DDL) statements in the Data Definition view of the Data perspective.

Data Definition Language (DDL) is a subset of SQL. It is a language for describing data and its relationships in a database. Use the Generate SQL DDL diaog to generate SQL DDL statements used to recreate a database object.

You might want to generate SQL DDL statements for database objects for the following reasons:

Generating an SQL DDL file

You can generate an SQL DLL file for an object. SQL DDL statements are used to change the structure of the database, such as define a new table, change the definition of a table, and remove a table.

Once you have created data definitions in your Data perspective, you can generate the SQL DDL file for the data object as follows:

  1. If the file that you want to re-generate is currently open in an editor, you will be prompted to reload the changes. At this time, you need to decide whether or not you want to keep the changes.

  2. From the pop-up menu of the database node, table, schema, or the view objects, click Generate DDL to open the Generate SQL DDL dialog:

    Generate DDL

  3. Provide the name and target location for the generated file. By default, it is named after the currently selected data object, and placed in the same project.

  4. Select whether to use fully qualified names (including the target schemas for the statements).

  5. Select whether to generate delimited identifiers. Place quotation marks around the identifiers.

  6. Select whether to generate the associated DROP statement for each CREATE statement contained in the resulting script.

  7. Select the Open SQL DDL file for editing when done check box to immediately start editing the file once it is generated:

    Generate DDL dialog

  8. Click Finish to generate the file.

    DDL Editor

The SQL DDL script is now visible under the target project:

SQL DDL Script

Applying DDL scripts to a remote database

You can apply the statements defined in an SQL DDL file by running the script against a remote database.

  1. Switch to the Data perspective's Data Definition view.

  2. Locate the SQL DDL script that you want to apply. The SQL DDL script has the file name extension .sql. If the script is not showing up in the Data Definition view, check your Data Definition filter preference (Menu button in Data Definition view and then click Filters) to make sure it is not being filtered out.

  3. From the script's pop-up menu, click Run on Database Server to open the Run Script wizard.

    Run on Database Server

  4. Select the statements that you want to execute against the remote database.

    Select the options statements

  5. Click Next to open the Run Script Options wizard.

  6. Select the options to be used when running the script.

    Select the options

  7. Click Next to switches to the Database Connection page of the wizard.

  8. Complete the required fields needed to establish a JDBC connection to the remote database you wish to apply the DDL to.

    Connection

  9. Click Finish.

    Confirm

New schema created

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCBCD 1.3 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free Mock Exam Engine