Application Best Practices w/DB2

Application Best Practices w/DB2

Post by Mike L. Be » Sat, 28 Jun 2003 02:16:33



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

 
 
 

Application Best Practices w/DB2

Post by Blair Kenneth Adamach » Sat, 28 Jun 2003 03:19:49


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

> 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


 
 
 

Application Best Practices w/DB2

Post by Sean McKeoug » Sat, 28 Jun 2003 04:16:49


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.


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


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

 
 
 

1. Best practices in updating application database schema

Hi,

1.My application uses sql server 7/2000, I release new
versions of the app quite often and they are accompanied
with db changes (usually adding new fields and sometimes
adding triggers and SPs). What is the best way to send db
updates to customers?

2. How to get description of tables and fields in sql
server programmatically or using queries?
I mean field names and  attributes, in order to compare db
schema to certain scripts and update the schema if
necessary.

Thank you

Vadim

2. DTS and Import

3. good (best) practice question

4. Storing Ranges of Values: Help Requested

5. applications connect to db2 without db2 client?

6. US-CA-ORACLE FUNCTIONAL (ERP

7. Best and Worst Development Practices -- Training in NYC and DC

8. Welcome to the fastest search engine!

9. Best Practices question

10. best data design practice on performance/size

11. best practices for physical data modelling for SQLserver?

12. IIS / SQL 7.0 authentication - best practices?

13. Data Cleansing Best Practice Quest