Building Tables with the DB2 Designer and Visual Studio 2005 - Page 2
September 12, 2006
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), HIREDATE DATE, JOB CHARACTER(8), EDLEVEL SMALLINT NOT NULL, SEX CHARACTER(1), BIRTHDATE DATE, 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: