SEO

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
pretty.

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?

Thanks,
Mike

It seems the main advantage would be if multiple applications called the
same stored procedures (or this could happen in the future).

"Mike L. Bell" wrote:

> 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
> pretty.
>
> 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?
>
> Thanks,
> Mike

Yep, the main two reasons to do this would be application code
encapsulation for easy deployment of app changes (as Blair mentioned),
or for performance. For performance to be a benefit you need more that a
few sql statement per stored proc.

For performance, Java is not the best choice...I favor SQL, because
they're as fast as C, but safely run directly in the engine as of v8.

Blair Kenneth Adamache wrote:

> It seems the main advantage would be if multiple applications called the
> same stored procedures (or this could happen in the future).
>
> "Mike L. Bell" wrote:
>
>
>>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
>>pretty.
>>
>>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?
>>
>>Thanks,
>>Mike
>
>