Click here to Skip to main content
15,445,757 members
Articles / Programming Languages / C#
Posted 28 Aug 2003


61 bookmarked

Connect to DB2 from Microsoft .NET

Rate me:
Please Sign up or sign in to vote.
4.67/5 (16 votes)
28 Aug 20034 min read
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.

Image 1

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 


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



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

Written By
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 Pin
Biju Alapatt26-Jan-15 18:26
MemberBiju Alapatt26-Jan-15 18:26 
QuestionDB2 on Mainframe Pin
kleinkauff16-Apr-13 10:34
Memberkleinkauff16-Apr-13 10:34 
Questionbest practices for exception handling Pin
deostroll1-Jul-11 6:45
Memberdeostroll1-Jul-11 6:45 
Generalfrench char modmono Pin
Ajay Kale New31-Aug-10 3:11
MemberAjay Kale New31-Aug-10 3:11 
QuestionMainframe check Error: Catastrophic failure when connection Mainframe DB2 shadow using Neon Pin
ilan0522-Jan-10 10:12
Memberilan0522-Jan-10 10:12 
QuestionIBM.Data.DB2.DB2Exception: Unable to allocate statement handle Pin
bart.nicolotti11-Feb-09 2:11
Memberbart.nicolotti11-Feb-09 2:11 
QuestionMulti Table Connection Pin
NigelAF30-Aug-07 6:14
MemberNigelAF30-Aug-07 6:14 
GeneralNetwork error while connecting to DB2/MVS from .Net Pin
viv_bhatt6-Jul-07 5:51
Memberviv_bhatt6-Jul-07 5:51 
Generalurgent Pin
PhanindraTSK4-May-07 20:13
MemberPhanindraTSK4-May-07 20:13 
QuestionThe 'IBMDADB2.1' provider is not registered on the local machine??? Pin
parvinder sehrawat9-Oct-06 21:14
Memberparvinder sehrawat9-Oct-06 21:14 
GeneralWeird connection problem with OleDB Pin
CBlackburn8-Mar-04 13:12
MemberCBlackburn8-Mar-04 13:12 
GeneralIBM IDE Add-ins Pin
eidylon24-Dec-03 6:09
Membereidylon24-Dec-03 6:09 
GeneralRe: IBM IDE Add-ins Pin
bivkovic115-May-06 0:36
Memberbivkovic115-May-06 0:36 
GeneralIBM DB2 OLEDB Drivers ConnectionStrings Pin
Vasudevan Deepak Kumar17-Sep-03 19:40
MemberVasudevan Deepak Kumar17-Sep-03 19:40 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings Pin
Prabhat_Joshi7-Oct-06 5:08
MemberPrabhat_Joshi7-Oct-06 5:08 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings Pin
Prabhat_Joshi8-Oct-06 21:55
MemberPrabhat_Joshi8-Oct-06 21:55 
QuestionRe: IBM DB2 OLEDB Drivers ConnectionStrings Pin
parvinder sehrawat9-Oct-06 2:59
Memberparvinder sehrawat9-Oct-06 2:59 
GeneralRe: IBM DB2 OLEDB Drivers ConnectionStrings Pin
parvinder sehrawat9-Oct-06 2:59
Memberparvinder sehrawat9-Oct-06 2:59 
GeneralGood article Pin
gxhpainter3-Sep-03 16:06
Membergxhpainter3-Sep-03 16:06 
GeneralIBM DB2 Managed Provider Developers Pin
Keith Platfoot2-Sep-03 18:38
MemberKeith Platfoot2-Sep-03 18:38 
GeneralRe: IBM DB2 Managed Provider Developers Pin
PeterHarrie2-Sep-03 19:50
MemberPeterHarrie2-Sep-03 19:50 
GeneralRe: IBM DB2 Managed Provider Developers Pin
Keith Platfoot3-Sep-03 4:58
MemberKeith Platfoot3-Sep-03 4:58 
GeneralRe: IBM DB2 Managed Provider Developers Pin
Joe Crump21-Sep-04 3:32
MemberJoe Crump21-Sep-04 3:32 
GeneralLinks Pin
Anonymous31-Aug-03 11:20
MemberAnonymous31-Aug-03 11:20 
GeneralRe: Links Pin
Sreejumon31-Aug-03 12:04
MemberSreejumon31-Aug-03 12:04 

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

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