IBM DB2 primarily runs on Unix, Linux IBM I, z/OS, and windows server. It is a relational database management system that comes in a variety of versions, running on devices ranging from handhelds to mainframes.
IBM DB2 is optimized to deliver industry-leading performance across multiple workloads while lowering administration, storage, development, and server costs.
Providers for IBM DB2
- .NET Framework Data Provider for OLE DB (OleDbConnection)
- .NET Framework Data Provider for ODBC (OdbcConnection)
- IBM OLE DB Provider for DB2
- IBM DB2 Driver for ODBC and CLI
- Microsoft OLEDB provider for IBM DB2
- DB2 .NET Data Provider (DB2Connection)
.NET Framework Data Provider for OLE DB
.NET Framework Data Provider for OLE DB is recommended for middle-tier applications using Microsoft SQL Server 6.5, or any OLE DB provider that supports the OLE DB interfaces listed in OLE DB Interfaces used by the OLE DB .NET Data Provider in the .NET Framework SDK.
The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling.
Sample Code
- using System.Data.OleDb;
- OleDbConnection myConnection = new OleDbConnection();
- myConnection.ConnectionString = myConnectionString;
- myConnection.Open();
-
- myConnection.Close();
Bridging to IBM OLE DB Provider for DB2
Syntax:
Provider=IBMDADB2;Database=urDataBase;Hostname=urServerAddress;Protocol=TCPIP;Port=50000;
Uid=urUsername;Pwd=urPassword;
.NET Framework Data Provider for ODBC
The .NET Data Provider for ODBC access to data sources that are connected to using an ODBC driver.
The ODBC .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers A .NET Framework data the provider also serves as a bridge between a data source and an ADO.NET DataSet. For data sources exposed by using ODBC.
Uses
the System.Data.Odbc namespace.
Sample Code
- using System.Data.Odbc;
- OdbcConnection myConnection = new OdbcConnection();
- myConnection.ConnectionString = myConnectionString;
- myConnection.Open();
-
- myConnection.Close();
Bridging to IBM DB2 Driver for ODBC and CLI
Syntax:
Driver={IBM DB2 ODBC DRIVER};Database=urDataBase;Hostname=urServerAddress;Port=1234;
Protocol=TCPIP;Uid=urUsername;Pwd=urPassword;
IBM OLE DB Provider for DB2
IBM OLE DB Provider for DB2 allows DB2 to act as a resource manager for the OLE DB provider. This support gives OLE DB-based applications the ability to extract or query DB2 data using the OLE interface. It is only available for the Windows operating system, and supports only the TCP/IP and Named Pipes communication protocols.
Code
Include "Provider=IBMDADB2" in the connection string to use this provider.
TCP/IP
Syntax:
Provider=IBMDADB2;Database=urDataBase;Hostname=urServerAddress;Protocol=TCPIP;Port=50000;
Uid=urUsername;Pwd=urPassword;
IBM DB2 Driver for ODBC and CLI
The IBM DB2 Driver for ODBC and CLI does not create a local database directory means that when you use this driver, you must make connectivity information available to your applications in other ways. It is installed and configured separately.
Standard Security
Syntax:
Driver={IBM DB2 ODBC DRIVER};Database=urDataBase;Hostname=urServerAddress;Port=1234;
Protocol=TCPIP;Uid=urUsername;Pwd=urPassword;
Microsoft OLEDB provider for IBM DB2
Microsoft OLE DB Provider for DB2 allows for SQL Server distributed queries to query data in DB2 databases. The following are the features of the OLE DB provider for DB2:
-
Interactive and scriptable Setup program
-
SNA Trace Utility and Trace Viewer
-
TCP/IP network connection
-
Execution of dynamic SQL commands (DDL and DML), including CALL statement for stored procedures
-
Customized Data Link property dialog boxes for creating and modifying file-persisted OLE DB data link files.
Code
Include "Provider=DB2OLEDB" in the connection string to use this provider.
TCP/IP
Syntax:
Provider=DB2OLEDB;Network
Transport Library=TCPIP;Network
Address=abc.abc.abc.abc;Initial
Catalog=urCtlg;Package
Collection=urPkgCol;Default
Schema=Schema;User
ID=urUsername;Password=urPassword;
APPC
Syntax:
Provider=DB2OLEDB;APPC
Local LU Alias=urAlias;APPC
Remote LU Alias=urRemote;Initial
Catalog=urCtlg;Package
Collection=urPkgCol;Default
Schema=Schema;UserID=urUsername;
Password=urPassword;
DB2 .NET Data Provider
DB2 .NET Data Provider extends DB2 support for the ADO.NET interface. The DB2 .NET Data Provider delivers high-performing, secure access to DB2 data. Following are the features:
-
Support for DB2 data servers
-
Integrated support for Web Services and Web Methods
-
Server Explorer integration to simplify access to DB2 during development
-
Integrated debugger for DB2 SQL PL (SQL Procedure Language)
-
New set of designers for DB2 database objects including tables, views, procedures, indexes, etc.
-
New support for integrated documentation
Sample Code
- using IBM.Data.DB2;
- DB2Connection myConnection = new DB2Connection();
- myConnection.ConnectionString = myConnectionString;
- myConnection.Open();
-
- myConnection.Close();
Standard Security
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword;
TCP/IP
Syntax:
Provider=DB2OLEDB;Network
Transport Library=TCPIP;Network
Address=abc.abc.abc.abc;Initial
Catalog=urCtlg;Package
Collection=urPkgCol;Default
Schema=Schema;User
ID=urUsername;Password=urPassword;
APPC
Syntax:
Provider=DB2OLEDB;APPC
Local LU Alias=urAlias;APPC
Remote LU Alias=urRemote;Initial
Catalog=urCtlg;Package
Collection=urPkgCol;Default
Schema=Schema;User
ID=urUsername;Password=urPassword;
Connection pooling, time in the pool
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword; Connection
Lifetime=60;
Connection pooling, do not pool
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword; Connection
Reset=false;
Connection pooling pool size
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword;MaxPool Size=100;MinPool Size=10;
Disable connection pooling
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword;Pooling=false;
Specifying schema
Syntax:
Server=urAddress:urPortNumber;Database=urDataBase;UID=urUsername;PWD=urPassword;CurrentSchema=urSchema;
Siddharth ShankarPosted Mar 9, 2021, 6:42 AM
HI Nipun, I am trying to connect to mainframe using db2 client . I have installed Db2 Client Version 11.1.1.I am using the DB2 .NET Data Provider code but I am getting ERROR [42968] [IBM] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968 error. Please let me know what need to be done.
Aruna RanatungaPosted Nov 5, 2019, 8:00 PM
My question is not related to this, but just want to know what is the maximum string length aloud for LVARCHAR? I know from informix it supports upto 32,739, but if I integrated this in to c# will it support to accept string length 32,739?
Jim GussPosted Apr 4, 2011, 10:26 AM
Great article! Question: How do you determine what the Port number should be? We've been using Data Source(ODBC) for a long time to connect to regular .Net Web Sites, but Web Services doesn't work the same way. We've asked IBM Tech support, but they were no help. Thanks for any help you can give us.