DB2 Universal Database: The Database Definition View, Part 2 - Page 3
February 14, 2006
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 (1,'Paul','Zikopoulos','545-666-8089')
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: email@example.com.
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.