Hi,
i would like to hear ideas about this subject.
Thanks!
maayan.

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

Andy

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

Thanks,
maayan.

Advantages

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

Disadvantages

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