Java Store Procedures Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

esmith2112 - 27 Sep 2005 16:19 GMT

We all of a sudden find ourselves in dire straits, because we have one
of those mysteries where everything used to work, and apparently, all
of a sudden everything went to pot just in time for a major software
release. All of our Java stored procedures have developed some bad
behavior and we've exhausted our resources in figuring this out.

Symptoms:

1. Store procedures have stopped returning result sets. Whereas before,
it would return the column headers with zero rows returned, it now
returns the message: No results returned from the stored procedure.
It's as if the proc is never actually executing the query.

2. Procedures have started to complain that that they've never heard of
various objects. We get SQL0204 errors saying that a nickname (that for
sure exists) is undefined.

I turn to the group because we have been unable to upgrade to the
current release of DB2 software due to legacy compatibility issues and
find ourselves out of support. I've tried the archives for the group
and lots of web searches with no definitive hits. If anyone has any
clue I'd love to hear from you

Environment:

DB2 7.2 FP 11
AIX 4.3
Federated database with 2 servers defined.

Thanks in advance,
Evan

Reply to this Message

Rhino - 30 Sep 2005 00:10 GMT

> We all of a sudden find ourselves in dire straits, because we have one
> of those mysteries where everything used to work, and apparently, all
[quoted text clipped - 27 lines]
> Thanks in advance,
> Evan

The only way to be really sure about what is happening is to examine at
least one or two of the stored procedures thoroughly. Make sure that the
tables they are trying to read actually exist; it's not enough to assume
they exist because you don't remember explicitly dropping them. Make sure
that the desired tables actually contain the rows you expect to return from
the stored procedure; perhaps someone (inadvertently?) deleted the rows you
expected to find.

Run the code in the Java stored procedure debugger and step through it a
statement at a time, making sure that every statement does what it should
do. I think you will soon see exactly what is wrong if you take this
approach. Now, that's assuming that you can get the debugger to work; I
never had much luck with it in DB2 V7.2. However, if you can't get it to
work, remember that
Java stored procedures cannot do screen I/O but they _can_ do file I/O. I
found it very educational to write lines to an external flat file to debug
my programs. It's not as convenient as a proper online debugger but it
works.

Good luck!

Rhino

Reply to this Message