- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:database:ibm_db2

# IBM DB2

##### Specification

Driver Name | db2
Data Provider | db2
Status | Active1)
Windows | Yes
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | Yes

##### Alternative Drivers

ODBC Driver For IBM DB2

## Description

This driver provides a connection to IBM DB2 Databases, using the IBM Data Client. This driver replaces the original DB2 ODBC driver.

### uCaseTables

##### syntax

     $db->uCaseTables = true/false;

Column names are now always returned in upper-case by the driver. Use of this variable is deprecated as of ADODb version 5.21.0 and will be removed in version 6.0.0

This variable sets the keys in metacolumns,metaprimarykeys and metaforeignkeys to upper-case when retrieved. The default is true.

    $mt = $db->getMetaColumns('ACT');
    print_r($mt);

    $db->uCaseTables = false;
    $mt = $db->getMetaColumns('ACT');
    print_r($mt);

## Connection Example Using Instance

The DB2 database must be catalogued for a connection like this to work.

    include '../adodb/adodb.inc.php';
    $database = 'SAMPLE';
    $user = 'db2inst1';
    $password = 'instance';
    $conn = ADOnewConnection('db2');
    $conn->connect($database, $user, $password);

## Connection Example Using DSN

    include '../adodb/adodb.inc.php';
    $dsn = 'hostname=192.168.0.30;protocol=tcpip;port=50000;database=SAMPLE;uid=db2inst1;pwd=instance';
    $conn = ADOnewConnection('db2');
    $conn->connect($dsn);

### db2oci

##### Specification

Driver Name | db2oci
Data Provider | db2
Status | Active2)
Windows | Yes
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | Yes

This driver re-maps ibm :0 bind variables to oracle compatible ? variables. Use this driver to enhance compatiblity between oracle and DB2 drivers

### db2ora

##### Specification

Driver Name | db2
Data Provider | db2
Status | Obsolete3)
Windows | Yes
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | No

This driver provides undocumented bind variable mapping from ibm to oracle.The functionality appears to overlap the db2_oci driver

[ibm, db2]

## Case Sensitivity In Table And Column Names

Unlike most DBMS, DB2 provides complete control over the casing of table and column names. This means, for example, that the tables `ACCOUNTS`, `accounts` and `Accounts` can exist simultaneously in the same database.

In normal usage, table and column names can be searched in a case insensitive manner. That insensitivity always applies precedence to the upper case table name. In this example, 2 tables exist: `ACCOUNTS` and `accounts`.

    $sql = 'SELECT * FROM ACCOUNTS';

    $sql = 'SELECT * FROM accounts';

    /*
    * Both of these queries will query the ACCOUNTS table
    */

In order to query the `accounts` table, it will be necessary to modify the SQL statement, using double quotes

    $sql = 'SELECT * FROM "accounts"'

### Query Precedence

The following order of query precedence applies:

1. If a single table name exists in a database, the table can be queried in any way as long as the table name is not quoted
2. If a single table name exists in a database, a query must match the casing of the name if the query table name is quoted
3. If multiple tables with the same name apply, any unquoted query will search a table name with upper casing, no matter how the query table name is constructed.

### Table Names With Special Characters

Any table name containing special characters will need to be quoted in all queries.

[db2, supported]
1) , 2) This driver is actively supported by ADOdb project members
3) This driver is not supported, and will be removed in ADOdb V6
v5/database/ibm_db2.txt · Last modified: 2016/01/12 17:06 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top