Re: [PHP-DB] DB2 Performance issue - More Info
Hi Mark, Thanks for the investigation! Will add your comment to the db2 drivers of adodb. Regards, John "Mark Newnham" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > From the IBM CLI Reference: > > SQL_ATTR_ODBC_CURSORS (DB2 CLI v5) > This connection attribute is defined by ODBC, but is not supported by DB2 > CLI. Any attempt to set or get this attribute will result in an SQLSTATE of > HYC00 (Driver not capable). > > A 32-bit option specifying how the Driver Manager uses the ODBC cursor > library. > > So I guess this means the message below was related to using a 3rd party > odbc driver. > > Shucks.. > > > Regards > > Mark > > > > -Original Message- > > From: Mark Newnham [mailto:[EMAIL PROTECTED]] > > Sent: Monday, October 01, 2001 3:33 PM > > To: '[EMAIL PROTECTED]' > > Subject: [PHP-DB] DB2 Performance issue > > > > > > This was posted on the newsgroup a couple of months ago: > > > > > > Christian Szardenings wrote: > > > > > > Hi Andrew, > > > > > > thanks a lot for your help. Today we discovered what > > > our real problem was: > > > > > > After "playing" a little bit with the php-scripts that try > > > to connect to the IBM DB2, we set the optional parameter > > > Cursortype when calling odbc_pconnect(). > > > > > > And the exciting thing: When we set the cursor type > > > to SQL_CUR_USE_ODBC Cursor Type, then > > > the whole query speed up from 1 till 10 seconds > > > to 0.2 till 0.3 seconds for 100 records. Amazing!!! > > > > > > Therfore, PHP is just almost fast as calling the DB2 > > > from Servlets using JDBC (don't take too much care > > > about the speed at whole: the database was on a > > > completely other location, so the whole connection > > > was made over a slow network connection). > > > > > > I hope this helps when other encounter the same > > > problem when trying to connect to DB2 from > > > PHP. > > > > > > Kind regards, > > > Christian Szardenings > > > > This posting has caused some discussion amongst some of us > > who are trying to > > use DB2 in Native mode (i.e. using Unified ODBC) as the IBM > > driver manager > > appears not to support this parameter. ( An odbc connect with > > the specified > > parameter returns [IBM][CLI Driver] CLI0150E Driver not capable. > > SQLSTATE=S1C00 > > > > Could the author or anyone else who knows, please clarify > > this posting if > > they are using unified odbc or perhaps a third party ODBC driver. > > > > > > TIA > > > > Mark > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: > > [EMAIL PROTECTED] > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] DB2 Performance issue - More Info
>From the IBM CLI Reference: SQL_ATTR_ODBC_CURSORS (DB2 CLI v5) This connection attribute is defined by ODBC, but is not supported by DB2 CLI. Any attempt to set or get this attribute will result in an SQLSTATE of HYC00 (Driver not capable). A 32-bit option specifying how the Driver Manager uses the ODBC cursor library. So I guess this means the message below was related to using a 3rd party odbc driver. Shucks.. Regards Mark > -Original Message- > From: Mark Newnham [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 01, 2001 3:33 PM > To: '[EMAIL PROTECTED]' > Subject: [PHP-DB] DB2 Performance issue > > > This was posted on the newsgroup a couple of months ago: > > > Christian Szardenings wrote: > > > > Hi Andrew, > > > > thanks a lot for your help. Today we discovered what > > our real problem was: > > > > After "playing" a little bit with the php-scripts that try > > to connect to the IBM DB2, we set the optional parameter > > Cursortype when calling odbc_pconnect(). > > > > And the exciting thing: When we set the cursor type > > to SQL_CUR_USE_ODBC Cursor Type, then > > the whole query speed up from 1 till 10 seconds > > to 0.2 till 0.3 seconds for 100 records. Amazing!!! > > > > Therfore, PHP is just almost fast as calling the DB2 > > from Servlets using JDBC (don't take too much care > > about the speed at whole: the database was on a > > completely other location, so the whole connection > > was made over a slow network connection). > > > > I hope this helps when other encounter the same > > problem when trying to connect to DB2 from > > PHP. > > > > Kind regards, > > Christian Szardenings > > This posting has caused some discussion amongst some of us > who are trying to > use DB2 in Native mode (i.e. using Unified ODBC) as the IBM > driver manager > appears not to support this parameter. ( An odbc connect with > the specified > parameter returns [IBM][CLI Driver] CLI0150E Driver not capable. > SQLSTATE=S1C00 > > Could the author or anyone else who knows, please clarify > this posting if > they are using unified odbc or perhaps a third party ODBC driver. > > > TIA > > Mark > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: > [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] DB2 Performance issue
Okay, one relevant issue is that PHP sets default cursor type to Dynamic. While this is a nice, potentially bidirectional and sensitive-to-change cursor it can be very expensive, some ODBC drivers don't support it (OpenLink's do) and Dynamic cursors typically dont work with tables lacking a primary key (e.g. views). To verify that this performance is directly the result of the default cursor setting, change SQL_CURSOR_DYNAMIC to SQL_CURSOR_STATIC in php_odbc.c and recompile. Best regards, Andrew Hill Director of Technology Evangelism OpenLink Software http://www.openlinksw.com Universal Data Access & Data Integration Technology Providers > -Original Message- > From: Mark Newnham [mailto:[EMAIL PROTECTED]] > Sent: Monday, October 01, 2001 5:33 PM > To: '[EMAIL PROTECTED]' > Subject: [PHP-DB] DB2 Performance issue > > > This was posted on the newsgroup a couple of months ago: > > > Christian Szardenings wrote: > > > > Hi Andrew, > > > > thanks a lot for your help. Today we discovered what > > our real problem was: > > > > After "playing" a little bit with the php-scripts that try > > to connect to the IBM DB2, we set the optional parameter > > Cursortype when calling odbc_pconnect(). > > > > And the exciting thing: When we set the cursor type > > to SQL_CUR_USE_ODBC Cursor Type, then > > the whole query speed up from 1 till 10 seconds > > to 0.2 till 0.3 seconds for 100 records. Amazing!!! > > > > Therfore, PHP is just almost fast as calling the DB2 > > from Servlets using JDBC (don't take too much care > > about the speed at whole: the database was on a > > completely other location, so the whole connection > > was made over a slow network connection). > > > > I hope this helps when other encounter the same > > problem when trying to connect to DB2 from > > PHP. > > > > Kind regards, > > Christian Szardenings > > This posting has caused some discussion amongst some of us who > are trying to > use DB2 in Native mode (i.e. using Unified ODBC) as the IBM driver manager > appears not to support this parameter. ( An odbc connect with the > specified > parameter returns [IBM][CLI Driver] CLI0150E Driver not capable. > SQLSTATE=S1C00 > > Could the author or anyone else who knows, please clarify this posting if > they are using unified odbc or perhaps a third party ODBC driver. > > > TIA > > Mark > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
