Procedural SQL

The major difference between DB2's stored procedure support and the other RDBMS vendors is the manner in which the stored procedure is coded. As I mentioned at the beginning of this chapter, other popular RDBMS products require procedural dialects of SQL for stored procedure creation. Oracle uses PL/SQL and Sybase, and Microsoft SQL Server uses Transact SQL. Each of these languages is proprietary, and they cannot interoperate with one another.

As of DB2 V6, IBM supports a procedural dialect of SQL based on the ANSI standard. The IBM DB2 version of procedural SQL is called SQL procedures language , or SPL for short.

NOTE

SQL/PSM is the ANSI standard specification for developing stored procedures and routines using SQL. PSM is an acronym for Persistent Stored Modules. IBM's implementation of its SQL Stored Procedure Language is based on SQL/PSM, but is not a complete implementation of the ANSI SQL/PSM standard.

But what is procedural SQL? One of the biggest benefits derived from SQL (and relational technology in general) is the capability to operate on sets of data with a single line of code. By using a single SQL statement, you can retrieve, modify, or remove multiple rows. However, this capability also limits SQL's functionality. A procedural dialect of SQL eliminates this drawback through the addition of looping, branching, and flow of control statements. Procedural SQL has major implications on database design.

Procedural SQL will look familiar to anyone who has ever written any type of SQL or coded using any type of programming language. Typically, procedural SQL dialects contain constructs to support looping ( WHILE or REPEAT ), exiting ( LEAVE ), conditional processing ( IF...THEN...ELSE ), blocking ( BEGIN...END ), and variable definition and use.

IBM's SQL Procedure Language

Stored procedure language for creating SQL stored procedures was added after the general availability of DB2 V6.

SQL stored procedures are like other stored procedures in that the SQL stored procedure must have a name and a schema, as well as the definition of the stored procedure characteristics and the actual code for the stored procedure. The code, however, is written in SQL alone ”no 3GL program is required.

SQL stored procedures differ from external stored procedures in the way that the code is defined. SQL stored procedures include the actual SQL procedural source code in the CREATE PROCEDURE statement, whereas external stored procedures specify only the definition of the stored procedure in the CREATE PROCEDURE statement. The actual code of an external stored procedure is developed independently and is not included in the CREATE statement.

SQL stored procedures are developed entirely in IBM's SQL procedures language but must be converted to C before they can be executed. This process is described later in this chapter in the section titled "Creating SQL Stored Procedures."

The actual SQL code in the SQL stored procedure is referred to as the body of the SQL stored procedure. The body of an SQL stored procedure can include most valid SQL statements, but also extended, procedural SQL statements. The procedure body consists of a single simple or compound statement. The following statements can be included in an SQL stored procedure body.

• Most regular SQL statements can be coded in an SQL stored procedure. Some SQL statements are valid in a compound statement, but they are not valid if the SQL is the only statement in the procedure body.

• Assignment statements can be used to assign a value (or null) to an output parameter or an SQL variable. An SQL variable is defined and used only within the body of an SQL stored procedure.

• CASE statements are used to select an execution path based on the evaluation of one or more conditions. The SQL procedures language CASE statement is similar to the SQL CASE expression previously described in Chapter 1, "The Magic Words."

• IF statements can be coded to select an execution path based on conditional logic.

• The LEAVE statement transfers program control out of a loop or a block of code.

• A LOOP statement is provided to execute a single statement or grouping of statements multiple times.

• The REPEAT statement executes a single statement or group of statements until a specified condition evaluates to true.

• The WHILE statement is similar to the REPEAT statement, but it executes a single statement or group of statements while a specified condition is true.

• The RETURN statement can be used to return a status in the form of an integer value to the invoking application.

• The SIGNAL statement works in conjunction with the RETURN statement. The SIGNAL statement can be used to set the SQLSTATE to a specific value. You can also use it to specify an optional MESSAGE_TEXT , the first 70 bytes of which will be stored in the SQLERRMC field of the SQLCA. The full message text can be obtained from the MESSAGE_TEXT and MESSAGE_LENGTH fields of GET DIAGNOSTICS .

• The RESIGNAL statement enables a condition handler within an SQL procedure to raise a condition with a specific SQLSTATE and message text, or to return the same condition that activated the handler.

• Compound statements can be coded that contain one or more of any of the other SQL procedures language statements. In addition, a compound statement can contain SQL variable declarations, condition handlers, and cursor declarations. Compound statements cannot be nested.

NOTE

When coding a compound statement, you must code the component statements in the following specific order:

1. SQL variable and condition declarations

2. Cursor declarations

3. Handler declarations

4. Procedure body statements ( CASE , IF , LOOP , REPEAT , WHILE , and other SQL statements)

Sample SQL Stored Procedures

The following SQL code implements an SQL stored procedure that accepts an employee number and a rate as input. The stored procedure raises the salary of the specified employee by the specified rate. However, using an IF statement, the stored procedure also checks to make sure that no raise exceeds 50%.

 CREATE PROCEDURE UPDATE_SALARY   (IN EMPLOYEE_NUMBER CHAR(10),    IN RATE DECIMAL(6,2)) LANGUAGE SQL WLM ENVIRONMENT SAMP1 COMMIT ON RETURN YES IF RATE <= 0.50 THEN UPDATE EMP      SET SALARY = SALARY * RATE      WHERE EMPNO = EMPLOYEE_NUMBER; ELSE UPDATE EMP      SET SALARY = SALARY * 0.50      WHERE EMPNO = EMPLOYEE_NUMBER; END IF 

Another sample stored procedure follows :

 CREATE PROCEDURE PROC1(OUT NOROWS INT) LANGUAGE SQL BEGIN   DECLARE var_firstnme VARCHAR(12);   DECLARE var_midinit CHAR(1);   DECLARE var_lastname VARCHAR(15);   DECLARE at_end INT DEFAULT 0;   DECLARE not_found CONDITION FOR '02000'   DECLARE cempname CURSOR FOR     SELECT   FIRSTNME, MIDINIT, LASTNAME     FROM     EMP     ORDER BY LASTNAME;   DECLARE CONTINUE HANDLER FOR not_found SET NOROWS=1;   OPEN cempname;   FETCH cempname INTO var_firstnme, var_midinit, var_lastname;   CLOSE cempname; END 

This SQL stored procedure declares a cursor on the EMP table and fetches a row from the cursor. The condition handler is used to handle the row-not-found condition. You could code a loop construct to fetch all rows from a cursor until no more rows are found. For example,

 .   .   . fetch_loop: REPEAT   FETCH cempname INTO         var_firstnme, var_midinit, var_lastname; UNTIL SQLCODE <> 0 END REPEAT fetch_loop   .   .   . 

Of course, a similar effect could be achieved using the LOOP construct with a LEAVE statement.

Creating SQL Stored Procedures

There are three steps to creating SQL stored procedures:

1. Write the procedural SQL source statements.
   
   
2. Create the executable form of the SQL procedure.
   
   
3. Define the SQL procedure to DB2.
   
   

There are two different ways for you to accomplish these three steps to create an SQL procedure:

• Use the IBM DB2 Stored Procedure Builder to guide you through the steps of specifying the source statements for the SQL procedure, defining the SQL procedure to DB2, and preparing the SQL procedure for execution.

• Code a CREATE PROCEDURE statement for the SQL procedure. Then use JCL or DSNTPSMP to define the SQL procedure to DB2 and create an executable procedure.

NOTE

As of DB2 V8, Stored Procedure Builder is renamed to DB2 Development Center.

Using JCL to Create SQL Stored Procedures

Use the following steps to prepare an SQL procedure using JCL. This JCL is similar to the JCL used for program preparation presented in Chapter 13, "Program Preparation," with an additional step to generate C source code.

First, preprocess the CREATE PROCEDURE statement using the DSNHPSM program. The output from this step is

• A C language source program

• A CREATE PROCEDURE statement (or for V5, an INSERT statement for defining the stored procedure in SYSIBM.SYSPROCEDURES for V5)

Next, precompile the generated C language program. This produces a DBRM and modified C language source statements. Ensure that the DBRM name is the same as the name of the load module for the SQL procedure.

The third step is to compile and link-edit the modified C source statements, producing an executable C language program. The default name for the C language program is the first eight bytes of the SQL procedure name. Finally, BIND the DBRM into a package and define the stored procedure to DB2.

Using DSNTPSMP to Create SQL Stored Procedures

DSNTPSMP , also known as the SQL procedure processor, is a REXX stored procedure that you can use to prepare an SQL procedure for execution. You can also use DSNTPSMP to perform selected steps in the preparation process or delete an existing SQL procedure. The following sections contain information on invoking DSNTPSMP .

DSNTPSMP can be executed only by issuing a CALL statement inside an application program or through DB2 Stored Procedure Builder or DB2 Development Center. Before you can run DSNTPSMP , you need to ensure that the appropriate PTFs and APARs have been applied to DB2, install the REXX language support feature, and code a program that issues a CALL statement for DSNTPSMP .

Use the Sample Programs Provided by IBM

IBM provides quite a few sample programs and jobs to assist you in developing SQL stored procedures. The samples can be found in the SDSNSAMP data set. Examine these for examples of how to implement effective DB2 SQL stored procedures. The SQL stored procedure samples that ship with DB2 are listed in Table 15.1.

Table 15.1. SQL Stored Procedure Samples

Name	Description
DSNHSQL	Sample JCL to preprocess, precompile, compile, prelink-edit, and link-edit SQL stored procedures.
DSNTEJ63	Sample JCL to prepare the DSN8ES1 SQL stored procedure for execution.
DSN8ES1	An example of an SQL stored procedure that uses the DB2 sample tables. It accepts a department number as input and returns a result set that contains salary information for each employee in that department.
DSNTEJ64	Sample JCL to prepare DSN8ED3 for execution.
DSN8ED3	A sample C program that calls the DSN8ES1 SQL stored procedure

The SQL Procedures Language "Catalog"

SQL procedures language requires two additional supportive tables, similar to DB2 Catalog tables. These tables contain information such as the source code of the SQL stored procedure code and the options used to develop the SQL stored procedure. The two tables are SYSIBM.SYSPSM and SYSIBM.SYSPSMOPTS .

SYSIBM.SYSPSM holds the source code for SQL stored procedures. The table contains one or more rows for each SQL stored procedure prepared by DSNTPSMP , Stored Procedure Builder, or DB2 Development Center. If the SQL stored procedure consists of more than 3,800 bytes, more than one row is required to hold the source code for the SQL procedure. Refer to Table 15.2 for a definition of SYSIBM.SYSPSM .

Table 15.2. SYSIBM.SYSPSM (SQL Procedure Source Table)

Column Name	Column Definition
SCHEMA	Schema of the SQL procedure. Blank if the SQL procedure was created prior to DB2 V6.
PROCEDURENAME	Name of the SQL stored procedure.
SEQNO	Sequence number between 1 and CEILING( x /3800) , where x is the number of bytes in the SQL procedure source statement.
PSMDATE	The date on which the SQL procedure was created.
PSMTIME	The time at which the SQL procedure was created.
PROCCREATESTMT	A VARCHAR(3800) column containing all or part of an SQL procedure source. If the SQL procedure statement is more than 3,800 bytes, this column contains the portion of the source statement indicated by SEQNO .

The SYSIBM.SYSPSM table has two indexes defined on it: DSNPSMX1 ( nonunique ) and DSNPSMX2 (unique).

SYSIBM.SYSPSMOPTS holds the program preparation options for SQL stored procedures. The table contains one row for each SQL stored procedure prepared by DSNTPSMP , Stored Procedure Builder, or DB2 Development Center. Refer to Table 15.3 for a definition of SYSIBM.SYSPSMOPTS .

The SYSIBM.SYSPSMOPTS table has one unique index, DSNPSMOX1 defined on it. This index must be defined before DSNTPSMP is executed.

Table 15.3. SYSIBM.SYSPSMOPTS (SQL Procedure Options Table)

Column Name	Column Definition
SCHEMA	Schema of the SQL procedure. Blank if the SQL procedure was created prior to DB2 V6.
PROCEDURENAME	Name of the SQL stored procedure.
BUILDSCHEMA	The schema name that qualifies the procedure name specified in the BUILDNAME column.
BUILDNAME	A procedure name associated with stored procedure DSNTPSMP . You can create multiple definitions for the DSNTPSMP stored procedure to run DSNTPSMP in different WLM environments.
BUILDOWNER	The authorization ID used to create the SQL stored procedure.
PRECOMPILE_OPTS	The options that were specified in the precompiler-options parameter for the most recent invocation of DSNTPSMP for this SQL stored procedure.
COMPILE_OPTS	The options that were specified in the compiler-options parameter for the most recent invocation of DSNTPSMP for this SQL stored procedure.
PRELINK_OPTS	The options that were specified in the prelink-edit-options parameter for the most recent invocation of DSNTPSMP for this SQL stored procedure.
LINK_OPTS	The options that were specified in the link-edit-options parameter for the most recent invocation of DSNTPSMP for this SQL stored procedure.
BIND_OPTS	The options that were specified in the bind-options parameter in the most recent invocation of DSNTPSMP for this SQL stored procedure.
SOURCEDSN	The name of the data set that contains the source code for the SQL stored procedure (if the SQL procedure source code was input to DSNTPSMP stored in an external data set).

The Benefits of Procedural SQL

The most useful procedural extension to SQL is the addition of procedural flow control statements. Flow control within procedural SQL is handled by typical programming constructs that you can mix with standard SQL statements. These typical constructs enable programmers to

• Embed SQL statements within a loop

• Group SQL statements together into executable blocks

• Test for specific conditions and perform one set of SQL statements when the condition is true, another set when the condition is false ( IF...ELSE )

• Perform branches to other areas of the procedural code

The addition of procedural commands to SQL provides a more flexible environment for application developers. Often, major components of an application can be delivered using nothing but SQL. You can code stored procedures and complex triggers using procedural SQL, thereby reducing the amount of host language (COBOL, C, Visual Basic, and so on) programming required.

Additionally, when stored procedures can be written using just SQL, more users will be inclined to use these features. DB2 requires stored procedures to be written in a host language. This requirement may scare off many potential developers. Most DBAs I know avoid programming ( especially in COBOL) like the plague.

In addition to SQL stored procedures, procedural SQL extensions also enable more complicated business requirements to be coded using nothing but SQL. For example, an independent SQL statement cannot examine each row of a result set during processing. Procedural SQL can accomplish this task quite handily using cursors and looping.

The Drawbacks of Procedural SQL

The biggest drawback to procedural SQL is that it is late getting into the ANSI standard. Although DB2's stored procedure support is based on the ANSI SQL3 standard, other DBMS vendors support different flavors of procedural SQL because they were developed before the ANSI standard. If your shop has standardized on one particular DBMS or does not need to scale applications across multiple platforms, you may not have this problem. But, then again, how many shops does this description actually describe? Probably not very many!

The bottom line is that scalability will suffer when applications are coded using non- standard extensions ”such as procedural SQL. Recoding applications that were designed to use stored procedures and triggers written using procedural SQL constructs is a non-trivial task. If an application needs to be scaled to a platform which uses a DBMS that does not support procedural SQL, a complete rewrite is exactly what must be done.

Performance drawbacks can be realized when using procedural SQL if the developer is not careful. For example, improper cursor specification can cause severe performance problems. Of course, this problem can happen just as easily when cursors are used inside a host language. The problem is more inherent to application design than it is to procedural SQL.

One final drawback is that even procedural SQL dialects are not computationally complete. Most dialects of procedural SQL lack programming constructs to control the users' screens and mechanisms for data input/output (other than to relational tables).