Chapter 15. Using DB2 Stored Procedures

IN THIS CHAPTER

• What Is a Stored Procedure?

• Implementing DB2 Stored Procedures

• The Procedural DBA

• Procedural SQL

• DB2 Development Center

In the distant past, DBMS products were designed only to manage and store data in an optimal manner. Although this core capability is still required of modern DBMS products, the purview of the DBMS is no longer limited just to data. With the advent of client/server computing and active databases, procedural business logic also is being stored and managed by the DBMS. DB2 is maturing and gaining more functionality. The clear trend is that more and more procedural logic is being stored in the DBMS. DB2 stored procedures enable you to write in-depth application programs and use them to extend the functionality of DB2.

In Chapter 8, "Using DB2 Triggers for Integrity," we examined triggers, one example of business logic that is stored in DB2 databases. Another example of business logic stored in the database is user-defined functions, which we explored in Chapter 4, "Using DB2 User -Defined Functions and Data Types." In this chapter, we will learn about stored procedures including what they are, when to use them, and guidelines for proper implementation.

NOTE

One example of logic being stored in the DBMS is the exit routine. DB2 has supported exit routines for many years , whereas stored procedure (V4), trigger (V6), and UDF (V6) support is more recent.

An exit routine, such as an EDITPROC or VALIDPROC , is coded in Assembler language. This code is then attached to a specific database object and is executed at a specified time, such as when data is inserted or modified. Exit routines have been available in DB2 for a long time; typically, the DBA is responsible for coding and maintaining them. Exit routines, however, are primitive when compared with the procedural logic support provided by a modern RDBMS.

The most popular RDBMS products support additional forms of database-administered procedural logic. Triggers, UDFs, and stored procedures are examples of this phenomenon .