| Introduction Working with DB2, from a .NET application developer's perspective, is just like working with any other relational database. One can find endless examples of how to perform databases tasks (from the mundane to the interesting) for Microsoft SQL Server and Oracle, but there is not as much similar documentation for accessing DB2 from Microsoft technologies, including .NET. A developer currently has different access techniques to programmatically connect .NET programming clients to DB2. For each of these the code is basically the same thanks to the providers involved, but some interesting limitations exist that you should consider. DB2 Architecture Overview The basic elements of the DB2 database engine are database objects, system catalogs, directories, and configuration files. All data access takes place through the SQL interface. You can run DB2 Univeral Database (UDB) as just the database server, with no additional products required. But for remote clients, you'll need additional products. The server products of DB2 UDB provide support for communication to the database server using protocols such as TCP/IP, SNA, or IPX/SPX. This, then, allows access from remote clients running the Administration Client, Runtime Client, or Application Development Client. The Application Development Client component is a collection of tools that are designed for database application developers. It includes libraries, header files, documented APIs, and sample programs. You should focus on the client that accesses the database server over the network. By using DB2 Connect, your .NET applications can access host-based DB2 (DB2 for AS/400, DB2 for OS/390, and DB2 for VSE and VM database servers). 
Figure 1: DB2 Component Architecture
Options for Connecting to DB2 from .NET How you connect to DB2 determines what you can do. There are three techniques to connect to DB2 from .NET: - Using an OleDb .NET Managed Provider Both Microsoft and IBM recommend utilizing an OleDb data provider for accessing most DB2 data when either DB2 V7 or lower is used or when a COM object is the caller. Within the .NET framework, OleDb providers are exposed via COM InterOp and essentially use the same drivers available for ADO development.
- Using an ODBC .NET Managed Provider This provides access to native ODBC drivers the same way the OLEDb .NET Data Provider provides access to native OLEDb providers. The ODBC .NET Data Provider is an add-on component to the .NET Framework. The ODBC .NET Data Provider is intended to work with all compliant ODBC drivers.
- Using the IBM DB2 .NET Provider (Beta) This provides ADO.NET connectivity to DB2 V8.1. The DB2 .NET Data Provider is an add-in component to the Visual Studio .NET Framework. This provider accesses IBM DB2 database servers running on variety of hardware and operating system platforms. At the time of this writing, the provider is still beta but is expected to be in production soon. The DB2 .Net Data Provider provides connectivity from applications written using ADO.NET to the following DB2 family of servers:
- DB2 Universal Database Version 8.1 running on Windows, UNIX and Linux
- DB2 Universal Database for z/OS and OS/390 V6.1 and later using DB2 Connect V8.1
The different connection strings highlight the syntactical difference between scenarios. The OleDb connection string ...
Provider=IBMDADB2.1;User ID=db2admin;Password=db2admin;Data Source=SAMPLE |
The ODBC connection string ...
DSN=DB2V8;UID=db2admin;PWD=db2admin |
The IBM Managed Provider connection string ...
Database=SAMPLE;User ID=db2admin;Password=db2admin;Server=IREK |
Comparing Code Level Access When you work with DB2 data access code, most operations are largely the same as you would expect as compared to working with other databases. Here I compare some code across connection techniques. In each of the following code snippets, we are trying to show some of the mechanics of accessing the database and managing data. Notice how the steps are the same for each technique. You start by creating the connection object, then creating a command object. In this simple example, I'm just retrieving a scalar value of the count of rows in this particular table. Finally, we execute the command's ExecuteScalar() method to return the integer. With Ole Db...
private OleDbConnection cn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM STAFF", cn);
int rc = Convert.ToInt32(cmd.ExecuteScalar()); |
With ODBC...
private OdbcConnection cn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM STAFF", cn);
int rc = Convert.ToInt32(cmd.ExecuteScalar()); |
With IBM's Managed Provider...
private DB2Connection cn = new DB2Connection(connectionString);
DB2Command cmd = new DB2Command("SELECT COUNT(*) FROM STAFF", cn);
int rc = Convert.ToInt32(cmd.ExecuteScalar()); |
What's key here is that nothing remarkable or confusing is happening! Functionality Comparison Based on which access technique you choose and which version database you access, you will have certain functionality open to you. You need to choose a connection strategy based on what work you need to accomplish. To measure this and provide some guidance, I put together a quick roadmap to help in the decision framework. I isolated a few key functional areas I felt were common to most database centric software development projects. In the chart below I summarize my findings. I measured each of the three access techniques against whether I could perform some particular database task with the provider. The database tasks I measured are: - Pass-thru SQL - Does the provider support working with simple pass-through dynamically built SQL statements?
- Simple Stored Procedures - Does the provider support calling basic stored procedures, including returning resultsets?
- In, Out, and InOut parameters - Does the provider support calling stored procedures and utilizing parameters to pass variable data?
- Dates and Currency - Does the provider support vendor-specific data types that might pose a problem?
- LOBs - Does the provider support working with large object data types?
I measured these tests against DB2 V7 and V8 on one dimension and across access methods on the other. 
Figure 2: Summary of functionality yielded by various providers
You can perform different kinds of work based on how you connect because each provider has a set of things it can accomplish. There are some items worth careful noting! For example, ODBC doesn't support DB2 stored procedures. So if you are building an application heavily dependant on stored procedures, you need to eliminate ODBC as your access method and consider OleDb or the managed provider. OleDb doesn't support large LOB data types with DB2. So, if you want to retrieve LOBs (for example JPEGs) you can use ODBC, or the beta managed provider against V8. Currently, the managed provider doesn't work with DB2 V7 or earlier at all, unless you have DB2 Connect V8 running between the application and a host-based DB2, like you would find on an OS/400 or an S/390. Interestingly, DB2 V7 and OleDb have a well-known bug working with stored procedure parameters. You can return data from stored procedures, but you cannot pass parameters to or from stored procedures under OleDb with DB2 V7. Performance Comparison To measure the providers' performance and offer guidance, I set out to define an effective test script and measure some key metrics across providers. For comparing performance I built some very simple Web pages, one for each test that exercised the exact same functionality. That gives us the control data to analyze the throughput under different connection techniques. 
Figure 3: Summary of performance results yielded by various providers.
When assessed strictly from a performance perspective, you can see that the managed provider yields much better results accessing DB2 data. This is to be expected, but nonetheless it is helpful to see the statistics represented.
There are two significant numbers worth monitoring in a test this simple. Requests per second (RPS) gives you a sense of how well a scenario scales. OleDb and ODBC both perform roughly the same under stress, but naturally the managed provider performs better (in fact about 10% better). Time-to-last-byte (TTLB) gives you a sense of how responsive the scenario is from an end-user's perspective. Here again, the managed provider performs better (about 10%) than OleDb or ODBC. So what is most notable is that the OleDb and ODBC scenarios provide roughly the same performance under load, while providing different functionality. IBM's managed provider provides notably better performance, and the most reliable functionality for addressing DB2 V8. But again, it does not currently support DB2 V7 and lower (without the DB2 Connect intermediary). Conclusion For application developers, working with DB2 as a backend is just as easy as working with any other RDBMS accessed via ADO or ADO.NET. ADO.NET does all the abstraction for you, such that your code looks and behaves similarly across databases. While there are architectural considerations when choosing between DB2 and other databases, as a developer your work should be predictable and manageable. About the Author Chip Irek is an Architect with IBM Global Services. He works in a group called Enterprise Services for Microsoft Technologies, providing .NET services to IBM customers. Chip has worked on several projects leveraging .NET and DB2. With nearly 15 years experience, he is an MCSD, an IBM certified Architect, and is pursuing his .NET certification. He can be reached at irek@us.ibm.com.
|
