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

Connect to DB2 from Microsoft .NET

, 28 Aug 2003
Rate this:
Please Sign up or sign in to vote.
Microsoft .NET and Db2 conenction


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 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.


"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"


"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"


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



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

Web Developer
India India
Sreejumon, Microsoft MVP for last 5 years.
He blogs at
He maintains the following sites.

Comments and Discussions

QuestionConnect to DB2 from Compact Framework PinmemberBiju Alapatt26-Jan-15 19:26 
QuestionDB2 on Mainframe Pinmemberkleinkauff16-Apr-13 11:34 
Questionbest practices for exception handling Pinmemberdeostroll1-Jul-11 7:45 
Generalfrench char modmono PinmemberAjay Kale New31-Aug-10 4:11 
QuestionMainframe check Error: Catastrophic failure when connection Mainframe DB2 shadow using Neon Pinmemberilan0522-Jan-10 11:12 
QuestionIBM.Data.DB2.DB2Exception: Unable to allocate statement handle Pinmemberbart.nicolotti11-Feb-09 3:11 
QuestionMulti Table Connection PinmemberNigelAF30-Aug-07 7:14 
GeneralNetwork error while connecting to DB2/MVS from .Net Pinmemberviv_bhatt6-Jul-07 6:51 
Generalurgent PinmemberPHANINDRA.TSK4-May-07 21:13 
QuestionThe 'IBMDADB2.1' provider is not registered on the local machine??? Pinmemberparvinder sehrawat9-Oct-06 22:14 
GeneralWeird connection problem with OleDB PinsussChristian Blackburn8-Mar-04 14:12 
GeneralIBM IDE Add-ins PinmemberWhosit24-Dec-03 7:09 
GeneralRe: IBM IDE Add-ins Pinmemberbivkovic115-May-06 1:36 
GeneralIBM DB2 OLEDB Drivers ConnectionStrings PinmemberDeepak Kumar Vasudevan17-Sep-03 20:40 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings PinmemberTechnology_Learner7-Oct-06 6:08 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings PinmemberTechnology_Learner8-Oct-06 22:55 
QuestionRe: IBM DB2 OLEDB Drivers ConnectionStrings Pinmemberparvinder sehrawat9-Oct-06 3:59 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings Pinmemberparvinder sehrawat9-Oct-06 3:59 
GeneralGood article Pinmembergxhpainter3-Sep-03 17:06 
As a mostly reformed AS/400 programmer recently converted to .net I was pleased to see that an article like this even existed! The as/400 or i-series version of DB2 is ALWAYS behind the development curve even though there are lots of shops using it. HIT Software also has an excellent native DB2 provider for AS/400 and other Db2 platforms. Their version also integrates nicely into Visual Studio 2003 and the .net 1.1 sdk. It is very expensive however. IBM has been promising a native DB2 provider for the iseries but is very very slow to market . Too bad .NET is so easy to develop and now if I could just get to that AS/400 data with it. I will try the ole db connection as shown maybe it isn't that slow...
the IBM DB2 data provider mentioned here ONLY works with Visual Studio 2002 and not the 1.1 .net ! But it does integrate well in that older version of VS and the .net framework.
GeneralIBM DB2 Managed Provider Developers PinmemberKeith Platfoot2-Sep-03 19:38 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberPeter Hendrix2-Sep-03 20:50 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberKeith Platfoot3-Sep-03 5:58 
GeneralRe: IBM DB2 Managed Provider Developers PinmemberJoe Crump21-Sep-04 4:32 
GeneralLinks PinsussAnonymous31-Aug-03 12:20 
GeneralRe: Links PinmemberSreejumon31-Aug-03 13:04 
GeneralRe: Links PinsussAnonymous21-Feb-05 5:54 
GeneralNice article PineditorNishant S29-Aug-03 17:15 

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 | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 29 Aug 2003
Article Copyright 2003 by Sreejumon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid