by Paul C. Zikopoulos
In Part 1 of this series, I covered the richness of the IBM Explorer and its unique features that help .NET application developers write applications that run against the IBM DB2 Universal Database (DB2 UDB) family on any platform. The DB2 UDB add-ins for Microsoft Visual Studio .NET (VS.NET) also come with a rich set of schema wizards that allow you to create a number of objects, including tables, views, indexes, triggers, data adapters, stored procedures, and user-defined functions (UDFs). (Data adapters will be covered in detail in a future article.)
In this article, I will show you how easy it is to create database objects for your DB2 UDB databases with the assistance of the IBM Explorer and a few wizards. I will not cover every step, but I will highlight key features and productivity enhancements. When features are common across the various wizards, I will just cover them once and refer you back to the section where they were covered with subsequent wizards.
The DB2 Create Table wizard
You can invoke the DB2 Create Table wizard by right-clicking the Tables folder in the IBM Explorer and selecting New->Table. (Note that you can create Indexes and Triggers on tables as well – I will cover those in a bit.)
All of the database schema creation wizards can be invoked from the database tree in a similar manner.
You can specify the typical characteristics that you would expect when creating a table. For example, you can select the table name, the schema where you want it to be created, and the table space where the table will reside. Additionally, you can add a comment that describes the table you want to create.
Next, you can define the columns for the table you want to create. The wizard provides a GUI interface where you can add columns and specify characteristics for them such as default values, sequences (including the number of values to cache and the increment), and more:
One very useful feature in the DB2 Create Table wizard is the ability to import column definitions into your table. This provides a fantastic way to enforce best practices, or easily emulate columns of tables that are in production.
Using the Import button, you can browse the schema of multiple tables in your databases and import one or more column definitions into your table, as the following figure shows:
Note that the columns in the preceding figure come from two separate tables. After you click OK, the table definition looks like this:
Some of our clients maintain tables with column definitions that are used as the basis for new tables. For example, an HR table may contain all of the data definitions used for HR-based applications, and so on. This feature is a nice way to leverage that kind of methodology from within the IDE.
Subsequent pages allow you to specify referential integrity (RI) constraints to enforce key business rules and help the optimizer with more details for access plan generation. For example, you can create primary keys (PKs), unique keys (UKs), and foreign keys (FKs) – as well as defining DELETE and UPDATE rules and check constraints, as the following figure shows:
This wizard also gives you the ability to specify scripting options for the table shown in the following figure. The Specify script options for the table page gives you the ability to add script headers and trailers for pre- and post-processing work to the table creation script you are building. For example, you may have a table generated with a header that drops the table before it is created (in case it already exists), and grants all privileges on the table to PUBLIC.
The next page gives you some really cool options from a development perspective.
When you create any object with a wizard, underneath the covers a database script file is generated. Note that when you use a wizard, the Run this script on the database check box is selected. Whenever you create a database object from the IBM Explorer, this option is always selected such that the object is created as soon as you click Finish. This is likely the way you want to create objects in your development environment, which is why it is the default.
In a production environment, you will typically use scripts to create your database objects. When you create objects using a DB2 Database Project and its associated scripts, you will note that the object is not automatically created; rather, a script file is generated for you. You can use this page to specify your creation and deployment preferences.
In addition to automatically creating the database object (the default action), you can also select to deploy the script file, used to create the object, into an existing project or even create a new one. For example, you may want to create the table locally and add the script to your production project in the same step. These scripts can be checked in and out of most source control programs, such as IBM Rational Clear Case or Microsoft Visual Source Safe, without leaving the IDE.
Finally (although you could have clicked Finish the moment this button became active), you are presented with a page that summarizes the options you selected for the table you want to create. Click Show SQL to see the SQL script that will be used to create the table. This script will also be deployed into a project if you specified for this action to occur.
