====== IBM DB2 =====
~~NOTOC~~
<WRAP right box round 300px>
== Specification ==
^Driver Name|db2|
^Data Provider|db2|
^Status|Active((This driver is actively supported by ADOdb project members))|
^Windows|Yes|
^Unix|Yes|
^ADOdb V5|Yes|
^ADOdb V6|Yes|

== Alternative Drivers ==
[[v5:database:odbc#odbc_db2|ODBC Driver For IBM DB2]]\\
== See Also ==
[[v5:reference:connection:setconnectionparameter#ibmdb2|setConnectionParameter()]]
</WRAP>
===== Description =====
This driver provides a connection to IBM DB2 Databases, using the IBM Data Client. This driver replaces the original [[v5:database:odbc#odbc_db2|DB2 ODBC]] driver.
== PHP Drivers For Windows ==
PHP 7 and 8 drivers for windows are currently available [[https://github.com/ibmdb/php_ibm_db2|here]] 

----------------------------------
==== uCaseTables ====
<WRAP right box>
== syntax ==
   $db->uCaseTables = true/false;
</WRAP>
<WRAP alert>
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
</WRAP>

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

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

$db->uCaseTables = false;
$mt = $db->getMetaColumns('ACT');
print_r($mt);
</code>
--------------------------------
-------------------------------------------------------
===== Connection Example Using Instance =====
<WRAP info>
The DB2 database must be catalogued for a connection like this to work.
</WRAP>
==== Building a catalog entry ====
This is a simple example building a catalogued connection from a windows client to a remote DB2 server running on a machine named adodb-db2.local. The server is running on port 25010. We will add a DSN entry called //**adodb-test**//

<code>
db2cli writecfg add -database SAMPLE -host adodb-db2.local -port 25010 
db2cli writecfg add -dsn adodb-test -database SAMPLE -host adodb-db2.local -port 25010
</code>
To use this DSN
<code php>
include '../adodb/adodb.inc.php';
$dsn = 'adodb-test';
$user = 'db2inst1';
$password = 'instance';
$conn = ADOnewConnection('db2');
$conn->connect($dsn, $user, $password);
</code>

===== Connection Example Using DSN =====
<code php>
include '../adodb/adodb.inc.php';
$dsn = 'hostname=adodb-db2.local;protocol=tcpip;port=25010;database=SAMPLE;uid=db2inst1;pwd=instance';
$conn = ADOnewConnection('db2');
$conn->connect($dsn);
</code>

------------------------------
===== Connection Example Using *LOCAL =====
<code php>
include '../adodb/adodb.inc.php';
$dsn = 'hostname=adodb-db2.local;protocol=tcpip;port=25010;database=SAMPLE;uid=db2inst1;pwd=instance';
$conn = ADOnewConnection('db2');
$conn->connect($dsn);

/*
* Add another connection
*/
$dsn = '*LOCAL';
$conn2 = ADOnewConnection('db2');
$conn2->connect($dsn);
------------------------------
</code>

------------------------------
==== db2oci ====
<WRAP right box round 300px>
== Specification ==
^Driver Name|db2oci|
^Data Provider|db2|
^Status|Deprecated((This driver is deprecated as of ADOdb version 5.21 and will be removed in ADOdb version 6))|
^Windows|Yes|
^Unix|Yes|
^ADOdb V5|Yes|
^ADOdb V6|No|

</WRAP>
This driver re-maps ibm :0 bind variables to oracle compatible ? variables. Use this driver to enhance compatiblity between [[v5:database:oracle]] and DB2 drivers.

The driver is deprecated. To write portable code that uses bind parameters and can be shared between DB2 and Oracle, use the [[v5:reference:connection:param|param()]] method.

-------------------------------------------------
==== db2ora ====

<WRAP right box round 300px>
== Specification ==
^Driver Name|db2|
^Data Provider|db2|
^Status|Obsolete((This driver is not supported, and will be removed in ADOdb V6))|
^Windows|Yes|
^Unix|Yes|
^ADOdb V5|Yes|
^ADOdb V6|No|
</WRAP>
This driver provides undocumented bind variable mapping from ibm to oracle.The functionality appears to overlap the db2_oci driver
{{tag>[ibm db2 tier1]}}

===== 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''.
<code php>

$sql = 'SELECT * FROM ACCOUNTS';

$sql = 'SELECT * FROM accounts';

/*
* Both of these queries will query the ACCOUNTS table
*/
</code>
In order to query the ''accounts'' table, it will be necessary to modify the SQL statement, using **double quotes**

<code php>

$sql = 'SELECT * FROM "accounts"'

</code>

==== Query Precedence ====
The following order of query precedence applies:
  - 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
  - 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
  - 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.


{{tag>[db2 supported]}}