3 V4.22 15 Apr 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence.
7 Set tabs to 4 for best viewing.
9 Latest version is available at http://php.weblogs.com/
11 DB2 data driver. Requires ODBC.
17 thanks a lot for your help. Today we discovered what
20 After "playing" a little bit with the php-scripts that try
21 to connect to the IBM DB2, we set the optional parameter
22 Cursortype when calling odbc_pconnect(....).
24 And the exciting thing: When we set the cursor type
25 to SQL_CUR_USE_ODBC Cursor Type, then
26 the whole query speed up from 1 till 10 seconds
27 to 0.2 till 0.3 seconds for 100 records. Amazing!!!
29 Therfore, PHP is just almost fast as calling the DB2
30 from Servlets using JDBC (don't take too much care
31 about the speed at whole: the database was on a
32 completely other location, so the whole connection
33 was made over a slow network connection).
35 I hope this helps when other encounter the same
36 problem when trying to connect to DB2 from
43 Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by
44 IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver.
46 From the IBM CLI Reference:
48 SQL_ATTR_ODBC_CURSORS (DB2 CLI v5)
49 This connection attribute is defined by ODBC, but is not supported by DB2
50 CLI. Any attempt to set or get this attribute will result in an SQLSTATE of
51 HYC00 (Driver not capable).
53 A 32-bit option specifying how the Driver Manager uses the ODBC cursor
56 So I guess this means the message [above] was related to using a 3rd party
59 Setting SQL_CUR_USE_ODBC
60 ========================
61 To set SQL_CUR_USE_ODBC for drivers that require it, do this:
63 $db = NewADOConnection('db2');
64 $db->curMode = SQL_CUR_USE_ODBC;
65 $db->Connect($dsn, $userid, $pwd);
70 I have had reports that the $host and $database params have to be reversed in
71 Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu:
73 > The symptom is that if I change the database engine from postgres or any other to DB2 then the following
74 > connection command becomes wrong despite being described this version to be correct in the docs.
76 > $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME )
78 > In case of DB2 I had to swap the first and last arguments in order to connect properly.
82 if (!defined('_ADODB_ODBC_LAYER')) {
83 include(ADODB_DIR . "/drivers/adodb-odbc.inc.php");
85 if (!defined('ADODB_DB2')) {
86 define('ADODB_DB2', 1);
88 class ADODB_DB2 extends ADODB_odbc
90 var $databaseType = "db2";
91 var $concat_operator = '||';
92 var $sysDate = 'CURRENT_DATE';
93 var $sysTimeStamp = 'CURRENT TIMESTAMP';
94 // The complete string representation of a timestamp has the form
95 // yyyy-mm-dd-hh.mm.ss.nnnnnn.
96 var $fmtTimeStamp = "'Y-m-d-H.i.s'";
97 var $ansiOuter = true;
98 var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
99 var $_bindInputArray = false;
100 var $upperCase = 'upper';
104 if (strncmp(PHP_OS, 'WIN', 3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
108 function IfNull($field, $ifNull)
110 return " COALESCE($field, $ifNull) "; // if DB2 UDB
113 function ServerInfo()
115 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
116 $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
117 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
118 return array('description' => 'DB2 ODBC driver', 'version' => $vers);
123 return $this->GetOne($this->identitySQL);
126 function RowLock($tables, $where)
128 if ($this->_autocommit) $this->BeginTrans();
129 return $this->GetOne("select 1 as ignore from $tables where $where for update");
132 function &MetaTables($ttype = false, $showSchema = false)
134 global $ADODB_FETCH_MODE;
136 $savem = $ADODB_FETCH_MODE;
137 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
138 $qid = odbc_tables($this->_connectionID);
140 $rs = new ADORecordSet_odbc($qid);
142 $ADODB_FETCH_MODE = $savem;
143 if (!$rs) return false;
145 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
147 $arr =& $rs->GetArray();
154 $isview = strncmp($ttype, 'V', 1) === 0;
156 for ($i = 0; $i < sizeof($arr); $i++) {
158 if (!$arr[$i][2]) continue;
159 if (strncmp($arr[$i][1], 'SYS', 3) === 0) continue;
163 if ($showSchema) $arr[$i][2] = $arr[$i][1] . '.' . $arr[$i][2];
167 if (strncmp($type, 'V', 1) === 0) $arr2[] = $arr[$i][2];
168 } elseif (strncmp($type, 'T', 1) === 0) $arr2[] = $arr[$i][2];
169 } elseif (strncmp($type, 'S', 1) !== 0) $arr2[] = $arr[$i][2];
174 // Format date column in sql string given an input format that understands Y M D
175 function SQLDate($fmt, $col = false)
177 // use right() and replace() ?
178 if (!$col) $col = $this->sysDate;
182 for ($i = 0; $i < $len; $i++) {
188 $s .= "char(year($col))";
191 $s .= "substr(monthname($col),1,3)";
194 $s .= "right(digits(month($col)),2)";
198 $s .= "right(digits(day($col)),2)";
202 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
207 if ($col != $this->sysDate)
208 $s .= "right(digits(minute($col)),2)";
213 if ($col != $this->sysDate)
214 $s .= "right(digits(second($col)),2)";
220 $ch = substr($fmt, $i, 1);
222 $s .= $this->qstr($ch);
228 function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputArr = false)
231 // could also use " OPTIMIZE FOR $nrows ROWS "
232 if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY ";
233 $rs =& $this->Execute($sql, $inputArr);
235 if ($offset > 0 && $nrows < 0) ;
238 $sql .= " FETCH FIRST $nrows ROWS ONLY ";
240 $rs =& ADOConnection::SelectLimit($sql, -1, $offset, $inputArr);
250 class ADORecordSet_db2 extends ADORecordSet_odbc
253 var $databaseType = "db2";
255 function ADORecordSet_db2($id, $mode = false)
257 $this->ADORecordSet_odbc($id, $mode);
260 function MetaType($t, $len = -1, $fieldobj = false)
264 $t = $fieldobj->type;
265 $len = $fieldobj->max_length;
268 switch (strtoupper($t)) {
272 if ($len <= $this->blobSize) return 'C';
277 case 'DBCLOB': // double-byte