This is an old revision of the document!
IBM DB2
Specification
Driver Name | db2 |
---|---|
Data Provider | db2 |
Status | Active1) |
Windows | Yes |
Unix | Yes |
ADOdb V5 | Yes |
ADOdb V6 | Yes |
Alternative Drivers
Description
This driver provides a connection to IBM DB2 Databases, using the IBM Data Client.
Generic Driver Option Support
ADODB_FETCH_BOTH | Yes |
Transactions | Yes |
Record Counting | Yes |
Sequences | Yes |
Driver Specific Options
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
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:
- 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.
Using metaCasing with DB2
When using DB2, it is important to remember that when using metaCasing and having mixed or lowercase table names in the database may mean that tables may become difficult to access if the are not in the same casing convention as to what is normally used.
Generally, development standards mean that table names in a database are created to a casing standard, for example, all upper-case or lower-case table and column names. In that case, metaCasing will work the same as in other databases, e.g.
/* * Connection to DB2 smple database, accessing a table called ACT * Set the casing to lower case */ $db->setMetaCaseOption($db:METACASE_LOWER); $cols = $db->metaColumnNames('ACT'); print_r($cols); /* * Prints Array( [0] => actno, [1] => actname, [2] => actdesc ) */
These rules also apply to column naming
The Impact Of METACASE_NATIVE
Setting the metaCasing option to METACASE_NATIVE
has an impact on the entire life cycle of a table, and it's use should be understood.
Creating Tables
Consider the following case-sensitive table name: accountTable
, and how it is created and referenced in ADOdb.
MetaCase | Created As | SQL Retrieval | Presentation in MetaFunctions |
---|---|---|---|
METACASE_UPPER | ACCOUNTTABLE | SELECT FROM ACCOUNTTABLE,accountttable,accountTable etc. | ACCOUNTTABLE |
METACASE_LOWER | ACCOUNTTABLE | SELECT FROM ACCOUNTTABLE,accountttable,accountTable etc. | accounttable |
METACASE_NATIVE | accountTable | SELECT FROM “accountTable” | accountTable |
As shown, once a table is created in non-upper case when the metaCasing value is METACASE_NATIVE
, it must always be retrieved using the same casing, and all SQL operations must quote the table names. The functions autoExecute(),getInsertSql(), and getUpdateSql() will automatically quote table and column names if METACASE_NATIVE
is set. For DB2, this behaviour overrides the $quoteFieldnames
parameter.