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 {
89 var $databaseType = "db2";
90 var $concat_operator = '||';
91 var $sysDate = 'CURRENT_DATE';
92 var $sysTimeStamp = 'CURRENT TIMESTAMP';
93 // The complete string representation of a timestamp has the form
94 // yyyy-mm-dd-hh.mm.ss.nnnnnn.
95 var $fmtTimeStamp = "'Y-m-d-H.i.s'";
96 var $ansiOuter = true;
97 var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
98 var $_bindInputArray = false;
99 var $upperCase = 'upper';
103 if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
107 function IfNull( $field, $ifNull )
109 return " COALESCE($field, $ifNull) "; // if DB2 UDB
112 function ServerInfo()
114 //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
115 $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
116 //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
117 return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
122 return $this->GetOne($this->identitySQL);
125 function RowLock($tables,$where)
127 if ($this->_autocommit) $this->BeginTrans();
128 return $this->GetOne("select 1 as ignore from $tables where $where for update");
131 function &MetaTables($ttype=false,$showSchema=false)
133 global $ADODB_FETCH_MODE;
135 $savem = $ADODB_FETCH_MODE;
136 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
137 $qid = odbc_tables($this->_connectionID);
139 $rs = new ADORecordSet_odbc($qid);
141 $ADODB_FETCH_MODE = $savem;
142 if (!$rs) return false;
144 $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
146 $arr =& $rs->GetArray();
153 $isview = strncmp($ttype,'V',1) === 0;
155 for ($i=0; $i < sizeof($arr); $i++) {
157 if (!$arr[$i][2]) continue;
158 if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
162 if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
166 if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
167 } elseif (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
168 } elseif (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
173 // Format date column in sql string given an input format that understands Y M D
174 function SQLDate($fmt, $col=false)
176 // use right() and replace() ?
177 if (!$col) $col = $this->sysDate;
181 for ($i=0; $i < $len; $i++) {
187 $s .= "char(year($col))";
190 $s .= "substr(monthname($col),1,3)";
193 $s .= "right(digits(month($col)),2)";
197 $s .= "right(digits(day($col)),2)";
201 if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";
206 if ($col != $this->sysDate)
207 $s .= "right(digits(minute($col)),2)";
212 if ($col != $this->sysDate)
213 $s .= "right(digits(second($col)),2)";
219 $ch = substr($fmt,$i,1);
221 $s .= $this->qstr($ch);
227 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
230 // could also use " OPTIMIZE FOR $nrows ROWS "
231 if ($nrows >= 0) $sql .= " FETCH FIRST $nrows ROWS ONLY ";
232 $rs =& $this->Execute($sql,$inputArr);
234 if ($offset > 0 && $nrows < 0);
237 $sql .= " FETCH FIRST $nrows ROWS ONLY ";
239 $rs =& ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
247 class ADORecordSet_db2 extends ADORecordSet_odbc {
249 var $databaseType = "db2";
251 function ADORecordSet_db2($id,$mode=false)
253 $this->ADORecordSet_odbc($id,$mode);
256 function MetaType($t,$len=-1,$fieldobj=false)
260 $t = $fieldobj->type;
261 $len = $fieldobj->max_length;
264 switch (strtoupper($t)) {
268 if ($len <= $this->blobSize) return 'C';
273 case 'DBCLOB': // double-byte