What Is a Stored Procedure?

Stored procedures are specialized programs that are executed under the control of the relational database management system. You can think of stored procedures as similar to other database objects such as tables, views, and indexes because they are managed and controlled by the RDBMS. But you can think of stored procedures as similar to application programs, too, because they are coded using a procedural programming language. Depending on the particular implementation, stored procedures can also physically reside in the DBMS. However, a stored procedure is not "physically" associated with any other object in the database. It can access and/or modify data in one or more tables. Basically, you can think of stored procedures as "programs" that "live" in the RDBMS.

A stored procedure must be directly and explicitly invoked before it can be executed. In other words, stored procedures are not event-driven. Contrast this concept with the concept of triggers, which are event-driven and never explicitly called. Instead, triggers are automatically executed (sometimes referred to as " fired ") by the RDBMS as the result of an action. Stored procedures are never automatically invoked.

DB2 has provided stored procedure support since V4, and IBM continues to enhance the functionality of stored procedures with each successive DB2 release. The major motivating reason for stored procedure support is to move SQL code off the client and on the database server. Implementing stored procedures can result in less overhead than alternative development methods because one client request can invoke multiple SQL statements.

DB2's Stored Procedure Implementation

DB2's implementation of stored procedures is a bit different from the stored procedure support available using other DBMS products. For example, both Microsoft SQL Server and Oracle require you to code stored procedures using procedural extensions to SQL: Microsoft provides Transact-SQL, and Oracle provides PL/SQL. DB2, on the other hand, enables you to write stored procedures using traditional programming languages. You can use any LE/370-supported language to code stored procedures. The supported languages are Assembler, C, C++, COBOL, OO COBOL, and PL/I. Additionally, IBM provides support for REXX and Java stored procedures, as well as their own extended SQL Procedure Language (procedural SQL) for developing stored procedures. A description of the procedural SQL option is provided in the "Procedural SQL" section later in this chapter.

NOTE

The language of the calling program can be different from the language used to write the stored procedure. For example, a COBOL program can CALL a C stored procedure.

DB2 stored procedures can issue both static and dynamic SQL statements with the exception SET CURRENT SQLID . As of V7, stored procedures can issue COMMIT and ROLLBACK statements. But keep in mind that these statements apply to the entire unit of work, not just the stored procedure code. A COMMIT in a stored procedure will commit all of the work done in the stored procedure, as well as all of the work done in the calling program since the last COMMIT . The same goes for ROLLBACK . However, COMMIT and ROLLBACK cannot be issued in a stored procedure under the following conditions:

• When the stored procedure is called by a trigger or user -defined function

• When the stored procedure is called by a client that uses two-phase COMMIT processing

• When the client program uses a type-2 connection to connect to the remote server that houses the stored procedure

CAUTION

Because it will become difficult to manage and control what exactly is being committed (or rolled back), it is best not to issue COMMIT and ROLLBACK statements in your stored procedures if you can avoid it.

Additionally, a stored procedure can issue DB2 commands and IFI (Instrumentation Facility Interface) calls. Stored procedures can access flat files, VSAM files, and other files, as well as DB2 tables. Additionally, stored procedures can access resources in CICS, IMS, and other MVS address spaces, but no commit coordination exists between DB2 and the other resources.

NOTE

DB2 stored procedures can connect to an IMS DBCTL or IMS DB/DC system using IMS Open Database Access (ODBA) support. The stored procedure can issue DL/I calls to access IMS databases. IMS ODBA supports the use of OS/390 RRSAF for syncpoint control of DB2 and IMS resources. Stored procedures that use ODBA can run only in WLM-established stored procedures address spaces, not a DB2-established address space.

DB2 stored procedures run under the control of the Work Load Manager (WLM). WLM effectively fences off user-developed code from running in DB2 address spaces with IBM-developed code. This layer of protection is useful to prohibit a stored procedure from causing an entire DB2 subsystem to fail.

NOTE

When stored procedures were first introduced, a DB2-managed address space known as the stored procedure address space , or SPAS for short, was used to fence stored procedures from DB2. Although the SPAS is still supported as of V8, IBM is phasing it out and it will be eliminated completely in a future version of DB2. Start migrating your DB2-managed stored procedures from the SPAS to WLM as soon as possible.

To further clarify, you cannot create a DB2-managed stored procedure in V8. V8 only tolerates existing DB2 managed stored procedures. Even altering an existing SP will force it to be WLM managed.

Why Use Stored Procedures?

DB2 stored procedures have many potentially time-saving and useful applications. The major uses can be broken down into six categories: reuseability, consistency, data integrity, maintenance, performance, and security, as described here.

• Reuseability ” The predominant reason for using stored procedures is to promote code reuseability. Instead of replicating code on multiple servers and in multiple programs, stored procedures allow code to reside in a single place ”the database server. Stored procedures then can be called from client programs to access DB2 data. This approach is preferable to cannibalizing sections of program code for each new application system being developed. By developing a stored procedure, you can invoke the logic from multiple processes as needed, instead of rewriting it directly into each new process every time the code is required. When they are implemented wisely, stored procedures are useful for reducing the overall code maintenance effort. Because the stored procedure exists in one place, you can make changes quickly without propagating the change to multiple applications or workstations.

• Consistency ” An additional benefit of stored procedures is increased consistency. If every user with the same requirements calls the same stored procedures, the DBA can be assured that everyone is running the same code. If each user uses his or her own individual, separate code, no assurance can be given that the same logic is being used by everyone. In fact, you can be almost certain that inconsistencies will occur.

• Maintenance ” Stored procedures are particularly useful for reducing the overall code maintenance effort. Because the stored procedure exists in one place, you can make changes quickly without propagating the change to multiple workstations.

• Data Integrity ” Additionally, you can code stored procedures to support database integrity constraints. You can code column validation routines into stored procedures, which are called every time an attempt is made to modify the column data. Of course, these routines catch only planned changes that are issued through applications that use the stored procedure. Ad hoc changes are not checked. Triggers provide better capabilities for enforcing data integrity constraints, but a trigger can be coded to CALL a stored procedure.

• Performance ” Another common reason to employ stored procedures is to enhance performance. In a client/server environment, stored procedures can reduce network traffic because multiple SQL statements can be invoked with a single execution of a procedure instead of sending multiple requests across the communication lines. The diagram in Figure 15.1 depicts a call to a DB2 stored procedure. The passing of SQL and results occurs within the SPAS, instead of over the network as would be necessary without the stored procedure. Only two network requests are required: one to request that the stored procedure be run and one to pass the results back to the calling agent.

  Figure 15.1. Calling a stored procedure.

  

  
  

• Security ” You can use stored procedures to implement and simplify data security requirements. If a given group of users requires access to specific data items, you can develop a stored procedure that returns only those specific data items. You can then grant access to call the stored procedure to those users without giving them any additional authorization to the underlying objects accessed within the body of the stored procedure. Furthermore, it is more difficult to tamper with a DB2 package on the mainframe than it would be to modify DLL code on a workstation.

Stored procedures provide a myriad of other useful benefits including:

• Flexibility ” Stored procedures can issue both static and dynamic SQL statements and access DB2 and non-DB2 data.

• Ease of Training ” DB2 stored procedures can be written in traditional programming languages that application programmers already know, or in procedural SQL that is easier for DBAs to learn and utilize.

• DBMS Protection ” Stored procedures run in a separate address space from the database engine, thereby eliminating the possibility of users corrupting the DBMS installation.