Building Tables with the DB2 Designer and Visual Studio 2005 - Page 3

September 12, 2006

If you wanted to create a table that had columns from a number of tables, this would be a great feature to use. Many customers I work with have best practice 'skeleton' tables used for different topics. For example, you may have a table that includes all customer information while another has defined columns for transactional operations. You could use this feature to 'discover' and automatically add these columns to your table. This is a real rapid application development booster and it's unique to the DB2 9 product!

6.  The Columns field is populated with all of the columns in the EMPLOYEE table (or the columns across a combination of tables if you selected them), as shown below:

7.  Select the EMPNO column and delete it by clicking Delete Column (). This will delete the column you created in Step 3.

8.  Rename the EMPNO1 column to EMPNO by double-clicking the column and entering EMPNO. The Columns section of the table designer should now look like this:

9.  Select the EMPNO column and expand the Column Properties window, as shown below:

You can use this section of the table designer to alter characteristics of each of the columns in your table. For example, you can create an Identity Column with specifications for the number of rows to initially cache, the increment level, and so on.

10.  Click the column to the right of Primary Key and set it to True, as shown below:

11.  Click the Keys icon (). You can see the EMPNO column is now a primary key for your table:

You can use this panel to add foreign keys to your table as well, to rename or alter the primary key, and so on.

12.  Click the Index icon (). The Index portion of the table designer opens.

13.  Add a descending index on the EMPNO column by clicking Add Index () in the Indexes window, changing the default index name to EMPNOINDEX, and changing the Order attribute (in the Select Columns window) to DESC, as shown below:

You can use the Index Properties field to change properties for the index in the same manner in which you can change a column's properties for a table. For example, you can make this index a clustered index.

You can use the other icons in the table designer to accomplish other tasks on your table, as shown in the following figure:

You can see that you can create constraints, grant privileges to users and groups, create triggers, and include pre- and post-scripts to run after the table is generated.

For example, you may want to call a script that pulls data from a production database and then imports that data into your new table after it is created using a post-execution script. Or, perhaps you want to grant certain access rights to your new table. I recommend that you experiment with these features after finishing the example in this article.

14.  Press Ctrl+S or simply close the designer by clicking to execute the script generated by Visual Studio 2005 to create the table you just designed, and click Yes as shown below:

Note that if you chose to close the designer and execute the script by clicking , you will see a different dialog box as shown below:

When you click Yes, Visual Studio 2005 runs the script that was generated by the table designer, as shown below (which you can see at any time within the designer by clicking View Script ():

If an error occurred when running the generated script, the IBM Message Output Pane window would detail that error. For example, when I first created this table, I had an error in my check constraint. I was able to quickly locate this problem using this message facility.

If the script ran successfully, you should now be able to see the EMPLOYEEBYDESIGNER table in the Server Explorer, as shown below. (You may need to refresh the Tables folder to see the new table.)

Wrapping it Up...

You can see how the table designer made creating a table (and some complex associated objects and characteristics) very simple indeed. Also, note that Visual Studio 2005 generated a script, which was then run for you automatically. This is likely the function you want when working in a development environment. In a production environment, it's more likely the case that you would deploy the script in a solution that the DBA who owns the database would deploy (which you can also do).

However you choose to finally create the table, I'm sure you'll agree that the table designer is a powerful tool that lets you bypass the novice steps of a wizard and at the same time leverage the auto-syntax generation that they provide and save your valuable time.

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 UDB and has written over one hundred magazine articles and several books about it. Paul has co-authored the books: DB2 9 New Features (available soon), 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). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.

Trademarks

IBM, DB2 and DB2 Universal Database are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks 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 registered trademark of Linus Torvalds 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.

Disclaimer

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.