The Schema Creation Wizards

Wednesday Jun 22nd 2005 by DatabaseJournal.com Staff

Part 1 of this series covered the features of the IBM Explorer that help .NET application developers write applications that run against the DB2 UDB family on any platform. Part 2 discusses how easy it is to create database objects for DB2 UDB databases with the assistance of IBM Explorer and a few wizards.

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.

The DB2 Create View wizard

The DB2 Create View wizard starts with essentially the same two pages that the DB2 Create Table wizard starts with: introducing the wizard and giving you the option to name the view, specify the schema, and add a comment. (These pages also allow you to specify an INSERT or UPDATE row conformance check for dynamic views.)

Now you are ready to define the SQL statement that forms the view. The DB2 UDB add-ins for VS.NET provide a specialized DB2 UDB SQL editor for writing DB2 UDB SQL statements:

Click for larger image

Notice that when I typed in the SELECT * FROM PAULZ statement that forms the basis of the view, and specified a schema that exists in the database, followed by a period (.), the Intellisense feature of VS.NET was invoked. The VS.NET add-in for DB2 UDB enhances the VS.NET Intellisense feature to be "DB2 UDB-aware."

In the preceding figure, you can see that I am building a view on the STAFF table. In addition to this, note the syntax colorization of the keywords. The "DB2 UDB-aware" Intellisense feature is provided for all wizards that give you the option to enter SQL (for example, the DB2 SQL/PL Stored Procedure wizard also includes this feature), and the DB2 UDB SQL editor (which I will cover in the next article in this series). This greatly helps developers navigate their schema.

In addition to this, note the presence of the Validate SQL button. If you click this button, the SQL statements you enter are validated (before build or run time) and you can find out if you have mistyped a statement or selected an object that does not exist in the database. Think about the productivity enhancements of finding errors before build or run time - this neat little feature can save you a lot of time!

If you recall from Part 1 in this series, this feature is possible because the DB2 UDB add-in creates a cache of the schema underneath the IBM Explorer window; VS.NET leverages this cache to perform syntax checking, schema validation, enable offline operations, and more. The remaining options in this wizard are the same as in the DB2 Create Table wizard.

The DB2 SQL Procedure wizard

From the IBM Explorer, you can also create DB2 UDB stored procedures: both SQL/PL and common language runtime (CLR) procedures. I will cover CLR procedures in a future article in this series since it involves the creation of a class file and its subsequent compilation into an assembly.

The DB2 Create SQL Procedure wizard starts like the DB2 Create View wizard in that you can specify options such as the schema where the procedure should be created, the schema name, and more. There is a special window for you to enter the routine's SQL statement - it is hooked up to the DB2 UDB-aware Intellisense feature and the SQL validation engine too, as you saw in the DB2 Create View wizard.

The DB2 SQL Procedure wizard provides developers with the ability to browse parameters for the stored procedure they are creating (shown next) in the same manner as developers who browse columns when creating tables:

This helps minimize data type matching errors and promotes more productivity as developers can simply 'discover' the appropriate data type for their variables.

The remaining pages in this wizard are the same as those I have previously covered with the other wizards.

The DB2 SQL Function wizard

The DB2 SQL Function wizard is very similar to the DB2 SQL Procedure wizard and lets you create both scalar and table functions. There are some pages that are specific for functions. For example, you have to specify the return type of the value for a scalar function:

This wizard is smart enough to take a 'best guess' from the SQL statement it parses to suggest the return type required for the function you are creating. The remaining pages in this wizard are similar to those in the DB2 SQL Procedure wizard.

The DB2 Create Index wizard

The DB2 Create Index wizard lets you quickly create RID-based indexes on your tables. DB2 UDB also supports other types of indexes such as dynamic bitmap indexes (which are created automatically when needed), grid indexes, and block indexes.

You can see in the following figure that you can specify the schema, the name of the index, the table space in which it should reside, a comment that describes the index, whether the index will be used to enforce unique rows in the data, and the table on which you want to create the index:

This wizard also lets you select the column on which you want the index keys to be created, and the ordering of the RIDs for the index (ascending or descending):

The remaining pages in this wizard are the same as those you have seen in the other wizards.

The DB2 Create Trigger wizard

The DB2 Create Trigger wizard lets you create BEFORE, AFTER, and INSTEAD OF triggers. This wizard has all kinds of pages that accelerate the time it takes to build DB2 UDB triggers.

The remaining details in this wizard are part of your homework assignment! I want you to download a free copy of DB2 UDB (more on that in a bit) and try it out for yourself.

The DB2 Data Adapter wizard

As previously mentioned, I will cover data adapters in a follow-on article, but for completeness, I wanted to at least mention them here. Data adapters in DB2 UDB can be used to accelerate the time it takes to develop applications, enforce best practices, and even expose your routines as IIS Web methods or DB2 UDB Web services with a simple click of a button. Sounds interesting doesn't it. It is - but you will have to wait for those details.

Wrapping it all up

In this article, I hope you have seen that the DB2 UDB add-in for VS.NET goes beyond simple explorer features. It also includes a set of rich wizards that truly make working with a DB2 UDB environment transparent for .NET developers. These wizards are yet another unique and rich feature that assists .NET developers in DB2 UDB environments.

Are you ready to try it out for yourself? Get a free trial copy of a DB2 UDB for Windows server at: www.ibm.com/software/data/db2/udb/support/downloadv8.html. You can also get a free copy of DB2 UDB Personal Edition for development purposes at: http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=db2udbpde.

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 nine years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.


IBM, Clear Case, DB2, DB2 Universal Database, Rational, and z/OS 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 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, 2005. 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.

Mobile Site | Full Site