by Paul C. Zikopoulos
In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphere Software ("Rational AD") product to create and work with IBM DB2 Universal Database ("DB2 UDB") for Linux, UNIX, and Windows database connections. In Part 2 (and the upcoming Part 3), I want to introduce you to the many capabilities provided by the Data Definition view in the Rational AD integrated development environment ("IDE").
If you are planning to follow along in this article, you need to ensure you followed the steps in Part 1 to set up your DB2 UDB environment. You also need to connect to the NEWSMPL database from the Data Definition view. (You will know you have a connection if you are able to expand the NEWSMPL tree.) Additionally, you should ensure you are in the Data Perspective view in the IDE. Before working through the examples in this article, your Database Explorer view should look like this:
Moving from Exploration to Definition
In order to start building your data definition, or working with the database in a Java project (which I will cover in a subsequent part of this series), you need to import the schema objects from the Database Explorer into your Data Definition project.
As you may recall from Part 1, when you add a database connection to the Database Explorer view, Rational AD gives you the opportunity to do this automatically:
In Part 1, I asked you to click Cancel since I wanted to focus on the features of the Database Explorer view.
In this section, I show you how to create a project and import the data schema into it by performing the following steps:
1. Create a new project by clicking File-->New-->Project. The New Project wizard opens.
2. Select Project from the Simple folder and click Next, as shown below:
Note: You may need to select the Show All Wizards check box to see this project.
3. Type a name for your project in the Project name field and click Finish, as shown below:
You can see on this page that the Directory field is automatically filled in with a directory path to store the contents of this project. Rational AD did this because of the way I have set up my preferences. You can override this default directory and select your own by clearing the Use default check box and subsequently using the Browse button to navigate to a directory where you would like to store the contents of this project.
If you want to reference another project, you can click Next instead of Finish on this page, select a different project, and then click Finish.
For example, on my machine, I have other Rational AD projects containing classes and other objects that I might want to use. In this case, I need to create a project reference, as follows:
When the project is created, your Data Definition view will look similar to the following one. (Note that my IDE has some existing projects, but this series will focus on the DATABASEJOURNALSERIES project.)
Once you have a project, you need to import the schema for the database you want to work with into that project from the Database Explorer view. You can import the schema into any project by performing the following steps:
1. Right-click the database that you want to import into your project in the Database Explorer view and select Reconnect, as shown below:
2. Right-click the database once you have a connection to it (it will have a sign beside it when it is connected) and select Copy to Project, as shown below:
The Copy to Project wizard opens.
3. Use Browse to select the project into which you want to import the NEWSMPL database schema, and click Finish.
When the copy operation finishes, the Data Definition view (when expanded) should look similar to this:
Note that the contents of the project are in the directory you specified when setting up the project:
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:
by Paul C. Zikopoulos
Working with Your Table A Sampling of What You Can Do
At this point, you can work with this table and perform many operations on it (some of which will be covered in a future installment of this series).
Expanding PART2.CUSTOMERS shows the table's definition and key columns that you defined:
You can work with a table's definition by right-clicking it and selecting Open. You can use the More
button (or the tabs at the bottom of the view) to work with the table's defined columns, as shown below. For example, you can change the length of a VARCHAR column, add a column to the table, or work with referential integrity constraints for the table.
From any tab, clicking will return you to the top-level menu.
You can also generate the DDL for the table by right-clicking it and selecting Generate DDL, as shown below:
In the preceding figure, you can see that by selecting the Open SQL DDL file for editing when done check box, Rational AD requires you to select a project where the DDL script will be deployed. In this example, I actually chose to deploy the script to a different project (called DATABASEPROJECT), but you can choose any project you want including the one you are working in now.
If you are working in a development environment and want to move the work that you have done to another project, you can use the copy and paste features within Rational AD to move the entire contents of a project, or an object, and so on:
In subsequent parts of this series, I will cover more tasks that you can do from the Data Definition view.
Interacting with Your Table
Let's finish up this article by inserting data into our new CUSTOMERS table.
From a command prompt, connect to the NEWSMPL database and issue the following command. (Take note of the schema qualifier for the CUSTOMERS table; you will need it to successfully perform the SQL operations in this section.)
INSERT INTO PART2.CUSTOMERS VALUES
This command should result in the following error:
What happened? The Rational AD product allows you to work with a database schema but the results are not persisted to the database until the actions are deployed to the database. At a high level, Rational AD has all of these scripts behind the scenes that correlate to the work you have performed - it is just waiting to deploy on the server.
To deploy the table you created to the NEWSMPL database, perform the following steps:
1. Right-click the CUSTOMERS table and select Deploy. The Deploy wizard opens.
2. Select the table that you want to deploy and click Next.
You can deploy to your database any number of objects you created in to your database. In this simple example, since we have only created one object (the CUSTOMERS table), and we selected to deploy this object by specifically clicking this table, it is the only object shown.
If you select to deploy your solution from a higher-level object, you can specifically select which objects to deploy. For example, if you selected Deploy from the NEWSMPL object, the Deploy wizard would open and look similar to this:
3. Select the options that correspond to the way you would like to deploy this object, and click Next.
For this example, I selected the defaults.
4. Select the database where you want to deploy the object. Be a little careful here because Rational AD will default the target database to the first database connection you made in the IDE. In my IDE, I have multiple databases and Rational AD has connections to all of them. The first connection I made was to my SAMPLE database, so I have to ensure that the NEWSMPL database is selected in the Existing Connection field before clicking Finish. In addition to this, ensure you have the correct Port number entered in this page. The default port number for a DB2 UDB installation is 50000, but yours may be different and even though you have a database connection, the RAD IDE doesn't seem to pick up this attribute and instead always pre-fills this field with the default DB2 UDB port number.
You can see in the DB Output view that this deployment was successful.
Now enter the same command that you did previously, followed by a SELECT statement (do not forget to qualify the table with the PART2 schema) to ensure your data is there. This time the results should look like this:
Wrapping it all up
In this article, I showed you how to import the objects of a database from the Database Explorer view into the Data Definition view. In addition to this, I showed you some of the data-related tasks that you can perform, as well as how to deploy the solution.
In the next article, I will still focus on the Data Definition view, but I will show you how to create a Web service, a stored procedure, and SQL statements using the Rational Application Developer integration with DB2 UDB.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, MQSeries, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2006. All rights reserved.
The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.