Suryanarayana.tadala
New User
Joined: 03 Nov 2005
Posts: 43
Location: St.Louis
|
|
|
|
? They allow you to encapsulate code. In other words, the database operation appears once, in the stored procedure, not multiple times throughout your application source. This improves debugging as well as maintainability.
? Changes to the database schema affect your source code in only one place, the stored procedure. Any schema changes then become a DBA task rather than a wholesale code revision.
? Since the stored procedures reside on the server, you can set tighter security restrictions on the client space, saving more trusted database permissions for the well-protected stored procedures themselves.
? Since stored procedures are compiled and stored outside the application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data, that you would avoid using in scripts or remote calls.
? Using stored procedures greatly reduces network traffic.
As a further illustration of this last point, suppose you want to update a customer record but you're not sure if the record even exists. One way is (a) to SELECT the record to see if the customer exists, (b) to UPDATE the record if it does, and (c) to INSERT a new record if it does not.
If you just put a series of SQL statements in your client code, each line is executed by sending a message over the network to the server, usually getting a response in return. But a stored procedure resides on the server. When called from the client application, it executes on the server and only has to respond when returning the final result set to the client, saving lots of back-and-forth traffic.
One other benefit you'll find with DB2 stored procedures in particular, the distinction of which will be explained in but a moment, is the Development Tooling support. The Enterprise edition of DB2 UDB (Universal Database) ships with a set of developer tools that allow for fast, reliable creation of stored procedures in both SQL and Java. Using the Wizards and Query Builders, you can create complex procedures without typing a word of code. |
|
