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:[color=blue]
> 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:
>[color=green]
>> On Thu, 10 Jun 2004 09:52:42 -0400, Dan Scott <dan.scott@ca.ibm.com>
>> wrote:
>>[color=darkred]
>>> 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?
>>>[/color]
>> 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.
>>[color=darkred]
>>> 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?[/color]
>>
>>
>>
>> I have PHP 4.3.6 which is compiled with the --with-ibm-db2 flag.[/color]
>
>
> 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.
>[color=green][color=darkred]
>>>
>>> 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.[/color]
>>
>>
>>
>> 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
>>[/color]
>
> 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.
>[color=green][color=darkred]
>>>
>>> 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?[/color]
>>
>>
>>
>> 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.[/color]
>
>
> Understood. Is there any chance of running Apache/PHP on the same
> machine as your DB2 server?
>[color=green][color=darkred]
>>>
>>>> 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.[/color]
>>
>>
>>
>> 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.
>>[color=darkred]
>>>
>>>> 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.[/color]
>>
>>
>>
>> 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.
>>[color=darkred]
>>>
>>> Dan[/color]
>>
>>
>>
>>[/color][/color]
