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();
//execute queries
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 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();
//execute queries, etc
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 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();
//execute queries, etc
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 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;Max Pool Size=100;Min Pool 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;
  

Next Recommended Readings
F11 Research & Development, LLC
F11Research & Development, LLC