Did you know that the IBM DB2 Universal Database (DB2 UDB)
product has arguably the best support for .NET in today’s database industry? In
this series of articles, I want to take you through some of the tooling productivity
benefits that a DB2 UDB developer can leverage when programming .NET
applications in the Microsoft Visual Studio.NET ( VS.NET)
integrated development environment (IDE).
In Part 1, I start with the IBM Explorer (think Server
Explorer and you’ll know what I’m talking about – only it’s much better).
Subsequent parts in this series will include:
- The DB2 Database Project
- Schema wizards.
- Information and other goodies.
- Application deployment.
Common Language Runtime (CLR) routines. Yes, you read that right:
you can create CLR-based routines in DB2 UDB (since September 2004). At the
time of writing, DB2 UDB is the only database in the world that supports CLR
Concurrent data readers to access more than one result set in the
same database connection (which can be to a DB2 UDB database server on the Linux,
UNIX, Windows, i5/OS, or z/OS (platforms) and query both of those result sets concurrently.
DB2 UDB database application blocks for rapid application
The IBM Explorer
Most .NET developers are
familiar with the Server Explorer. The promise from IBM is that the IBM
Explorer functions in a very similar manner to the Server Explorer. The IBM
Explorer has additional features, though, that provide developers with richer
capabilities that they have told IBM they wanted out of this piece of the IDE.
The IBM Explorer and Server
Explorer windows are shown below:
The IBM Explorer surfaces
connection to any DB2 UDB family member (including those for i5/OS and z/OS environments), as well as federated objects set up with IBM WebSphere Information Integrator.
Before you get started, remember: if
you want to build a .NET application with DB2 UDB, you have to use the IBM Explorer. Dragging-and-dropping table objects in the Server
Explorer window will create OLE DB connections.
So, what else is so great about
the IBM Explorer? As a baseline, it fully supports the drag-and-drop rapid
application development (RAD) features that you are accustomed to with the VS.NET Server Explorer. For
example, the following code was generated by simply dragging-and-dropping the
STAFF table onto a Visual Basic.NET (VB.NET) WinForm:
The palette for this code looks like this:
As you would expect, the Data Set
was generated by simply right-clicking the Data Adapter (which not all
databases that offer .NET integration can do).
The great things about the IBM Explorer
So, now that you are comfortable with the fact that you can
build your .NET applications with DB2 UDB in the same manner that you build
your SQL Server applications, I want to share with you some of those
productivity enhancements I alluded to earlier.
Connecting to development databases
One of the really cool features in the IBM Explorer is the
ability to discover databases and dynamically add them to the IBM Explorer tree
view without leaving the IDE. Traditionally, a database administrator (DBA)
would likely have to set a database connection on an application developer’s
workstation, or use some of the existing tooling to specify the server name of
the target database, and more.
DB2 products come with the Add DB2 Data Connection wizard, which
allows you to discover databases on your network. Of course, your DBAs
have to ensure that you have been granted access credentials to those
databases, and they can configure databases to respond to or ignore DB2 UDB discovery
requests. By clicking the Refresh button in the wizard, the local DB2
UDB client code sends out a broadcast message asking eligible DB2 UDB servers
to respond with configuration information.
From here, the application developer simply needs to select which
server and database to connect to, and let the development begin.
You can also use the Options tab to customize z/OS build
options on a per connection and per project basis.
Many developers elect to set up multiple connections to the
same database with different connection options and credentials, as shown
You can see in the preceding figure that I have set up two
different connections to the BANKDB database: namely, BANKDBDEVELOPERS and
Filtering database connections
One thing IBM heard from application developers is that when
they work with databases that have a large number of objects, the amount of
time it takes to populate the object tree can become unacceptable. For example,
consider an SAP application that can contain up to 30,000 database objects – this
task just screams coffee time.
The IBM Explorer includes a filter with granularity at the
database object layer. Using IBM Explorer, you can filter the following
objects: tables, views, stored procedures, and functions. This gives
applications developers a lot of granularity beyond a filter applied at the
database level (which you can do as well).
The figure below shows the Table folder expanded on the default DB2
UDB SAMPLE database:
Using the filter option, you can restrict the tables
returned in this folder by schema, name, or a combination of these. In the
example below, I set a filter such that the only objects in the Tables branch have
or contain the key name *STAFF*:
When this filter is applied, the Tables branch in the
database object tree looks like this:
This filter can make working across varying teams and with
large databases more productive for developers programming to DB2 UDB
Working without a database connection
When using the Server Explorer, no matter what database you
are programming to, you need to have a connection to the database to browse the
objects within them and build your applications.
You can see in the following figure that despite the fact
that the database manager isn’t started (see the highlighted text in the
figure), I was still able to drag-and-drop a table object onto my WinForm. More
on this in a bit.
The DB2 UDB add-in creates a cache of the schema (stored in
XML) that resides underneath the IBM Explorer. This not only allows application
developers to work with the database schema in offline mode, but also assists
in syntax checking without having to build your solution.
Quite simply, you do not have to be connected to the
database to build your application. If you were building an application from
the Server Explorer and the database instance was not started, depending on the
vendor’s database, you would either be blocked from using the entire IDE until
an error was returned, or you would not be able to expand the database object
Creating a database
It is pretty basic, but you can create (and drop) a database
from the IBM Explorer, as shown below:
This option creates a default DB2 UDB database with the
default table space definitions, and so on. In essence, this is the same as
entering the CREATE DATABASE TESTMEOUT command from a DB2 UDB command
prompt. While this functionality may not be ‘rich’ enough for a production
database, it is a convenient option for creating local development databases.
Asynchronous operations with minimal blocking
For most databases, the Server Explorer operates in a synchronous
manner. If you expand a server with SQL Server or DB2 UDB instances that are
not started, the entire IDE is blocked until either a login window is returned
(which can take some time when the instance isn’t started) or an error is
returned to the IDE.
Because the IBM Explorer caches the database schema, the
amount of blocking that can occur is minimized. In addition to this, certain
operations in the IBM Explorer that block the interface only block the IBM
Explorer interface and not the rest of the IDE.
Creating schema objects
There are a number of wizards that are directly accessible
from the IBM Explorer. Typically, developers will use these options to
synchronously create objects in their development databases. (Of course, you
can only perform actions that your DBA has authorized you to do.)
The following figures show the wizard that assists in
creating a DB2 UDB SQL/PL procedure:
I will discuss the schema creation wizards in the next
article in this series, but for now look at the following figure from the
Notice in the preceding figure that the DB2 UDB add-in for
VS.NET was able to flag an error in the SQL statement that this stored
procedure is based on. Users of other databases might have to wait until run
built time to surface this error. This is the schema cache underneath the IBM
Explorer that is busy at work ensuring that you remain as productive as
You also can browse data from objects in the IBM Explorer in
a native Microsoft data grid (shown in the figure below), as well as perform
INSERT, DELETE, and UPDATE operations if you have the correct privileges to
perform these operations.
From the IBM Explorer you can also execute routines that you
have created. For example, I create a stored procedure called STAFF2000, which takes
an Integer-type input variable (which represents a department number) and
returns a list of the employees that work in that department.
The following figure shows executing a stored procedure
called STAFF2000, passing it an input variable, and viewing the results in a
native Microsoft data grid:
Generating CREATE scripts
You can select the Generate Create Script option to
generate a creation script for the object you select. (You cannot do this on
some objects.) When you select this option, you are presented with the
This window allows you to add script fragments (headers and
trailers) to the script, and choose where to deploy the script. When you select
this option, it has the effect of running the db2look command, which
generates the data definition language (DDL) code to create the selected object,
as shown below:
You can then use the Insert Create Script option to
add the script to, and use it in, some other database, or check it into your
source control program, and so on.
Wrapping it all up
In this article, I showed you how friendly the IBM Explorer
is for Microsoft developers. Aside from the natural drag-and-drop code
generation experience that .NET developers who program to SQL Server are
already accustomed to, there are a host of unique features that really create a
rich development environment, no matter what DB2 UDB database you are
When you consider that most of the features I mentioned in
this article apply to any DB2 UDB database product on any platform, you should
start to get the feeling that learning about the DB2 UDB VS.NET add-ins can
truly enrich your resume, let alone your development environment. In the next
article in this series, I will talk about the schema wizards I briefly
mentioned in this article.
Are you ready to try it out for yourself? Get a free trial
copy of a DB2 UDB for Windows server at: http://www14.software.ibm.com/webapp/download/category.jsp?s=c&cat=data.
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
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: [email protected].
IBM, DB2, DB2
Universal Database, z/OS, i5/OS, and WebSphere are trademarks or
registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
Intel is a trademark or
registered trademark of Intel Corporation in the United States and other countries.
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.
product, and service names may be trademarks or service marks of others.
International Business Machines Corporation, 2005. All rights reserved.
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.