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 <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.

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
>
>
>