Connection strings for IBM DB2

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
  1. using System.Data.OleDb;  
  2. OleDbConnection myConnection = new OleDbConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries  
  6. 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
  1. using System.Data.Odbc;  
  2. OdbcConnection myConnection = new OdbcConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. 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
  1. using IBM.Data.DB2;  
  2. DB2Connection myConnection = new DB2Connection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. 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;