Paul Zikopoulos discusses a new tool that is unique to DB2 9 in the Visual Studio 2005 integrated development environment (IDE) called a designer. Specifically, he examines how to create a table using the table designer.
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:
Visual Studio 2005 Designers and Visual Studio 2003/2005 Wizards
In Visual Studio.NET 2003, you could use wizards to generate the script required to build a database object. However, these wizards (which are still available with the DB2 integration with Visual Studio 2005) limit users with respect to the flexibility and capability of what they can perform with them.
For example, there is no wizard to alter an object, no way to manage privileges on the object (other than to GRANT ALL TO PUBLIC), and no customization capabilities. Furthermore, wizards are modal: once you start a wizard to create an object, you can't leave it and perform other actions so long as that wizard is still open. By contrast, designers are non-modal, which is more in step with what a SQL Server 2005 developer would expect from Visual Studio 2005.
An example of creating a table using a wizard in Visual Studio.NET 2003, which is also available in Visual Studio 2005 (it's the top part of the figure) and a designer, which is only available in Visual Studio 2005, (the bottom part of the figure) is shown below:
The fact that the DB2 9 Visual Studio integration still offers wizards, yet has an exceptional non-modal option is really great for developers. Depending on the amount of assistance you need, you have granular control over assistance for object creation.
Creating a Table with the DB2 Table Designer
In this section, I want to show you how to create a table called EMPLOYEEBYDESIGNER that is based on the EMPLOYEE table in the SAMPLE database. (You can create this by entering the db2sampl command from your operating system's command prompt.)
The EMPLOYEE table in the SAMPLE database doesn't have any referential integrity (RI) set up, so we'll add an RI constraint using the table designer. This is a simple example, but it will help illustrate the powerful capabilities that designers give developers. In addition to this, I'll highlight other features and functions that designers offer for table creation.
In the end, you'll see a fast way in which to generate the following SQL statements (not to mention not having to memorize all the syntax):
CREATE TABLE PAULZ.EMPLOYEEBYDESIGNER (
EMPNO CHARACTER (6) NOT NULL,
FIRSTNME VARCHAR (12) NOT NULL,
MIDINIT CHARACTER (1) NOT NULL,
LASTNAME VARCHAR (15) NOT NULL,
WORKDEPT CHARACTER (3),
PHONENO CHARACTER (4),
EDLEVEL SMALLINT NOT NULL,
SALARY DECIMAL (9,2),
BONUS DECIMAL (9,2),
COMM DECIMAL (9,2),
CONSTRAINT CN07050604254732 PRIMARY KEY (EMPNO));
CREATE INDEX EMPNOINDEX ON PAULZ.EMPLOYEEBYDESIGNER(EMPNO DESC)
CLUSTER PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS;
GRANT ALTER, DELETE, INDEX, REFERENCES, SELECT,
UPDATE ON TABLE PAULZ.EMPLOYEEBYDESIGNER TO USER PAULZ;
GRANT INSERT ON TABLE PAULZ.EMPLOYEEBYDESIGNER TO USER PAULZ WITH GRANT OPTION;
COMMENT ON TABLE PAULZ.EMPLOYEEBYDESIGNER IS
'This is the EMPLOYEE table in SAMPLE database';
To create the table outlined in the previous DDL, perform the following steps:
1. Start the table designer by selecting Tables, right-click, and select Add New Table with Designer:
(Note that this is how you access the corresponding wizard if you need a little more hand-holding.)
2. The table designer opens. Enter the name for the table in the Table name field, the schema in which you want this table created in the Table schema field, and optionally use the Comment and Table space fields to describe the table and identify the table space where the EMPLOYEEBYDESIGNER table will reside, as shown below:
Note: You can see in the previous figure that the table designer has three sections. (Other designers may have fewer or more sections.) I've collapsed the Columns and Column Properties sections of this designer for simplicity; we'll expand them later in this article.
3. Expand the Columns section (and optionally collapse the Table Definition section) so the Visual Studio 2005 integrated development environment (IDE) looks like this:
I've added Hover Help tags to identify the functions of the controls on the right. You can use these controls to work with the columns in your table.
For example, the first column in the EMPLOYEE table is the EMPNO column. This column is a CHARACTER (CHAR)-based column whose length is 6 characters. There are many ways to get this detail; one is shown below:
When you click Add column (), you can create a column like this for your table, as shown below:
4. Click Import Columns (). The Import dialog box opens:
5. Select the EMPLOYEE table and click to add all the columns in the EMPLOYEE table, and click OK:
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: firstname.lastname@example.org.
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.
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.