Connect to DB2 from Microsoft .NET

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.

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.

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.

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.

The 'ibmdadb2' provider is not registered on the local machine.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The 'ibmdadb2' provider is not registered on the local machine.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: The 'ibmdadb2' provider is not registered on the local machine.]

When trying to connect to our AS400 using an OleDb connection I receive the following error:

Non-NULL controlling IUnknown was specified, and either the requested interface was not
IUnknown, or the provider does not support COM aggregation.

However, if I pop a message box, or show a form before opening the connection it all works. Just wondering if anyone has had similar experiences, and if so, how they got around having to use a GUI element to get it to work?

I am still unable to connect to the IBM DB2 database using this connection string.
The connection string is:
Provider=IBMDADB2;Network Address=IP Address of Server:Port Number;Default Schema=Database Name;User ID=myUID;Password=myPWD;"

I got error as:
SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705

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.

I met the guys who developed IBM's DB2 provider for .NET, they gave a presentation at an internal expo here at IBM Rochester (I'm here for the summer as a co-op). I was fairly impressed, and somewhat surprised that there was any C#/.NET development going on at IBM. This would've been an interesting project to work on, and could've really helped hone my ADO.NET skills; too bad I was stuck doing Java programming instead!

Nope, no responsibility here. I've been working on a platform for autonomic software agents, written in Java. I can definitely understand where you're coming from, though, I know they're converting some server software from C++ to Java, and I've had doubts whether this is a smart move, especially because of the sluggishness of a Swing interface. I keep hearing Java's faster than it used to be, but most of the Java apps I have to use here at work still seem pretty slow. The exception is Eclipse, which I've heard uses some sort of customized AWT, not Swing. But it still takes forever to start up, even on my 2.4 GHz machine.

I've also used the IBM DB2 "development" tools working on a project using DB2 from .NET. The tools are the worst pieces of commercial software I've ever used, honestly with no exaggeration. That 'development center' is so buggy I don't think they did any testing (ANY TESTING!). The user-interface isn't intuitive either and unless they have some super-secret shortcut keys for entering data that I don't know about, you can't easily enter test parameters for your stored procedure. I want to type, tab, type, tab, type, tab, enter. And they want you to click, type, oops dont have focus, double-click, type, tab, type (oops no focus), double-click, type, tab, now this decimal field that used to be 0 has -49 in it mysteriously, overwrite that with 0, click on another item, click back, its -49 again, hmmm. Jesuz Chrizt!

And performance.... don't get me started. This thing is dog slow to launch and use. I could probably type in some code, build and run it to test a stored procedure before I can launch it, get to the stored procedure, and test.

Thanks for listening to my rant. I'd love to curse but trying to keep this clean.

Links where the mentioned providers can be downloaded from would have been very useful. IMO this article does not have much value without this information. The only problem is where to find the the db providers; once you have the binary it's real easy to figure out how to use it.