Mar 27, 2013 12:08:00 PM by Steve Hilker
DB2 allows stored procedures to be developed in a variety of ways. Stored Procedures can be based on a programming language such as C, C++, Java, or SQL PL. Stored Procedure creation and registration is very similar to User-defined functions.
There are two different types of stored procedures. It takes significantly more work to create an external stored procedure than one based on SQL PL. All external stored procedures need to written and compiled using one of the supported programming languages. Stored procedures, once written and generated as dynamically loadable libraries or classes, must be registered with the database. The object code is placed into a library that DB2 has access to, and then the CREATE PROCEDURE command is used to catalog the stored procedure into the database.
Below is an example of a stored procedure being registered using the CREATE PROCEDURE statement:
CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 NOT FENCED LANGUAGE C PARAMETER STYLE GENERAL
The stored procedure ASSEMBLY_PARTS would need to be compiled and placed into a library that is accessible by DB2 at run time. Once the stored procedure has been registered, it can be called from an application:
CALL ASSEMBLY_PARTS(NO_IN, COUNT_PARTS, COST);
In addition to user-defined scalar stored procedures, DB2 also allows external routines to return result sets. These result sets are handled like cursors for a prepared statement. The only difference is that the result set has already been opened on your behalf by the stored procedure. More information on External Routines can be found in the DB2 documentation.
The reasons for writing a stored procedure in a language other than SQL PL are similar to those for User-defined stored procedures:
Under normal circumstances, a native SQL PL stored procedure will outperform a stored procedure built in C or Java. However, in very heavy, compute-intensive routines, using external programs may be faster.
A distinct disadvantage of an external stored procedure is the dependency on the compiler and lack of portability between platforms. A stored procedure written and compiled on a Windows platform must be moved and recompiled on a different (non-Windows) platform.
The major advantage of SQL PL-based stored procedures is the ability to quickly write, debug, and implement a stored procedure, along with the portability to any other distributed DB2 platform. If a routine can be easily developed in SQL PL, the effort to write it in an external language may not be economical.
Stored procedures are like any other database object in DB2 in that each stored procedure has a schema name associated with it. Ideally, each stored procedure is fully qualified when it is called. However, this can be difficult to remember and can limit the flexibility of SQL queries.
The alternative is to use the special register CURRENT PATH. DB2 uses this path to resolve unqualified stored procedure references. The path in this case is not a list of directories, but a list of schema names such as "SYSIBM", "SYSFUN" or "SAMPLE".
An application would set this special register at initialization to point to the proper libraries, and then all stored procedure calls would go to the specified libraries. One use for this is to have a routine use the stored procedures in the "TEST" library during development, and then switch to the "PRODUCTION" library when testing is complete. This eliminates the need to change the high-level schema qualifier for the stored procedures.
The previous examples referred to stored procedures that used a programming language such as C, C++, or Java to create the logic associated with the stored procedure. An alternative method of creating these stored procedures is through the use of SQL statements.
CREATE PROCEDURE CREATE_T_EMP() LANGUAGE SQL BEGIN DECLARE EOF INT DEFAULT 0; DECLARE STMT VARCHAR(200); DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1; EXECUTE IMMEDIATE 'DROP TABLE T_EMP'; SET STMT = 'CREATE TABLE T_EMP(EMP CHAR(6), SALARY INT)'; EXECUTE IMMEDIATE STMT; END
The CREATE PROCEDURE statement allows the use of SQL PL statements in the routine body. The CREATE_T_EMP stored procedure drops the T_EMP table and recreates it for further processing. The simplified structure of the CREATE PROCEDURE command is:
 CREATE PROCEDURE name(IN | OUT | INOUT arg1 type1, IN | OUT | INOUT arg2 type2, …)  LANGUAGE SQL CONTAINS SQL  BEGIN … statements … END
The sections of the CREATE PROCEDURE are discussed below.
1. CREATE PROCEDURE name(…)
The initial section of the CREATE PROCEDURE statement names the stored procedure and defines what the names and data types of the arguments will be. Each argument to the stored procedure must be defined including whether it is an input variable (IN), output variable (OUT), or both (INOUT). There is no notion of optional arguments within a stored procedure. The CREATE PROCEDURE command does not allow user-defined types to be used as arguments to the stored procedures.
2. LANGUAGE SQL CONTAINS SQL
A mandatory clause that is required as part of the stored procedures definition. The options can be READ SQL DATA, CONTAINS SQL, or MODIFIES SQL DATA. There are additional options regarding result sets that are covered in the DB2 documentation on the command.
3. BEGIN .. END block
All of the logic for a stored procedure is found within the BEGIN/END block.
The following steps describe how to use Toad for DB2 to create an SQL or external stored procedure.
To create a procedure:
Click Create Procedure
Right-click within the Data Browser Objects panel or the Object Palette, and select Create Procedure.
Modify the following template in the Editor window:
-- Procedure template CREATE PROCEDURE <proc-name> ( IN | OUT <param_name> <param_type>) RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE END
Note: Toad uses a semicolon as the default delimiter. You can modify the default delimiter under Tools | Options | Database | DB2.
Click Execute SQL Script
The following is a sample procedure named PROCEDURE1:
CREATE PROCEDURE DB2ADMIN.PROCEDURE1 ( OUT var0 VARCHAR(4000) ) SPECIFIC DB2ADMIN.PROCEDURE1 DYNAMIC RESULT SETS 1 ------------------------------------------------------------ -- SQL Stored Procedure -- var0 ------------------------------------------------------------ P1: BEGIN -- Declare variable DECLARE var0_TMP VARCHAR(4000) DEFAULT ' '; -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT PROCSCHEMA, PROCNAMEFROM SYSCAT.PROCEDURES; -- Cursor left open for client application OPEN cursor1; SET var0 = var0_TMP; END P1
Try Toad for IBM DB2 for free
Are you in a trial now or just learning about Toad for IBM DB2? Find out which edition is right for you with our Toad for IBM DB2 functional matrix.
Like what you’ve learned so far about Toad for IBM DB2, why not try it for free for 30 days?
Learn more about Toad for IBM DB2 so you can spend less time managing DB2 and more time innovating.
Data sheet: Toad for IBM DB2
Start a discussion about this blog in the Toad World® forum, Toad for IBM DB2 .
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Written by Steve Hilker
Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.
In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.