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