By Chip Irek
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: 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. What’s key here is that nothing remarkable or confusing is happening! 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());
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: I measured these tests against DB2 V7 and V8 on one dimension and across access methods on the other. 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.
Figure 2: Summary of functionality yielded by various providers
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. 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. 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).
Figure 3: Summary of performance results yielded by various providers.
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).
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.