Comparison of developing DB2 applications with. net

Introduction

In the view of. NET application software developers, using DB2 is no different from using any other relational database. There are countless examples of using SQL Server and Oracle products to execute database tasks, but there are far fewer documents on using Microsoft technologies including. Net to access DB2 databases.

Currently, developers can use different access technologies to connect the. NET client to DB2 through programming.CodeIt is basically the same, but there are still some interesting restrictions that we need to consider.

DB2 Architecture Overview

The basic elements of the DB2 database engine are database objects, System directories, directories, and configuration files. All data is accessed through the SQL interface. We can only run DB2 Univeral database (UDB) as a database server. This does not require other products, but we also need other products for remote clients.

(Figure pict01)

The connection to DB2 determines what we can do. There are three technologies available to connect to DB2 from. Net:

1. Use oledb. Net managed provider. If you are running DB2 V7 or earlier versions and the COM object is called, both Microsoft and IBM recommend that you use the oledb data provider to access most DB2 data. In the. NET Framework, oledb providers are expressed through COM InterOP and use the drivers that can be used for ADO development.Program.

2. Use ODBC. Net managed provider. This method provides access to the local ODBC driver, which is the same as oledb. NET data provider provides access to the local oledb provider. ODBC. NET data provider is an additional component of the. NET Framework. It can be used with all compatible ODBC drivers.

3. Use IBM DB2. net provider (Beta): This method provides support for connecting ADO. Net to DB2 v8.1. DB2. NET data provider is an internal plug-in Attachment of Visual Studio. NET Framework. It can access DB • database servers running on different hardware and operating system platforms. It is still in beta testing, but IBM will soon release its official product. DB2. NET data provider can connect applications developed using ADO. net to the following DB2 database server:

• DB2 Universal Database Version 8.1 running on Windows, UNIX, and Linux platforms.

• DB2 Universal Database running on Z/OS, OS/390 v6.1, and a later version of the DB2 connect v8.1 component.

Different connection strings show syntax differences in different situations:

Oledb connection string: provider = ibmdadb2.1; user id = db2admin; Password = db2admin; Data Source = sample

ODBC connection string: DSN = db2v8; uid = db2admin; Pwd = db2admin

BM managed provider connection string: Database = sample; user id = db2admin; Password = db2admin; server = irek

 

 

Code-level access comparison

When developing the DB2 data access code, most operations are largely the same as accessing other databases. The following code compares the connection technology:

In each of the following code snippets, we will show some database access and data management skills. Please note which steps are the same for each technology. Create a connection object and a command object. In this simple example, we only access a scalar value in the specified row and column in the specific database table. Finally, execute the executescalar () method of this command and return this integer.

Use ole db Technology

Private oledbconnection Cn = new oledbconnection (connectionstring); oledbcommand cmd = new oledbcommand ("select count (*) from staff", CN); int rc = convert. toint32 (CMD. executescalar ());

Use ODBC

Private odbcconnection Cn = new odbcconnection (connectionstring); odbccommand cmd = new odbccommand ("select count (*) from staff", CN); int rc = convert. toint32 (CMD. executescalar ());

Use 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 ());

Function Comparison

Based on the selected access technology and database version, we can use different features. In addition, we also need to select a connection policy as needed.

In order to allow readers to better choose connection technologies, I have developed a roadmap that can be used in decision making. In the roadmap, I found some common features for most database-centric software development projects.

In the following chart, I sorted out my experiences. I evaluated each of the three access technologies based on whether specific database tasks are allowed to be completed. The evaluation database tasks include:

• Does the pass-thru SQL statement handler provider support transmission through dynamically developed SQL statements?

• Does a simple Stored Procedure zookeeper support calling basic stored procedures, including returned results?

• Does the In, out, And inout parameter transfer allow the provider to call the stored procedure and transmit variable data using parameters?

• Does the provider of the date and currency pair support data types provided by specific vendors that may cause problems?

• Does the lobs metadata handler provider support processing big object data types?

(Figure pict02)

Based on the connection technology, we can complete different types of tasks, because each provider has its own work. Some problems are worth noting.

For example, ODBC does not support DB2 stored procedures. Therefore, if the developed application is very dependent on the stored procedure, we should consider oledb or managed provider instead of ODBC when selecting access technology. Oledb does not support the lob data type. To access lob data (such as JPEG files), we can use ODBC or managed provider. Currently, managed provider cannot run on DB2 V7 or earlier versions unless DB2 connect V8 is running between the application and the host-based DB2 system.

Interestingly, DB2 V7 and oledb have a very famous bug when using stored procedure parameters. We can return data from stored procedures, but on the oledb + DB2 V7 platform, we cannot pass parameters between applications and stored procedures. Performance Comparison

To measure the provider's performance and provide guidance to readers, we will define an effective test script and test the critical performance of various providers. To compare the performance of various connection technologies, we have developed some simple web pages. A web page is designed to perform tests with identical functions, this will allow us to master the control data output under different connection technologies.

(Figure pict03)

From a strict performance perspective, we find that the managed provider has the highest performance when accessing the DB2 database. In this simple test, there are two important figures worth noting: the number of requests per second (RPS) can make us feel the size of the access, oledb and ODBC are similar in this aspect, but the performance of the managed provider is better (about 10%); the time when the last byte is received (TTLB) from the end user's perspective, the response time is about 10% higher than oledb and ODBC.

It is worth noting that, although different functions are provided, the performance of oledb and ODBC is basically the same under load conditions. The managed provider of IBM has the best performance and provides the most reliable features for accessing DB2 V8. However, without DB2 connect as an intermediate, DB2 V7 and earlier versions are not supported.

Conclusion

For application software developers, using DB2 as the backend is as simple as using other relational databases accessed through ADO or ADO. net. Ado. Net has completed all the abstract work for us. When selecting DB2 or other databases, despite the architectural considerations, our work as a developer should be predictable and manageable.

 

This article from 51cto.com http://www.51cto.com/html/2006/0302/21945.htm thank you!