Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003

    Question Unanswered: When not use triggers, check constraints and stored procedures

    i would like to hear ideas about this subject.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    For Triggers and Check Constraints do not use them if you do not need them. They add to the workload of whatever table and / or columns are involved. Stored procedures are only invoked when called for so they do not add any additional overhead, meaning that there is no indirect processing with Stored procedures. In my opinion, Stored procedures should only be used if they consolidate database logic.


  3. #3
    Join Date
    Jun 2003
    Do you know any literature that talk about disadvantages of those things?
    or even give any objective information (response times etc.).
    I find only advantages (IBM books).
    do you know any performance book/article that talks about those stuff?
    i just don't find any...


  4. #4
    Join Date
    May 2003

    1. Increases data integrity by centralizing the logic and enforcing the rules in one place, and not relying on one or more application programs to enforce data integrity.

    2. There are important performance benefits if the application runs on a remote client and one can eliminate multiple communications to the database.

    For example without these features one might do the following:

    1. Client submits SQL to server
    2. Client gets result back and checks return code
    3. Clients submits another SQL to server
    4. Client gets result back and checks return code
    5. etc.

    But the following is much better in terms of performance because there is only one communication between remote client and server:

    1. Client invokes stored procedure or single SQL statement
    2. DBMS performs multiple SQL statements
    3. DBMS sends back return code and parms to client


    1. Using these features usually makes the application less portable to other vendor databases, and to a lesser extent even less portable other DB2 platforms.

    2. Makes migration from test to production, etc, a little more complex (especially for stored procedures).

    3. It makes the application programmer more dependent on the DBA and requires a high degree of coordination, which can be a problem in some environments, but not all.

    4. Performs worse if the logic is redundantly executed by the application and the database (which sometimes happens due to ignorance or poor design).

    5. Slightly less flexible than application written SQL code (because only a subset of SQL statements is allowed).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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