473,287 Members | 3,181 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Java Stored Procedures in DB2 V7.2

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."
Nov 12 '05 #1
1 2308
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?

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Suresh Tri | last post by:
Hi all, I am in search of any Enterprise level Opensource Project that uses Java Stored Procedures supported by Oracle. I could not find any by googling. Can any one plese point me to any such...
7
by: Alex | last post by:
Hi all, I am trying to install a java stored procedure via the windows development centre. The linux box is running 8.1 FP4 as is the windoze platform. If I am on the linux box i can install...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
1
by: Kumar | last post by:
Hi I am trying to recreate a database under the following environments : From: Solaris with DB2UDB version 7.2 with FP 9 To: Linux with DB2UDB version 7.2 with FP 9 It will be of really a...
1
by: Alex | last post by:
Hi all, Just been dabbling with java stored procedures and I'm having problems replacing System: db2 8.1.4 on RH 7.1 linux system 1). Look at java jdbc samples as supplied with db2 and run...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
2
by: Rafael Faria | last post by:
Hi All, We are starting a large data warehousing project using DB2 8.2 on AIX. There is a direction to move any new internal development to Java and a question was raised: Would it be a good...
1
by: esmith2112 | last post by:
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...
2
by: Michael | last post by:
Running DB2 v7 UDB ("DB2 v7.1.0.93", "n031208" and "WR21333") on Windows XP, I am unable to find out why the "Build for Debug" option within Stored Procedure Builder is not enabled on Java stored...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.