Connect to DB2 from Microsoft .NET

Introduction

Most of the retail applications are still running in legacy systems like AS/400, Mainframe etc. If you are planning to migrate the entire application (or any modules) to Microsoft .NET, migration of the existing data will be the bottleneck (since there can be terra bytes of data, synchronization issues, data type mismatch etc). So your final choice will be to use the existing database. Let us consider different options to connect the Microsoft .NET and DB2 database resides in AS/400 system.

Managed providers

Managed providers works with in the boundaries of Common Language Runtime. There are no COM interoperability, obviously no worry about serialization and marshaling. Managed providers expose provider specific behavior through methods, properties and interfaces, which are much efficient than OLEDB providers. Hence the final result will be better performance. Currently there are matured managed providers for SQL Server and Oracle database (SqlClient and OracleClient). We are still waiting for System.Data.Db2Client namespace. So current option is to use other third party managed providers available in market. Some of them are still under development.

IBM DB2 UDB 8.1.2

This version provides a managed data provider for DB2 that is developed and supported by IBM. No third party drivers or bridge solutions are needed. A collection of add-ins for the Visual Studio .NET IDE that simplify creation of the applications that use ADO.NET API. These add-ins also extend the power of the Visual Studio .NET to developing DB2 server-side objects such as stored procedures and user-defined functions. Using VS IDE integration we will be able to visually explore the various DB2 resources, similar to SQL server. The .NET support is also shipped with DB2 Connect 8.1.2, providing support for DB2 UDB for z/OS and OS/390 V7. Here is the code snippet using this provider.

DB2Connection cn = new DB2Connection(
  "Database=TEST;UserID=db2admin; Password=password;Server=IS500");
DB2Command cmd = new DB2Command("SELECT COUNT(*) FROM EMP01", cn);
int count = Convert.ToInt32(cmd.ExecuteScalar());

Microsoft Host Integration Server 2004

One of the main features of the Host Integration Server 2004 is the managed provider for legacy database, which obviously include the DB2 provider. The beta version is currently available. We can programmatically invoke legacy Customer Information Control System (CICS) and Information Management System (IMS) programs, which are essential to mainframe transaction processing. But Visual Studio .NET IDE integration is not available.

DataDirect Connect for .NET, Edition 2.0

DataDirect’s "DB2 Wire Protocol" provider can connect to DB2 UDB for z/OS and OS/390 v7.1 and DB2 UDB for AS/400 V4R5, V5R1, and V5R2. At present this will not support distributed transactions.

Mono.Data.DB2Client

Mono project is also creating IBM DB2 Data Provider. It requires the Call Level Interface to IBM DB2 shared library. This is db2cli.dll on Windows. The IBM DB2 CLI API is very similar to the ODBC API. This provider is not fully functional. Compiles on Windows and Linux.

Unmanaged providers

As mentioned in the previous diagram, we have two more options to connect to DB2 database, using ODBC and OLEDB providers. Obviously there will be an extra layer and interoperability that will definitely affect the performance. There are different unmanaged providers are available. Let us consider a few of them.

The OLE DB.NET Data provider to access the database on an iSeries server can use the iSeries OLE DB provider. To take advantage of the .NET support, iSeries Access V5R2 with ServicePack 2 or higher is needed on the Windows system. For maximum stability, you should also install the latest database group PTF (SF99501 for V5R1 or SF99502 for V5R2) on the iSeries server.

OleDbConnection cn = new OleDbConnection(
  "Provider=IBMDA400.1;Data Source=TEST;User ID=db2user;" + 
  "Password=db2user;Default Collection =SAMPLEDB"); 

Similarly, if we are using the ODBC driver, then we have to set the DSN name in the connection string. Specifically, the DSN property is used to point to the target iSeries data source. The DSN must be previously registered with the ODBC Administration utility.

OdbcConnection cn = new OdbcConnection(
  "DSN=TestODBC; UID=db2user; PWD=db2user;DBQ=SAMPLEDB");

If you are using Microsoft OLEDB provider, then connection string will be as follows.

TCP/IP

"Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection =MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"

APPC

"Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias =MyRemote;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;UserID=MyUser;Password=MyPW"

Conclusion

The final selection depends on so many factors (support from vender, cost, scalability, stability etc). Hope you can make the right selection by keeping performance in mind. :)

References

• http://www7b.software.ibm.com/dmdd/downloads/dotnetbeta/ (UDB 8.1.2)
• http://www7b.software.ibm.com/dmdd/library/techarticle/
  0304alazzawe1/0304alazzawe1.html (UDB 8.1.2)
• http://www.ddtek.com/products/dotnet/dotnetrelhigh.asp (DataDirect)
• http://www.go-mono.com/ibmdb2.html (Mono)