Java Stored Procedures in DB2 V7.2

[Rhino]

I am trying to get a sense of requirements and best practices for Java
stored procedures in DB2 V7.2 for Windows.

1. Is it required or recommended that any of the following be closed before
leaving the stored procedure: open Statement and PreparedStatement objects;
open ResultSet objects; open JDBC connections? Although the stored procedure
will still run succesfully if these open objects and connections are not
closed, are there any negative performance or other consequences from not
closing these things manually?

2. What is the best way to do error handling? For example, if a
PreparedStatement object's executeQuery() method fails for some reason, what
should be returned to the client? I think that the procedure should pass
back at least the SQLCode, SQLState, and SQLMessage and would strongly
prefer to pass back a programmer-written message identifying where the error
took place, e.g. during executeQuery() of statement such-and-such. Do best
practices dictate that anything more, such as a stacktrace, be passed back
as well or that information of this kind be written to an external file?

3. Is there a practical limit on how many parameters and/or result sets can
be passed back to the client? Will there be any performance or other
advantages in limiting the number of parameters and result sets to a
particular number? If yes, what is that number? I'm just writing small
stored procedures for now but I can easily imagine writing much bigger ones.
However, I don't want to overdo things and make a stored procedure the
solution to every problem.

4. I understand that stored procedures can access other programs on the
database server. That gives me thoughts of generating faxes or PDFs,
exporting data to spreadsheets, etc. etc. What sorts of things have people
done that take advantage of this capability? Can anyone point me to examples
of these uses?

--

Rhino
---
rhino1 AT sympatico DOT ca
"If you want the best seat in the house, you'll have to move the cat."

[Sean McKeough]

Comments to 2 of your points inline:

Rhino wrote:

I am trying to get a sense of requirements and best practices for Java
stored procedures in DB2 V7.2 for Windows.

1. Is it required or recommended that any of the following be closed before
leaving the stored procedure: open Statement and PreparedStatement objects;
open ResultSet objects; open JDBC connections? Although the stored procedure
will still run succesfully if these open objects and connections are not
closed, are there any negative performance or other consequences from not
closing these things manually? If a cursor is actually meant to be a result set, then you must not
close it (or its statement) inside the sp...if you do, no result set
will make it back to the client. I think JDBC disconnect is a noop when
issued from within the SP...

The same work will get done if you close these things manually, or leave
them for cleanup. The only issue is when they get done (in v7, gc in a
db2dari process is syncronous, so a later sps performance may be
affected by an earlier sp being lazy when cleaning up). In v8, gc is
asyncronous, and in general you run in a threaded process, so leaving
more records for manual cleanup could hurt you more...
2. What is the best way to do error handling? For example, if a
PreparedStatement object's executeQuery() method fails for some reason, what
should be returned to the client? I think that the procedure should pass
back at least the SQLCode, SQLState, and SQLMessage and would strongly
prefer to pass back a programmer-written message identifying where the error
took place, e.g. during executeQuery() of statement such-and-such. Do best
practices dictate that anything more, such as a stacktrace, be passed back
as well or that information of this kind be written to an external file?

3. Is there a practical limit on how many parameters and/or result sets can
be passed back to the client? Will there be any performance or other
advantages in limiting the number of parameters and result sets to a
particular number? If yes, what is that number? I'm just writing small
stored procedures for now but I can easily imagine writing much bigger ones.
However, I don't want to overdo things and make a stored procedure the
solution to every problem. We have a limit of 90 paramters for Java routines...I think the result
set limit is 254. :-)

From a performance perspective, fewer parms are better...in particular
for String objects, as each parm must be marshelled/unmarshalled using
JNI, which is an expensive operation. Numerics are faster. Of course,
the more SQL you do in the SP, the less interesting the parm marshalling
overhead becomes.

4. I understand that stored procedures can access other programs on the
database server. That gives me thoughts of generating faxes or PDFs,
exporting data to spreadsheets, etc. etc. What sorts of things have people
done that take advantage of this capability? Can anyone point me to examples
of these uses?