Building Tables with the DB2 Designer and Visual Studio 2005
September 12, 2006
In a previous series of articles I showed you all the great integration features between the IBM DB2 Universal Database for Linux, UNIX, and Windows Version 8.x (DB2 UDB) product and the Microsoft Visual Studio.NET 2003 integrated development environment (IDE).
In early June 2006, IBM announced the next release of the DB2 product, DB2 9 (formerly known as DB2 Viper). Part of this announcement includes the support for Microsoft Visual Studio 2005 and its accompanying ADO.NET 2.0 driver.
As I've been writing about the DB2 integration into Visual Studio 2005, I've discussed in other articles:
In this article, I want to show you a new tool that is unique to DB2 9 in the Visual Studio 2005 integrated development environment (IDE) called a designer. Specifically, I'll show you how to create a table using the table designer.
Note: This article covers a simple example of cloning a table and adding some new features to it. I did this for simplicity. The features outlined in this article are exceptional for creating new tables, or tables based on columns that reside in other repositories, and more. If you just want to clone a table, you can use function within Visual Studio 2005 or the DB2 CLP to perform that task.
What Are Designers?
Designers are a very exciting DB2 integration feature in Visual Studio 2005. Quite simply, designers are schema productivity features that balance the simplification offered by wizards (which are still offered in Visual Studio 2005 for DB2 schema development) and the quick task completion time associated with a notebook-style interface that doesn't have the 'Click Next' methodology applied to it.
In Visual Studio 2005, the wizards that you may be familiar with from the DB2 integration with Visual Studio .NET 2003 continue to be available from the Server Explorer. Designers can also be launched from the Server Explorer view to create and alter new database objects.
These designers provide an aggregated view of an object and all its related objects and typical associated actions. Each designer has one or more views that expose a part of the functionality available to the designer.
Designers are available to build the following DB2 objects: tables, views, scripts, data views, and stored procedures, as shown in the Visual Studio 2005 toolbar list:
You can click any of the designers in this list to have its respective toolbar appear in the Visual Studio 2005 menu bar. A toolbar for each designer is provided, and you can use the icons to switch among the various views of the designer.
Here are the various toolbars for the DB2 designers in Visual Studio 2005:
You can use any of these toolbars to navigate among the different views of a designer. Each designer view typically exposes a different set of functionality for that designer. Closing a designer closes all views of that designer.
When a designer is not opened, or selected, the respective toolbar will be inactive. The following screen capture shows what the designer toolbar looks like when the table designer is active:
As you can see, other designers are grayed and therefore inactive.
There are some common actions that you can perform from any designer's toolbar and a uniform look-and-feel. For example, the Show Script () and Privileges View () features are available on all the designers. In addition to this commonality, designers have seamless integration into the File -> Save action in Visual Studio 2005, so when you close a designer, the script you're generating behind the designer is automatically run (though you can choose to not have Visual Studio 2005 perform this action when you close a designer).
The table designer toolbar gives you multiple features that are available at the click of a button. You can work with the column definitions in a table, work with the table's business rules via primary keys, foreign keys, and check constraints, define triggers on a table, grant privileges, and generate the table's creation script (along with defining script trailers and headers).
An example of the table designer's toolbar is shown below: