DB2 Universal Database: The Database Definition View, Part 2 - Page 2

February 14, 2006

by Paul C. Zikopoulos

Working in the Data Definition View

The Data Definition view gives application developers all sorts of powerful features. For example, you can create SQL statements and database schema objects, generate Enterprise Java Beans from SQL statements or tables, generate user-defined functions (UDFs), stored procedures, and data definition language for the schema objects, deploy a solution, create WebSphere MQSeries functions, and more.

In the remainder of this article (and the next Part in this series), I will take you through a number of these features and give you examples that you can try on your own.

Creating a Schema

You can use the built-in wizards in Rational AD to create a schema for your database. To create a schema, perform the following steps:

1. Under your project folder, right-click the database where you want to create your objects, and click New-->Schema Definition. The New Schema Definition wizard opens (see the next figure).

When you expand the database associated with your project, you will see that the schemas that exist in your database are surfaced in the Data Definition view. This means that you can create database objects within them (if you have the correct privileges) without creating a new schema. I am just showing you how to create a schema to illustrate some of the rich capabilities of the Rational AD IDE, and to distinguish our current examples from future examples in subsequent parts of this series.

2. Type the schema name PART2 in the Schema name field and click Finish, as shown below:

You can see that this schema has now been created in your Data Definition view, in the project that contains the database that you imported:

In the Schema Definition window (the first figure in Step 2) you can see there is a Browse button beside the Database field. This button allows you to select a database that does not exist in your project.

For example, you could select the SAMPLE database from a different project:

In the previous figure, you can see that I am instructing Rational AD to create the schema PART2 in a database that is part of the MYDB2PROJECT. Note the warning message in red: you must select a database to create a schema in since a schema does not belong to a project but rather to a database.

If I select the SAMPLE database for this schema, the Data Definition view would look like this:

The point here is that you have a lot of flexibility.

You can delete a schema simply by highlighting it and clicking the Delete key, or selecting the schema name, right-clicking it, and selecting the Delete option.

Creating a Table

You can create a table in a schema using the Create Table wizard that is provided in the Rational AD IDE. To create a table, perform the following steps:

1. Right-click the Tables folder under the schema you want to work with and click New-->Table Definition. The New Table Definition wizard opens.

2. Type CUSTOMERS as a table name and an optional description of the table in the Comments field, and click Next.

3. Add columns to your table using the Add Another button so that the table definition looks like the figure below, and then click Next:

You can use Delete to remove a column.

Make the ID column an INTEGER-based column; the FIRSTNAME and LASTNAME columns are VARCHAR columns with a size of 50; the EXTENSION column is a CHARACTER column with a length of 4.

The Table Columns page provides a drop-down list to help you select from the built-in data types in DB2 UDB. (It will also include any user defined data types created in your database.) For each column, you can select other attributes such as its nullability, default values, whether it is a key column, and more:

Note how the Nullable check box is inactive for the ID column? This is because the Key column check box is selected, which means this column will be the primary key of the table.

4. Select a primary key for your table. For this example, select the ID column and move it to the Column(s) in Primary Key pane by clicking >,as shown below. Then click Next.

If you selected the Key column check box for the ID column in Step 2, that column would already appear in the Column(s) in Primary Key pane and be identified as this table's primary key.

5. You can optionally add a foreign key to the referential constraints you are defining for this table. For this example, just click Finish.

When the wizard is finished, expand the Tables folder in the Data Definition view for the PART2 schema so that you can see the table you created: