I need to connect to a DB2 from a .NET application. But it’s in iSeries system. I did not know what is the way do it. I read a lot of articles/blog posts and they really helped me. But in this post, I thought of consolidating them for fast reference.
There are two .NET data providers for DB2.
Both providers follow the same rules/guidelines of common ADO.NET components. For an example, both have
DataReader. But there are differences and limitations.
Using this provider, you can only access the iSeries system. You have to install iSeries Client Access to development machine and/or server. It is quite a bit setup file ( > 3GB with 64Bit version), but we required (refer the below screen shots):
- .NET Data Provider
- Header, Library and Documentation
There are limitations/unsupported features [IBM Redbooks 2005 ,Page 38] in this provider, below lists 3 out of 11.
- User-defined types (UDTs): Although some features may work, extensive testing has not
been done using the
IBM.Data.DB2.iSeries provider with UDTs.
- Distributed relational database architecture (DRDA®), including the
ROLLBACK statements: Instead, we recommend
using the built-in transaction support provided via the
iDB2Connection.BeginTransaction() method, and the
I think they have stopped update/improvements for this provider after .NET version 1.1
This comes as new IBM data provider for .NET 2.0 and has rich features. For easier development, it is coming with Visual Studio Add-in. Also IBM released beta version for .net 4.0 and add-in for VS 2010. You can see details and download by clicking this link.
Testing DB2 .NET Connection
After installing either IBM Data Server Client or IBM Data Server Runtime Client, we can test the connection by running the following command:
To connect iSeries system, we have to give the port no. 446 (default) to the connection string, Server=[IP:PORT].
C:\Program Files\IBM\SQLLIB\BIN>testconn20.exe "User ID=[UserName];Password=[pwd];
Database=[Data Base Or Alias];Server=[IP];"
If this succeeds, it should dump output to the console similar to the following. [Beginning DB2 2008, Page 282]
Step 1:Printing version info
.NET Framework version: 2.0.50727.42
DB2 .NET provider version: 220.127.116.11
Capability bits: ALLDEFINED
Factory for invairant name IBM.Data.DB2 verified
Step 2: Connecting using "User ID=fuzzy;Password=fuzzy;Database=SAMPLE;
Server type and version: DB2/NT 09.05.0000
Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existence of packages
SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
Step 4: Calling GetSchema for tables to validate existence of schema functions
- [IBM Redbooks 2005]: Hernando Bedoya,Carlos Carminati,Lorie DuBois,Jarek Miszczyk,Ajit Mungale. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET. 2005
- [Beginning DB2 2008]: Grant Allen,Beginning DB2: From Novice to Professional, Apress 2008