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.

External Stored Procedures

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.

External Stored Procedures versus SQL PL

The reasons for writing a stored procedure in a language other than SQL PL are similar to those for User-defined stored procedures:

• The stored procedure functionality required to do either text or mathematical computations are not available in SQL PL.
   
• Speed of execution is critical and there are a high number of computations required in the stored procedure.
   
• Some SQL is not supported in SQL PL

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.

External Stored Procedures and SCHEMAs

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.

SQL PL 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:

[1] CREATE PROCEDURE name(IN | OUT | INOUT arg1 type1,
                          IN | OUT | INOUT arg2 type2, …)
[2]  LANGUAGE SQL CONTAINS SQL
[3]  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.

[View:/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-06/2677.TOAD_5F00_499.gif:0:0]

The following steps describe how to use Toad for DB2 to create an SQL or external stored procedure.

To create a procedure:

1. Select Procedures in the Database Browser or Object Palette.

2. Click Create Procedure
   [View:/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-06/OM11B_5F00_517.gif:0:0]

   or

   Right-click within the Data Browser Objects panel or the Object Palette, and select Create Procedure.

3. 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.

4. Click Execute SQL Script
   [View:/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-06/OM11B_5F00_518.gif:0:0]

Sample Procedure

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

Questions?

Start a discussion about this blog in the Toad World® forum, Toad for IBM DB2 . 

Help your colleagues

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!