Feb 15, 2011

Connecting DB2/iSeries From .net Application

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 lot of articles/blog posts and really helped me. But in this post, I thought to consolidate them for fast reference.

There are two .net data providers for DB2.

  1. IBM.Data.DB2.iSeries
  2. IBM.Data.DB2

Both provider follows same rules/guidelines of common ADO.net components. For an example, both have Connection, Command, DataAdapter, DataReader. But there are differences and limitations.

IBM.Data.DB2.iSeries

Using this provider you can only access to 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 bellow screen shots)

  • .NET Data Provider
  • Header,Library and Documentation.

clip_image002Figure 1clip_image002[12]

 

There are limitations/unsupported features [IBM Redbooks 2005 ,Page 38]  in this provider, bellow lists 3 out of 11.

  1. User-defined types (UDTs): Although some features may work, extensive testing has not
    been done using the IBM.Data.DB2.iSeries provider with UDTs.
  2. Distributed relational database architecture (DRDA®), including the CONNECT and
    DISCONNECT statements.
  3. SET TRANSACTION, COMMIT, and ROLLBACK statements: Instead, we recommend
    using the built-in transaction support provided via the iDB2Connection.BeginTransaction()
    method, and the iDB2Transaction object.

I think they have stopped update/improvements for this provider after .net version 1.1

IBM.Data.DB2

This comes as new IBM  data provider for .net 2.0 and having rich features. For the 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 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 success, 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: 9.0.0.2
    Capability bits: ALLDEFINED
    Build: 20070524
    Factory for invairant name IBM.Data.DB2 verified
    Elapsed: 7.15625
 
Step 2: Connecting using "User ID=fuzzy;Password=fuzzy;Database=SAMPLE;
    Server=localhost;ServerType=db2;pooling=false"
    Server type and version: DB2/NT 09.05.0000
    Elapsed: 4.640625
 
Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existence of packages
    SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
    Elapsed: 0.890625
 
Step 4: Calling GetSchema for tables to validate existence of schema functions
    Elapsed: 1.78125
 
Test passed.




References


[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

6 comments:

Sumit said...

Hi!
regarding the IBM.Data.DB2 provider, can you specify how it can be used to modify db2 iSeries information?
It clearly is only suited for reading the data, but fails while trying to perform write operations on them.

Kelum Warnajith Ganegoda said...

Hi Sumit,

It's supports INSERT, UPDATE and DELETE as well.

What kind of failure you get?

Sumit said...

I was getting error messages like REXX variable contains inconsistent data. https://www-304.ibm.com/support/docview.wss?uid=swg21206832

I was also not able to change table state from visual studio.

As a workaround, now I am suffixing my queries with a 'with none' clause. But cannot use Entity framework (or any other tool) directly.

Anonymous said...

I'm trying to connect to DB2 AS400 from .net but the connection fails using this test. Also cannot create the connection from visual studio 2008 with add-ons installed.

Error says remote host was not found. SQLstate=08001

But I can connect using odbc.

I would like to use the db2 connection.

Thanks for any suggestions

Kelum Warnajith Ganegoda said...

Hi,

Could you paste here your connection string.

Without seeing the code cannot give a suggestion.

Kelum Warnajith Ganegoda said...

Hi Sumit,

I found an article for entity framework. Check this out.

http://www.ibm.com/developerworks/data/library/techarticle/dm-0903linqentity/