A Brief Introduction to Stored Procedures, Triggers, and Functions

SQL PL is usually associated with stored procedures and incorrectly referred to as "DB2 Stored Procedure Language." The proper meaning of DB2 SQL PL is "DB2 SQL Procedural Language." Furthermore, SQL PL is not used just for stored procedures. Database triggers, user-defined functions (UDFs), and dynamic compound SQL are also developed using this language, and this book will show you how.

Stored procedures, triggers, and functions are a class of objects called database application objects. Application objects encapsulate application logic at the database server rather than in application-level code. Use of application objects help reduce network traffic. In the case of stored procedures, for example, only the original request and the final output need to be transmitted between the client and the server.

Triggers are useful for enforcing business rules consistently and automatically across many applications that share a database.

Functions are useful for simplifying application development by encapsulating commonly used calculations or data lookups.

All of these objects help improve application runtime performance and can take advantage of the larger number of CPUs and disks that typically reside on a database server.

Use of database application objects is very popular because of their many benefits:

• Improved manageability. Application objects are modular and can be moved from database to database.

• Clear division of roles between DBA and application developer. The DBA knows the data model best. The application developer knows application interfaces best. Complex business logic can be encapsulated in database application objects. Application developers only need to know which procedures or functions to call.

• Increased performance. Keeping business logic in stored procedures residing on the server will help improve the performance of most data-driven applications. Instead of an application having to send data back and forth across a network for every SQL statement and cursor, a single call can be made with results returned at the end just once.

There are almost always opportunities to improve application performance using database application objects. Newer applications using three-tier or (n-tier) architectures were designed to reduce the cost of creating and maintaining applications. Practical experience, however, has shown that the performance of keeping all business logic in the application-server tier can have a large performance impact.

We have consistently witnessed that applications can benefit significantly by moving data-intensive logic from the middle tier back into the database tier. Application developers have discovered that while moving all business logic to the middle tier makes for very clean design, some classes of data processing should remain at the database tier for even reasonable performance. Use of SQL procedures, functions, and triggers has therefore continued to gain popularity in modern applications.