Section 7.14. Stored Procedures

7.14. Stored Procedures

Stored procedures are programs whose executable binaries reside at the database server. They serve as subroutines to calling applications, and they normally wrap multiple SQL statements with flow logic. Figure 7.27 depicts a situation in which stored procedures are useful.

Figure 7.27. Reducing network traffic by using stored procedures

In the figure, Program 1 and stored procedure mysp execute the same set of SQL statements. Program 1, however, does not perform as well as Program 2 because of the extra overhead of sending each SQL statement through the network and waiting for its return. On the other hand, Program 2 only needs to call the stored procedure mysp once and then wait for its return. Because mysp performs all the SQL statements within the database server, there is minimal network overhead.

Besides improving response time for applications running on a different server than the database server, stored procedures also provide a central location to store database application logic. This allows for a single place to maintain your code.

You can write stored procedures in several languages, such as C, Java, and SQL PL. SQL PL procedures are the most popular ones because they are easy to learn, provide very good performance, and are very compatible across the DB2 platforms, including DB2 for z/OS and DB2 for iSeries.

To create a stored procedure in the database, use the CREATE PROCEDURE statement. Stored procedures that do not use the SQL PL language are known as external procedures. For this type of procedure, the CREATE PROCEDURE statement simply registers the procedure to DB2. The executable code is normally kept under the sqllib\function\routine subdirectory.

In the case of SQL PL stored procedures, the source code is included with the CREATE PROCEDURE statement. Moreover, executing the CREATE PROCEDURE statement will compile the code, bind the SQL statements, and create the necessary packages.

NOTE

Prior to Version 8.2, a C/C++ compiler was required to create SQL PL stored procedures because these procedures were first converted to the C language. Version 8.2 does not require this compiler. DB2's engine performs the preparation/compilation of the stored procedure without any other requirement.

The following is an example of an SQL PL stored procedure created in the database sample (which is provided with DB2).

 CREATE PROCEDURE CSMMGR.NEW_SALARY (IN  p_empno   CHAR(6),                                     OUT p_empName VARCHAR(30) )        LANGUAGE SQL --------------------------------------------------------------------- -- SQL Stored Procedure used to update the salary of an employee --------------------------------------------------------------------- P1: BEGIN   DECLARE v_firstName VARCHAR(12);   DECLARE v_lastName  VARCHAR(15);   UPDATE employee SET salary = salary * 1.05          WHERE empno = p_empno;   SELECT lastname, firstnme INTO v_lastName, v_firstName     FROM employee WHERE empno = p_empno;   SET p_empName = v_lastName || ', ' || v_firstName; END P1 

In this example, the procedure name is CSMMGR.NEW_SALARY. This procedure takes an input parameter p_empno and an output parameter p_empName. The procedure will increase by 5% the value in the salary column of table employee for the employee with employee number p_empno. It will then return the name of the employee who received the increase in the format lastname, firstname. Figure 7.28 shows the Development Center tool used to develop the procedure. At the bottom of the figure, you can see the result of its execution.

Figure 7.28. Using the Development Center tool to develop, test, and run the CSMMGR.NEW_SALARY stored procedure

NOTE

We recommend using the Development Center tool to develop, debug, and test your SQL PL stored procedures. From the tool you can also drop and alter procedures.

NOTE

We recommend the book DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS by Zamil Janmohamed, Clara Liu, Drew Bradstock, Raul Chong, Michael Gao, Fraser McArthur, and Paul Yip for a detailed explanation of the SQL PL language.

To change the properties of your stored procedures, you can use the ALTER PROCEDURE statement. To drop a store procedure, use the DROP PROCEDURE statement.

Stored procedures are also classified as fenced or unfenced. A fenced stored procedure runs in a different address space than the DB2 engine. This guarantees that a failure from the procedure will not corrupt the DB2 engine itself. In Linux and UNIX, a fenced user needs to be created to work with fenced stored procedures. Refer to Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases, for details.

An unfenced stored procedure runs in the same address space as the DB2 engine. In terms of performance, unfenced stored procedures run faster than fenced ones; however, there is a risk that unfenced procedures may corrupt DB2 information, so you should make sure to test these procedures thoroughly.

NOTE

SQL PL stored procedures can run only unfenced.