php-odbc and dynamic vs forward only cursors

[Todd Huish]

I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds to
retrieve 500 rows via a php-odbc link. This same select from the cli is
for all intents practicaly instantaneous. After much research I discovered
that PHP by default uses a dynamic cursor type which can be quite a bit
slower than a forward only cursor. BTW I have been searching forward
only/read only/static cursor as all the same thing, if this is incorrect
someone please disabuse me of the notion. I found some posts on how to
change the php-odbc driver to use forward only cursors. After happily
hacking the php source and recompiling my 500 row result set went from 25
seconds to < 1 second. Elated by this test I recompiled on my main server
and had the programmers run some tests. The problem now is that they use
the odbc_num_rows() function -a lot- and it broke this for them. I found
plenty of documentation on why this is. My main question is, is there
another way to get the odbc driver to return a static cursor. I tried "for
read only" on the end of my sql statements and it appeared to make no
difference. Idealy I would like to use forward only cursors whenever
possible and dynamic ones when row counts are required. I can think of
ways that they can get around using row counts, they are only using them
for a positive/negative on wether a select statement returned any rows,
but until that code can all change I need a different solution.

--
Todd Huish

[Dan Scott]

Wow, there are a lot of questions crammed in there, unfortunately
without much useful data about your environment. I'll try to help where
I can.

First: what are you running on? DB2 version (with FixPak if any),
operating system and version, PHP version. What's your database client
environment and your server environment?

Are you using ODBC connectivity through something like unixODBC or did
you compile PHP using the --with-ibm-db2 configure flag to use native
CLI support?

Finally, there's a really interesting (and old) user comment on the PHP
documentation at http://ca.php.net/manual/en/function.odbc-connect.php
that sounds incredibly similar to your situation -- basically, using the
optional cursor type parameter on your odbc_connect() call to specify
SQL_CUR_USE_ODBC increased the performance of their application from
taking up to 10 seconds for retrieving 100 rows down to a fraction of a
second.

More comments throughout...

Todd Huish wrote:

I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds to
retrieve 500 rows via a php-odbc link. This same select from the cli is
for all intents practicaly instantaneous. After much research I
discovered that PHP by default uses a dynamic cursor type which can be
quite a bit slower than a forward only cursor.
Yes, basically--PHP requests a dynamic cursor, and DB2 downgrades it to
a keyset-driven cursor. A good resource for some of the guts of PHP /
DB2 interaction is Clara Liu's "Application Development Experiences with
PHP and DB2 Universal Database Version 8" --
http://www-106.ibm.com/developerwork...u/0301liu.html
Clara states that to force PHP to open a read-only cursor you just need
to append FOR READ ONLY to your SELECT statements.
BTW I have been
searching forward only/read only/static cursor as all the same thing,
if this is incorrect someone please disabuse me of the notion.
The best description of the differences between cursors can be found in
the topic "Cursors in CLI applications" at
http://publib.boulder.ibm.com/infoce...d/c0007645.htm
Quick differentiation: static and forward only cursors are both
read-only, but static cursors are scrollable (backwards and forwards),
whereas forward only are, well, forward only.
I found
some posts on how to change the php-odbc driver to use forward only
cursors. After happily hacking the php source and recompiling my 500
row result set went from 25 seconds to < 1 second. Elated by this test
I recompiled on my main server and had the programmers run some tests.
The problem now is that they use the odbc_num_rows() function -a lot-
and it broke this for them.
Ah, change the source and you've pretty much lost out on any chance of
getting further help from anyone other than the people that posted the
hacks. Did you try appending "FOR READ ONLY" to your statements before
changing the source?
I found plenty of documentation on why this
is. My main question is, is there another way to get the odbc driver to
return a static cursor. I tried "for read only" on the end of my sql
statements and it appeared to make no difference.
Weird. That should make a big difference. I can see that "FOR READ ONLY"
does the right thing on my Red Hat ES 3.0 Update 2 / DB2 "Stinger" beta
/ PHP 5.0RC2 compiled --with-ibm-db2 system.
Idealy I would like
to use forward only cursors whenever possible and dynamic ones when row
counts are required. I can think of ways that they can get around using
row counts, they are only using them for a positive/negative on wether
a select statement returned any rows, but until that code can all
change I need a different solution.

Clara's article describes a better way of finding out whether a SELECT
statement returned rows or not -- basically, check the return value of
odbc_result() on the first row that you try to fetch. Using
odbc_num_rows() with DB2 returns the number of rows affected by INSERT,
UPDATE, or DELETE statements, and has nothing to do with SELECT
statements, so it shouldn't even have worked in the way that you
describe before you changed the PHP source.

Dan

[Todd Huish]

On Thu, 10 Jun 2004 09:52:42 -0400, Dan Scott <da*******@ca.ibm.com> wrote:

Wow, there are a lot of questions crammed in there, unfortunately
without much useful data about your environment. I'll try to help where
I can.

First: what are you running on? DB2 version (with FixPak if any),
operating system and version, PHP version. What's your database client
environment and your server environment?
I apologize for the lack of info. It's one of those things where the
problem has been plaguing me for days and has rendered me slightly
insensible.
I am running DB2 UDB 8.1.5 on a RHEL 3 platform for the server and a MDK
10 client with 8.1.5 as well.
Are you using ODBC connectivity through something like unixODBC or did
you compile PHP using the --with-ibm-db2 configure flag to use native
CLI support?
I have PHP 4.3.6 which is compiled with the --with-ibm-db2 flag.

Finally, there's a really interesting (and old) user comment on the PHP
documentation at http://ca.php.net/manual/en/function.odbc-connect.php
that sounds incredibly similar to your situation -- basically, using the
optional cursor type parameter on your odbc_connect() call to specify
SQL_CUR_USE_ODBC increased the performance of their application from
taking up to 10 seconds for retrieving 100 rows down to a fraction of a
second.
I read that post and tried adding SQL_CUR_USE_ODBC to my connect string
but it didn't fly. I had hopes too because that is the -exact- problem I
am having. DB2 gives me the following error which I will have to track
down some more.

Warning: odbc_connect(): SQL error: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00, SQL state S1C00 in SQLSetConnectOption in
/virtualhosts/test/www/db2_test.php on line 8

More comments throughout...

Todd Huish wrote:

I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds
to retrieve 500 rows via a php-odbc link. This same select from the
cli is for all intents practicaly instantaneous. After much research I
discovered that PHP by default uses a dynamic cursor type which can be
quite a bit slower than a forward only cursor.
Yes, basically--PHP requests a dynamic cursor, and DB2 downgrades it to
a keyset-driven cursor. A good resource for some of the guts of PHP /
DB2 interaction is Clara Liu's "Application Development Experiences with
PHP and DB2 Universal Database Version 8" --
http://www-106.ibm.com/developerwork...u/0301liu.html
Clara states that to force PHP to open a read-only cursor you just need
to append FOR READ ONLY to your SELECT statements.

BTW I have been searching forward only/read only/static cursor as all
the same thing, if this is incorrect someone please disabuse me of the
notion.

The best description of the differences between cursors can be found in
the topic "Cursors in CLI applications" at
http://publib.boulder.ibm.com/infoce...d/c0007645.htm
Quick differentiation: static and forward only cursors are both
read-only, but static cursors are scrollable (backwards and forwards),
whereas forward only are, well, forward only.

I found some posts on how to change the php-odbc driver to use forward
only cursors. After happily hacking the php source and recompiling my
500 row result set went from 25 seconds to < 1 second. Elated by this
test I recompiled on my main server and had the programmers run some
tests. The problem now is that they use the odbc_num_rows() function
-a lot- and it broke this for them.

Ah, change the source and you've pretty much lost out on any chance of
getting further help from anyone other than the people that posted the
hacks. Did you try appending "FOR READ ONLY" to your statements before
changing the source?

Yeah, that is the very first thing I tried. This also, unfortunately, did
not work allthough everything I read says it should have. I'm not sure why
what I am doing is ignoring the "for read only" indicator. At this point
my php source is back to unhacked. I don't really like leaving it in that
state but I am quickly reaching the end of my rope and am willing to try
anything.

I found plenty of documentation on why this is. My main question is,
is there another way to get the odbc driver to return a static cursor.
I tried "for read only" on the end of my sql statements and it
appeared to make no difference.
Weird. That should make a big difference. I can see that "FOR READ ONLY"
does the right thing on my Red Hat ES 3.0 Update 2 / DB2 "Stinger" beta
/ PHP 5.0RC2 compiled --with-ibm-db2 system.

I'll have to try this some more. I have php5 installed I just don't use it
that much yet. At least this way I know -someone- has gotten this to work
so with that knowledge I can hopefuly forge ahead.

Idealy I would like to use forward only cursors whenever possible and
dynamic ones when row counts are required. I can think of ways that
they can get around using row counts, they are only using them for a
positive/negative on wether a select statement returned any rows, but
until that code can all change I need a different solution.
Clara's article describes a better way of finding out whether a SELECT
statement returned rows or not -- basically, check the return value of
odbc_result() on the first row that you try to fetch. Using
odbc_num_rows() with DB2 returns the number of rows affected by INSERT,
UPDATE, or DELETE statements, and has nothing to do with SELECT
statements, so it shouldn't even have worked in the way that you
describe before you changed the PHP source.

I was unaware they were using num_rows in this fashion and had to have a
bit of a training email to the developers as to what that function is used
for. The problem is that they are very used to using this function to
determine if a select returned a result because we have all been using
mysql for years and that function works just fine. An education problem on
my part. Now of course trying to get all the code changed is a totally
different and joyous proposition.

Dan

--
Todd Huish

[Dan Scott]

Well, I'm afraid I don't have good news to report at this point. I
tested a remote client-server connection (two tiers: browser and Apache
w/ PHP running on client, DB2 on remote server) over an ADSL line and
consistently got 500 rows in about 18 seconds through PHP (writing to a
file, so browser rendering wasn't a factor) vs. 3 seconds for CLI -- but
running a local client-server connection was almost instantaneous. I've
made a few notes interspersed below, but it looks like there's a
bottleneck showing up specifically in the remote client scenario.

I'll try to dig a little further, but I wanted to let you know what I
had found so far.

Todd Huish wrote:

On Thu, 10 Jun 2004 09:52:42 -0400, Dan Scott <da*******@ca.ibm.com> wrote:

Wow, there are a lot of questions crammed in there, unfortunately
without much useful data about your environment. I'll try to help
where I can.

First: what are you running on? DB2 version (with FixPak if any),
operating system and version, PHP version. What's your database
client environment and your server environment?

I apologize for the lack of info. It's one of those things where the
problem has been plaguing me for days and has rendered me slightly
insensible.
I am running DB2 UDB 8.1.5 on a RHEL 3 platform for the server and a
MDK 10 client with 8.1.5 as well.

Are you using ODBC connectivity through something like unixODBC or
did you compile PHP using the --with-ibm-db2 configure flag to use
native CLI support?

I have PHP 4.3.6 which is compiled with the --with-ibm-db2 flag.

PHP 5.0RC2 compiled --with-ibm-db2 here, although I tried running
with unixODBC instead and got exactly the same results of 18 seconds
for 500 rows.

Finally, there's a really interesting (and old) user comment on the
PHP documentation at
http://ca.php.net/manual/en/function.odbc-connect.php that sounds
incredibly similar to your situation -- basically, using the optional
cursor type parameter on your odbc_connect() call to specify
SQL_CUR_USE_ODBC increased the performance of their application from
taking up to 10 seconds for retrieving 100 rows down to a fraction of
a second.

I read that post and tried adding SQL_CUR_USE_ODBC to my connect string
but it didn't fly. I had hopes too because that is the -exact- problem
I am having. DB2 gives me the following error which I will have to
track down some more.

Warning: odbc_connect(): SQL error: [IBM][CLI Driver] CLI0150E Driver
not capable. SQLSTATE=S1C00, SQL state S1C00 in SQLSetConnectOption in
/virtualhosts/test/www/db2_test.php on line 8

I tried it as well, with exactly the same error. Another post I found
suggested that it only worked with unixODBC, so I gave that a shot, but
still got the same error.

More comments throughout...

Todd Huish wrote:

I have noticed something disturbing when retrieving datasets over a
relatively slow line (multiple T1). I am looking at about 25 seconds
to retrieve 500 rows via a php-odbc link. This same select from the
cli is for all intents practicaly instantaneous. After much research
I discovered that PHP by default uses a dynamic cursor type which
can be quite a bit slower than a forward only cursor.

Yes, basically--PHP requests a dynamic cursor, and DB2 downgrades it
to a keyset-driven cursor. A good resource for some of the guts of
PHP / DB2 interaction is Clara Liu's "Application Development
Experiences with PHP and DB2 Universal Database Version 8" --
http://www-106.ibm.com/developerwork...u/0301liu.html

Clara states that to force PHP to open a read-only cursor you just
need to append FOR READ ONLY to your SELECT statements.

BTW I have been searching forward only/read only/static cursor as
all the same thing, if this is incorrect someone please disabuse me
of the notion.

The best description of the differences between cursors can be found
in the topic "Cursors in CLI applications" at
http://publib.boulder.ibm.com/infoce...d/c0007645.htm
Quick differentiation: static and forward only cursors are both
read-only, but static cursors are scrollable (backwards and
forwards), whereas forward only are, well, forward only.

I found some posts on how to change the php-odbc driver to use
forward only cursors. After happily hacking the php source and
recompiling my 500 row result set went from 25 seconds to < 1
second. Elated by this test I recompiled on my main server and had
the programmers run some tests. The problem now is that they use
the odbc_num_rows() function -a lot- and it broke this for them.

Ah, change the source and you've pretty much lost out on any chance
of getting further help from anyone other than the people that posted
the hacks. Did you try appending "FOR READ ONLY" to your statements
before changing the source?

Yeah, that is the very first thing I tried. This also, unfortunately,
did not work allthough everything I read says it should have. I'm not
sure why what I am doing is ignoring the "for read only" indicator. At
this point my php source is back to unhacked. I don't really like
leaving it in that state but I am quickly reaching the end of my rope
and am willing to try anything.

Understood. Is there any chance of running Apache/PHP on the same
machine as your DB2 server?

I found plenty of documentation on why this is. My main question
is, is there another way to get the odbc driver to return a static
cursor. I tried "for read only" on the end of my sql statements and
it appeared to make no difference.

Weird. That should make a big difference. I can see that "FOR READ
ONLY" does the right thing on my Red Hat ES 3.0 Update 2 / DB2
"Stinger" beta / PHP 5.0RC2 compiled --with-ibm-db2 system.

I'll have to try this some more. I have php5 installed I just don't use
it that much yet. At least this way I know -someone- has gotten this to
work so with that knowledge I can hopefuly forge ahead.

Idealy I would like to use forward only cursors whenever possible
and dynamic ones when row counts are required. I can think of ways
that they can get around using row counts, they are only using them
for a positive/negative on wether a select statement returned any
rows, but until that code can all change I need a different solution.

Clara's article describes a better way of finding out whether a
SELECT statement returned rows or not -- basically, check the return
value of odbc_result() on the first row that you try to fetch. Using
odbc_num_rows() with DB2 returns the number of rows affected by
INSERT, UPDATE, or DELETE statements, and has nothing to do with
SELECT statements, so it shouldn't even have worked in the way that
you describe before you changed the PHP source.

I was unaware they were using num_rows in this fashion and had to have
a bit of a training email to the developers as to what that function is
used for. The problem is that they are very used to using this function
to determine if a select returned a result because we have all been
using mysql for years and that function works just fine. An education
problem on my part. Now of course trying to get all the code changed is
a totally different and joyous proposition.

Dan

[Dan Scott]

A further set of data: running the same 500-row SELECT tests over a
local 100MB LAN connection (latency: ~0.1ms) in the same two-tier
configuration gave me sub-second PHP results, so the network is clearly
a major factor. Windows client -> Windows server and Linux client ->
Windows server showed the same results.

Dan

Dan Scott wrote:

Well, I'm afraid I don't have good news to report at this point. I
tested a remote client-server connection (two tiers: browser and Apache
w/ PHP running on client, DB2 on remote server) over an ADSL line and
consistently got 500 rows in about 18 seconds through PHP (writing to a
file, so browser rendering wasn't a factor) vs. 3 seconds for CLI -- but
running a local client-server connection was almost instantaneous. I've
made a few notes interspersed below, but it looks like there's a
bottleneck showing up specifically in the remote client scenario.

I'll try to dig a little further, but I wanted to let you know what I
had found so far.

Todd Huish wrote:

On Thu, 10 Jun 2004 09:52:42 -0400, Dan Scott <da*******@ca.ibm.com>
wrote:

Wow, there are a lot of questions crammed in there, unfortunately
without much useful data about your environment. I'll try to help
where I can.

First: what are you running on? DB2 version (with FixPak if any),
operating system and version, PHP version. What's your database
client environment and your server environment?

I apologize for the lack of info. It's one of those things where the
problem has been plaguing me for days and has rendered me slightly
insensible.
I am running DB2 UDB 8.1.5 on a RHEL 3 platform for the server and a
MDK 10 client with 8.1.5 as well.

Are you using ODBC connectivity through something like unixODBC or
did you compile PHP using the --with-ibm-db2 configure flag to use
native CLI support?

I have PHP 4.3.6 which is compiled with the --with-ibm-db2 flag.

PHP 5.0RC2 compiled --with-ibm-db2 here, although I tried running
with unixODBC instead and got exactly the same results of 18 seconds
for 500 rows.

Finally, there's a really interesting (and old) user comment on the
PHP documentation at
http://ca.php.net/manual/en/function.odbc-connect.php that sounds
incredibly similar to your situation -- basically, using the
optional cursor type parameter on your odbc_connect() call to
specify SQL_CUR_USE_ODBC increased the performance of their
application from taking up to 10 seconds for retrieving 100 rows
down to a fraction of a second.

I read that post and tried adding SQL_CUR_USE_ODBC to my connect
string but it didn't fly. I had hopes too because that is the -exact-
problem I am having. DB2 gives me the following error which I will
have to track down some more.

Warning: odbc_connect(): SQL error: [IBM][CLI Driver] CLI0150E Driver
not capable. SQLSTATE=S1C00, SQL state S1C00 in SQLSetConnectOption
in /virtualhosts/test/www/db2_test.php on line 8

I tried it as well, with exactly the same error. Another post I found
suggested that it only worked with unixODBC, so I gave that a shot, but
still got the same error.

More comments throughout...

Todd Huish wrote:

I have noticed something disturbing when retrieving datasets over
a relatively slow line (multiple T1). I am looking at about 25
seconds to retrieve 500 rows via a php-odbc link. This same select
from the cli is for all intents practicaly instantaneous. After
much research I discovered that PHP by default uses a dynamic
cursor type which can be quite a bit slower than a forward only
cursor.

Yes, basically--PHP requests a dynamic cursor, and DB2 downgrades it
to a keyset-driven cursor. A good resource for some of the guts of
PHP / DB2 interaction is Clara Liu's "Application Development
Experiences with PHP and DB2 Universal Database Version 8" --
http://www-106.ibm.com/developerwork...u/0301liu.html

Clara states that to force PHP to open a read-only cursor you just
need to append FOR READ ONLY to your SELECT statements.

BTW I have been searching forward only/read only/static cursor as
all the same thing, if this is incorrect someone please disabuse
me of the notion.

The best description of the differences between cursors can be found
in the topic "Cursors in CLI applications" at
http://publib.boulder.ibm.com/infoce...d/c0007645.htm
Quick differentiation: static and forward only cursors are both
read-only, but static cursors are scrollable (backwards and
forwards), whereas forward only are, well, forward only.

I found some posts on how to change the php-odbc driver to use
forward only cursors. After happily hacking the php source and
recompiling my 500 row result set went from 25 seconds to < 1
second. Elated by this test I recompiled on my main server and had
the programmers run some tests. The problem now is that they use
the odbc_num_rows() function -a lot- and it broke this for them.

Ah, change the source and you've pretty much lost out on any chance
of getting further help from anyone other than the people that
posted the hacks. Did you try appending "FOR READ ONLY" to your
statements before changing the source?

Yeah, that is the very first thing I tried. This also, unfortunately,
did not work allthough everything I read says it should have. I'm not
sure why what I am doing is ignoring the "for read only" indicator.
At this point my php source is back to unhacked. I don't really like
leaving it in that state but I am quickly reaching the end of my rope
and am willing to try anything.

Understood. Is there any chance of running Apache/PHP on the same
machine as your DB2 server?

I found plenty of documentation on why this is. My main question
is, is there another way to get the odbc driver to return a static
cursor. I tried "for read only" on the end of my sql statements
and it appeared to make no difference.

Weird. That should make a big difference. I can see that "FOR READ
ONLY" does the right thing on my Red Hat ES 3.0 Update 2 / DB2
"Stinger" beta / PHP 5.0RC2 compiled --with-ibm-db2 system.

I'll have to try this some more. I have php5 installed I just don't
use it that much yet. At least this way I know -someone- has gotten
this to work so with that knowledge I can hopefuly forge ahead.

Idealy I would like to use forward only cursors whenever possible
and dynamic ones when row counts are required. I can think of ways
that they can get around using row counts, they are only using
them for a positive/negative on wether a select statement returned
any rows, but until that code can all change I need a different
solution.

Clara's article describes a better way of finding out whether a
SELECT statement returned rows or not -- basically, check the return
value of odbc_result() on the first row that you try to fetch.
Using odbc_num_rows() with DB2 returns the number of rows affected
by INSERT, UPDATE, or DELETE statements, and has nothing to do with
SELECT statements, so it shouldn't even have worked in the way that
you describe before you changed the PHP source.

I was unaware they were using num_rows in this fashion and had to have
a bit of a training email to the developers as to what that function
is used for. The problem is that they are very used to using this
function to determine if a select returned a result because we have
all been using mysql for years and that function works just fine. An
education problem on my part. Now of course trying to get all the
code changed is a totally different and joyous proposition.

Dan

[Todd Huish]

On Mon, 14 Jun 2004 14:30:59 -0400, Dan Scott <da*******@ca.ibm.com> wrote:

A further set of data: running the same 500-row SELECT tests over a
local 100MB LAN connection (latency: ~0.1ms) in the same two-tier
configuration gave me sub-second PHP results, so the network is clearly
a major factor. Windows client -> Windows server and Linux client ->
Windows server showed the same results.

Dan

Yeah thats about the same thing I am showing. I would like to run the db2
server close to the webservers, and I do for the front end websites, but I
can't for the backend tools which are web based as well. They will
eventualy be using a locally replicated copy of the db but that is down
the road a couple months. I have re-educated the developers in the use of
the num_rows() function and after a weekend of fixing I think we are going
to be able to live without it and I should be able to change the php
driver to use the forward_only cursor. Live, learn, do, learn some more,
backtrack, re-do, eventualy come into the light...this is the life we live
:-)

--
Todd Huish