I have some tests that I run against the Derby Server using the IBM DB2 ODBC
client and I've noticed that, for scrollable cursors, the client returns the
first row *twice* now that the Derby Server supports pre-fetching (DERBY-822).
Long story short someone who works with the client pointed out that the client
is sending a QRYROWSET of size "0" when it sends the OPNQRY for selects, which
is (meant to be) an indication to the server that it should not return any rows.
The person admitted that he wasn't sure if DRDA specifies that a QRYROWSET of
"0" should disable pre-fetching or not--but that's what the client code seems to
expect. He also admitted that it's possible to fix this on the ODBC client side
by discarding rows after the OPNQRY--but a) it seems silly (to me) to perform
prefetching and then spend extra logic trying to discard the data, and b) an
ODBC client change won't happen in the Derby 10.2 timeframe.
I verified that with the ODBC client the server does indeed receive a qryrowset
value of "0". My question, then, is this: should the server perform
pre-fetching if the client sends a qryrowset of "0" on an OPNQRY? And what does
DRDA say about it?
There's a quote from the DRDA manual that is actually pasted in the DERBY-822
description, and which I've confirmed in my own copy of the manual. Note in
particular the third sentence (highlighted):
<begin quote>
The qryrowset parameter specifies whether a rowset of rows is to be returned
with the command. This is only honored for non-dynamic scrollable cursors
(QRYATTSNS not equal to QRYSNSDYN) and for non-scrollable cursors conforming to
the limited block query protocol. >>> The target server fetches no more than
the requested number of rows. <<< It may fetch fewer rows if it is restricted by
extra query block limits, or if a fetch operation results in a negative SQLSTATE
or an SQLSTATE of 02000.
<end quote>
Based on that quote, it seems like the server should not be returning rows if
qryrowset is 0. On the other hand, when I lookup QRYROWSET in the DDM manual,
there are a bunch of very involved definitions of what the value means in
different situations--and to be quite frank, I have no idea what any of it means.
So if anyone out there knows the answer to this question, or else can make some
sense of the "QRYROWSET" definitions in the DDM manual, I would appreciate some
feedback/direction.
If this isn't something that we can resolve on the server side, this could
result in *regressed* behavior for users who currently use IBM DB2 ODBC driver
against Apache Derby 10.1--i.e. when they upgrade to 10.2 their apps might start
returning extra rows.
I think the server-side change to not pre-fetch for qryrowset should be fairly
straightforward (based on what little knowledge I have of this area :), but the
question remains: what does DRDA protocol say about it?
Any comments/suggestions/answers out there?
Thanks much,
Army
