by Paul C. Zikopoulos, BA, MBA
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 routines.
- 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 development
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 SQL/PL wizard:
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 possible.
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 following window:
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 programming to.
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
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: firstname.lastname@example.org.
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.
Microsoft and 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.
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.