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 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(
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.
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
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
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
"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. :)
Sreejumon, Microsoft MVP for last 5 years.
He blogs at http://blog.sreesharp.com
He maintains the following sites.