By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

Result Sets Mismatch?

P: n/a
Scratching my head on an issue that has been plaguing us ever since we
upgraded from version to version 8. Our db2diag file gets inundated
with messages that take the form:

MESSAGE : sqlriInvokeInvoker ignored because of prior condition, probe
145
DATA #1 : Hexdump, 136 bytes
0x2004FD54 : 5351 4C43 4120 2020 0000 0088 0000 01D0
SQLCA ........
0x2004FD64 : 0005 FF31 FF30 FF20 2020 2020 2020 2020 ...
1.0.
0x2004FD74 : 2020 2020 2020 2020 2020 2020 2020
2020
0x2004FD84 : 2020 2020 2020 2020 2020 2020 2020
2020
0x2004FD94 : 2020 2020 2020 2020 2020 2020 2020
2020
0x2004FDA4 : 2020 2020 2020 2020 5351 4C43 534D 4342
SQLCSMCB
0x2004FDB4 : 893E 0019 0000 0019 0000 0000 0000
0000 .>..............
0x2004FDC4 : 0000 0000 0000 0000 2020 2020 2020
2020 ........
0x2004FDD4 : 2020 2020 2020 2020

Opened PMR with IBM and they helped me decode this message as a
warning:

SQL0464W Procedure "<procedure-name>" returned
"<generated-nbr-results>" query result sets, which
exceeds the defined limit "<max-nbr-results>".

(01D0 in first line of hex dump is hex for 464)

They told me that I was on my own.

Through some detective work, we were able to correlate the timestamps
from the diag log entries with output from an event monitor for
statements to a couple Java stored procedures. The only thing is that
neither of them returns any result sets. I've validated that
everything is defined as having zero result sets. I've dropped and
recompiled all the "offending" procedures without any change.

While all the SQL contained in the procs does indeed get executed
correctly, we are inundated in "noise" in the db2diag.log file. We
currently have diaglevel set to 2 to minimize this, but this prevents
us from seeing any other errors that we want to be able to track by
having it set at 3.

Anyone seen anything like this before or have any idea what's going
on?

We are running 8.2 FP9 on AIX 5.3.

Thanks,

Evan

Mar 7 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
SQL0464W Procedure "<procedure-name>" returned
"<generated-nbr-results>" query result sets, which
exceeds the defined limit "<max-nbr-results>".
Through some detective work, we were able to correlate the timestamps
from the diag log entries with output from an event monitor for
statements to a couple Java stored procedures. The only thing is that
neither of them returns any result sets. I've validated that
everything is defined as having zero result sets. I've dropped and
recompiled all the "offending" procedures without any change.

While all the SQL contained in the procs does indeed get executed
correctly, we are inundated in "noise" in the db2diag.log file. We
currently have diaglevel set to 2 to minimize this, but this prevents
us from seeing any other errors that we want to be able to track by
having it set at 3.
Evan

Is there any chance that one or more of the procedures are ending with
cursors left open ?
How do you verify that all the procedures exit at the "right" places ?
Mar 7 '07 #2

P: n/a
On Mar 7, 12:57 pm, "mike" <_lin...@yahoo.comwrote:
Is there any chance that one or more of the procedures are ending with
cursors left open ?
How do you verify that all the procedures exit at the "right" places ?

The procedures are lengthy but a cursory glance at my largest offender
contains a "finally" block in the Java code that explicitly says:
rs.close();
rs = null;

Can a Java stored procedure exit without performing the finally
block?

I can't seem to find any detailed docs on the interaction between DB2
server and the local JVM where procs get executed. It would be
interesting to discover what goes on.

Evan

Mar 7 '07 #3

P: n/a
esmith2112 wrote:
On Mar 7, 12:57 pm, "mike" <_lin...@yahoo.comwrote:
>Is there any chance that one or more of the procedures are ending with
cursors left open ?
How do you verify that all the procedures exit at the "right" places ?


The procedures are lengthy but a cursory glance at my largest offender
contains a "finally" block in the Java code that explicitly says:
rs.close();
rs = null;

Can a Java stored procedure exit without performing the finally
block?

I can't seem to find any detailed docs on the interaction between DB2
server and the local JVM where procs get executed. It would be
interesting to discover what goes on.
In SQL PL only cursors specified as WITH RETURN can be an issue. All
other cursors are implicitly closed when the scope (BEGIN..END) is left.
Could it be that you have a nested SQL Procedure where the RESULTSETs
clause doesn't jibe with the CURSOR WITH RETURN?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.