On Thu, 10 Jun 2004 09:52:42 -0400, Dan Scott <dan.scott@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