Professional Web Applications Themes

Application Best Practices w/DB2 - IBM DB2

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

  1. #1

    Default Application Best Practices w/DB2

    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
    Mike L. Bell Guest

  2. #2

    Default Re: Application Best Practices w/DB2

    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
    Blair Kenneth Adamache Guest

  3. #3

    Default Re: Application Best Practices w/DB2

    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
    >
    >
    Sean McKeough Guest

Similar Threads

  1. Best Practices?
    By mikeap in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: September 30th, 02:35 AM
  2. application.cfc best practices
    By Gib Sllab in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 23rd, 09:46 PM
  3. Databinding Best Practices
    By Ryan in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 9th, 04:56 PM
  4. Best practices
    By ring in forum ASP.NET Web Services
    Replies: 2
    Last Post: February 17th, 01:01 PM
  5. Some PHP best practices
    By Rutger Claes in forum PHP Development
    Replies: 0
    Last Post: July 4th, 08:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139