Click here to Skip to main content
Click here to Skip to main content

Connect to DB2 from Microsoft .NET

By , 28 Aug 2003
Rate this:
Please Sign up or sign in to vote.

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. Smile | :)

References

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Sreejumon
Web Developer
India India
Sreejumon, Microsoft MVP for last 5 years.
 
He blogs at http://blog.sreesharp.com
 
He maintains the following sites.
 
http://www.industrial-automation-software.com
http://forum.t-mug.org
http://www.t-mug.org
http://www.sreesharp.com
 

Comments and Discussions

 
QuestionDB2 on Mainframe Pinmemberkleinkauff16-Apr-13 10:34 
Questionbest practices for exception handling Pinmemberdeostroll1-Jul-11 6:45 
Generalfrench char modmono PinmemberAjay Kale New31-Aug-10 3:11 
QuestionMainframe check Error: Catastrophic failure when connection Mainframe DB2 shadow using Neon Pinmemberilan0522-Jan-10 10:12 
QuestionIBM.Data.DB2.DB2Exception: Unable to allocate statement handle Pinmemberbart.nicolotti11-Feb-09 2:11 
QuestionMulti Table Connection PinmemberNigelAF30-Aug-07 6:14 
GeneralNetwork error while connecting to DB2/MVS from .Net Pinmemberviv_bhatt6-Jul-07 5:51 
Generalurgent PinmemberPHANINDRA.TSK4-May-07 20:13 
QuestionThe 'IBMDADB2.1' provider is not registered on the local machine??? Pinmemberparvinder sehrawat9-Oct-06 21:14 
Hi
 
I am trying to connect DB2 through .NET but i m getting following error
- "The 'IBMDADB2.1' provider is not registered on the local machine."
 
I have VS.NET 2003,How can i resolve this problem ???
 
Code--
 
Shared connectionString As String = "Provider=IBMDADB2.1;Server=xxx.xxx.xxx.xxx;Port=xxxx;User ID=myUserIDassword=myPWD;Data Source=Database name"
 

Regards
parvinder
 

GeneralWeird connection problem with OleDB PinsussChristian Blackburn8-Mar-04 13:12 
GeneralIBM IDE Add-ins PinmemberWhosit24-Dec-03 6:09 
GeneralRe: IBM IDE Add-ins Pinmemberbivkovic115-May-06 0:36 
GeneralIBM DB2 OLEDB Drivers ConnectionStrings PinmemberDeepak Kumar Vasudevan17-Sep-03 19:40 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings PinmemberTechnology_Learner7-Oct-06 5:08 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings PinmemberTechnology_Learner8-Oct-06 21:55 
QuestionRe: IBM DB2 OLEDB Drivers ConnectionStrings Pinmemberparvinder sehrawat9-Oct-06 2:59 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings Pinmemberparvinder sehrawat9-Oct-06 2:59 
GeneralGood article Pinmembergxhpainter3-Sep-03 16:06 
GeneralIBM DB2 Managed Provider Developers PinmemberKeith Platfoot2-Sep-03 18:38 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberPeter Hendrix2-Sep-03 19:50 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberKeith Platfoot3-Sep-03 4:58 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberJoe Crump21-Sep-04 3:32 
GeneralLinks PinsussAnonymous31-Aug-03 11:20 
GeneralRe: Links PinmemberSreejumon31-Aug-03 12:04 
GeneralRe: Links PinsussAnonymous21-Feb-05 4:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 29 Aug 2003
Article Copyright 2003 by Sreejumon
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid