I inherited a UDB database to manage recently. It talks to a Websphere
application coded in Java. The developers previously managed the DBA
activities themselves before. You can imagine some of the messes that
I've encountered relating to design and configuration.
One of the sticky points that I'm dealing with is the use of Java
stored procedures. Someone decided that all database access should be
in the form of a Java stored procs. The main problem I'm having with
this, is the migration of procs from developement, to test, to
production. The problems revolve around who created the SP orignally
(ownership), the existence of previous .jar files, not being able to
see source for a given SP, and several others. We've managed to throw
together kludges to make things work, but the environment is not
After having looked at the SPs, I began to question their utility.
This is where my question comes in. All of the procs are fairly
simple, "ADD_TRANSACTION", "ADD_USER", "GET_TRANSACTION", "GET_USER",
etc. They all do simple inserts, updates, deletes and selects. No
complex business logic. No great amounts of server-side processing. Is
there any benefit of designing an application like this? Is the
overhead of calling the stored proc mitigated by executing the proc
with DB2's JVM? Given the problems that I constantly have managing
them, I am wondering if I should propose removing the data
manipulation from the Java stored procs and put it back in the
application. Does anyone have any opinions or metrics that would help
me to sway my decision one way or the other?