. NET development DB2 Database related application comparison

Comparison | program | data | Database

Brief Introduction

In the view of. NET application developers, using DB2 is no different from using any other relational database. You can see countless examples of using SQL Server and Oracle products to perform database tasks, but for use include. NET, Microsoft technology access to DB2 database documents is far less than that.

Currently, developers can use different access technologies to programmatically. NET client is connected to the DB2, the code is fundamentally the same, but there are still some interesting limitations 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, and all data access is done through the SQL interface. We can only run the DB2 univeral database (UDB) as a DB server, which requires no other product, but we need some other products for remote clients.

(Figure pict01)

The way we connect to DB2 determines the tasks we can accomplish. There are three kinds of technologies that can be used from. NET connection to DB2:

1. Use OLE DB. NET Managed Provider. If you are running DB2 V7 or lower versions and COM objects are callers, Microsoft and IBM two companies recommend using OLE DB data providers to access most DB2 data. In the. NET framework, OLE DB providers are represented through COM interop and use the drivers that are available for ADO development.

2. Use ODBC. NET Managed Provider. This approach provides access to the local ODBC driver, and the same way that OLE DB. NET Data Provider provides access to local OLE DB providers. The ODBC. NET Data provider is an add-on component of the. NET Framework that can be used in conjunction with all compatible ODBC drivers.

3. Using IBM DB2. NET Provider (Beta): This provides support for connecting Ado.net to DB2 V8.1. The DB2. NET Data provider is an inline attachment to the visual Studio. NET Framework that can access DB database servers running on different hardware and operating system platforms. At present, it is still in beta testing phase, but IBM will soon release the formal products. DB2. Net Data Provider is able to connect applications developed using Ado.net to the following DB2 database server:

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

• DB2 Universal Database running on a later operating system with Z/OS, os/390 V6.1, and a newer version of the DB2 Connect V8.1 component.

Different connection strings show syntax differences in different situations:

OLE DB 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

Comparison of code-level access

When developing DB2 data access code, most operations are largely the same as accessing other databases. Here's a comparison of some of the Code for Connection technology:

In each of the following snippets, we'll show some tips for database access and data management, and ask readers to note which steps are the same for each technique. We first create a connection object and then create a command object. In this simple example, we only access a scalar value in the specified row and column in this particular database table. Finally, we execute the ExecuteScalar () method of the command, returning the integer.

Using OLE DB Technology

Private OleDbConnection cn = new OleDbConnection (connectionString); OleDbCommand cmd = new OleDbCommand ("Select COUNT (*) from STAFF", CN); int rc = Convert.ToInt32 (cmd. ExecuteScalar ());

Using ODBC

Private OdbcConnection cn = new OdbcConnection (connectionString);

OdbcCommand cmd = new OdbcCommand ("Select COUNT (*) from STAFF", CN);

int rc = Convert.ToInt32 (cmd. ExecuteScalar ());

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

Comparison of functions

Depending on the access technology selected and the database version used, we can use different features. In addition, depending on what needs to be done, we also need to select the connection strategy.

To enable the reader to better choose the connection technology, I have sorted out a roadmap that can be used in decision making. In the roadmap, I've identified a number of features that are common to most database-centric software development projects.

In the chart below, I've sorted out my own experience. I evaluated each of the three access technologies based on whether the specific database tasks were allowed to be completed, and the database tasks for the assessment were:

Pass-thru Sql━━ Does this provider support delivery through dynamically developed SQL statements?

• Simple stored procedures ━━ does this provider support the invocation of basic stored procedures including return results?

in, out, inout parameter ━━ does this provider support invoking stored procedures and passing variable data with parameters?

• Date and currency ━━ does this provider support data types that are provided by specific vendors that can cause problems?

Lobs━━ does this provider support processing of large object data types?

(Figure pict02)

Depending on the connectivity technology used, we can accomplish different types of tasks, because each provider has the work that it can accomplish. There are some problems that we should pay attention to.

For example, ODBC does not support DB2 stored procedures. Therefore, if you develop an application that relies heavily on stored procedures, we cannot consider ODBC when choosing the access technology, but should consider OLE DB or managed Provider. OLE DB does not support LOB data types, and if you want to access LOB-type data (such as JPEG files), we can use ODBC or managed Provider. Currently, Managed provider cannot be run on DB2 V7 or previous versions unless DB2 Connect DB2 is running between the application and the host-based V8 system.

Interestingly, when using the parameters of stored procedures, DB2 V7 and OLE DB have a very famous bug. We can return the data from the stored procedure, but on the OLEDB+DB2 V7 platform, we cannot pass parameters between the application and the stored procedure.

Performance comparison

To measure the performance of the provider and provide guidance to the reader, we will define an effective test script and test the key performance of the various providers. To compare the performance of the various connectivity technologies, we developed a few simple Web pages, a Web page for performing exactly the same function of the test, which will enable us to analyze the different connection technology output of the control data.

(Figure pict03)

If strictly from a performance point of view, we find that Managed provider has the highest performance when accessing the DB2 database. In this simple test, there are two important numbers to be concerned about: Requests per second (RPS) can make us feel the size of the visit, OLE DB and ODBC are basically similar in this respect, but managed provider performance is better (about 10% higher) The last byte of Time (TTLB) is the response time seen from the end-user's perspective, in which Managed provider performance is again about 10% higher than OLE DB and ODBC.

Most notably, although the functionality provided is different, the performance of OLE DB and ODBC is essentially the same under load. IBM's managed provider has the best performance and provides the most reliable functionality when accessing DB2 V8. However, without DB2 Connect as an intermediate, it does not support DB2 V7 and lower versions.

Conclusion

For application developers, using DB2 as the backend is as simple as using other relational databases accessed through ADO or ado.net. Ado. NET completes all the abstract work for us. While there are architectural considerations when choosing DB2 or other databases, as developers, our work should be predictable and manageable.