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:
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.
Trademarks
IBM, Clear Case, DB2,
DB2 Universal Database, IBM, 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.
Disclaimer
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.
