Writing Stored Procedures

The programs that implement stored procedures can be written in several languages: C/C++, COBOL/OO COBOL, PL/1, assembler, REXX, Java, and SQL Procedure Language. The calling program and the stored-procedure program can be implemented in different languages. For example, the client code could be COBOL, and the stored procedure could be C. The connection is through the DB2 thread, so individual coding does not matter. With the exception of REXX, the stored-procedure code is compiled and stored at the server and can contain static SQL and dynamic SQL, as well as IFI calls, and can issue DB2 commands. The stored-procedure program can also access external resources, such as VSAM files, and can even call CICS transactions.

Language Environment

The Language Environment product libraries are used to load and execute the stored procedures in one of the stored-procedure address spaces. When you create a stored procedure, you may pass runtime options for Language Environment to use when the stored procedure is executed. Language Environment is used because it establishes a common runtime environment for the many languages used for building stored procedures, providing a consistent set of interfaces for essential runtime services. When using Language Environment, you do not have to specify language-specific libraries in the JCL procedure of the stored-procedure address space.

Language Environment performs several functions for DB2: hiding the differences among programming languages, providing the ability to make a stored procedure resident in the stored-procedures address space, and supporting a large number of runtime options. Language Environment, along with the language compiler, provides the ability to debug your program using the IBM Debug Tool, also known as CODE/370. Stored-procedure programs can be debugged in batch mode or interactively, using the VisualAge Remote Debugger or IBM Distributed Debugger, or under z/OS using the VTAM MFI (Main Frame Interface) and a 3270 terminal emulator.

Stored-procedure programs should be written and compiled to be reentrant and reusable. This allows Language Environment to share a single copy of the program in memory. You can also use the STAY RESIDENT YES option, which will keep the program in memory for the next stored-procedure call to avoid having to retrieve the module from disk again.

Parameters

Stored procedures usually have parameters that are passed in and, possibly, out. From the caller, the parameters are passed in the same manner as the commonly available CALL syntax:

 EXEC SQL    CALL SP1(:parm1, :parm2, :parm3); END-EXEC. 

Here, the parameters are host variables. Each parameter can be defined as input to the stored procedure, output from the stored procedure, or both input and output. The calling program must define appropriate storage areas for each parameter. Null values are passed by using indicator variables, as in

 EXEC SQL    CALL SP1(:parm1 :ind1, :parm2 :ind2, :parm3 :ind3); END-EXEC. 

The parameters used by the stored-procedure program must match those defined on the CREATE PROCEDURE statement. DB2 cannot verify that the stored-procedure program handles the correct number and type of parameters, so the programmer must make sure they do this correctly. DB2 will validate that the calling program specified the correct parameters as compared to the CREATE PROCEDURE statement and will set up the parameter list accordingly.

PARAMETER STYLE Option

The PARAMETER STYLE option of the CREATE PROCEDURE statement controls the number and method of passing parameters into the stored-procedure program. To illustrate the differences in parameter passing, we'll use a stored-procedure program written in C and defined as follows:

 CREATE PROCEDURE YLA.TWOTOONE                 ( IN  PARM1 CHAR(8)                 , IN  PARM2 CHAR(5)                 , OUT PARM3 CHAR(80)                 )                 EXTERNAL NAME SP1                 LANGUAGE C                 PROGRAM TYPE SUB                 PARAMETER STYLE xxxxxxx                 COLLID TEST                 STAY RESIDENT YES                 WLM ENVIRONMENT WLMENV1; 

PARAMETER STYLE GENERAL conforms to the way DB2 passed parameters to stored procedures prior to version 6. In this style, the parameters passed to the program are essentially the same parameters that are defined in the CREATE PROCEDURE statement. The function declaration would look like this:

 void sp1(char   parm1[9],      /* Input parm1 */          char   parm2[6],      /* Input parm2 */          char   parm3[81],     /* Output parm3 */          ) 

Generally, C programs use a null-terminated character array to hold character string fields. DB2 handles the conversion from fixed-length character fields to null-terminated strings automatically for the stored-procedure program. This is why the parameter fields in our example are one byte longer than the parameters specified on the CREATE PROCEDURE statement.

PARAMETER STYLE GENERAL WITH NULLS extends the GENERAL style to pass null indicators back and forth. The null indicators are input/output variables passed in an array, as in

 void sp1(char   parm1[9],      /* Input parm1 */          char   parm2[6],      /* Input parm2 */          char   parm3[81],     /* Output parm3 */          short* p_indparm[3]   /* Indicators for parms */          ) 

The newest way of passing parameters, PARAMETER STYLE DB2SQL, is the recommended style for new stored procedures. It is the default for all external languages except JAVA and is invalid for REXX. With the DB2SQL specification, the parameters passed to the stored-procedure program look like this:

 void sp1(char   parm1[9],      /* Input parm1 */          char   parm2[6],      /* Input parm2 */          char   parm3[81],     /* Output parm3 */          short* p_indparm1,    /* Indicator for parm1 */          short* p_indparm2,    /* Indicator for parm2 */          short* p_indparm3,    /* Indicator for parm3 */          char   p_sqlstate[6], /* sqlstate to return */          char   p_proc[28],    /* stored procedure name */          char   p_spec[19],    /* specific name */          char   p_diag[71],    /* message to return */          ) 

As you can see, DB2SQL passes null indicators as separate variables and includes several additional parameters. The most significant of these are the p_sqlstate and p_diag parameters, which allow the stored-procedure program to pass error information back to the caller without using additional output parameters. This simplifies error checking in the client programs. As of version 8, there are no restrictions on the allowable SQLSTATE values other than that they be valid. Prior to version 8, the actual values allowed for the returned SQLSTATE are as follows:

• 00000 returns SQLCODE 0

• 01Hxx returns SQLCODE +462

• 02000 returns SQLCODE +100

• 38001 returns SQLCODE 487

• 38002 returns SQLCODE 577

• 38003 returns SQLCODE 751

• 38004 returns SQLCODE 579

• 38yxx returns SQLCODE 443

The p_diag message area is limited to 70 bytes, but the caller may not receive the entire string. DB2 takes the message text from this field and appends it to the SQLERRMC field in the SQLCA. DB2 includes other information at the beginning of the SQLERRMC field, so your message may be truncated.

The last available style is PARAMETER STYLE JAVA and is used only for Java stored procedures.

DBINFO Clause

If desired, the stored-procedure program can get an additional block of information from DB2 when called. This is requested by adding the DBINFO clause to the CREATE or ALTER PROCEDURE statement. When this clause is specified, DB2 passes the DBINFO block as the last parameter to the stored-procedure program. This block contains

• The length of the location name.

• The current location name.

• The length of the authorization ID.

• The authorization ID of the application.

• A structure that provides information about the CCSIDs and encoding scheme of the subsystem.

• The length of the table qualifier.

• The qualifier of the table that is specified in the table name.

• The length of the table name.

• The name of the table for a user-defined function.

• The length of the column name.

• The name of the column for a UDF.

• Information that identifies the product on which the program executes. This field has the form DSNvvrrm, where vv is a two-digit version identifier, rr is a two-digit release identifier, and m is a one-digit modification-level identifier.

• The operating system on which the program that invokes the UDF runs.

• A reserved area of 24 bytes.

• A pointer to a string that uniquely identifies the application's connection to DB2.

• A reserved area of 20 bytes.

Note that this block does not contain the number of entries in the table function column list or the table function column list pointer; this information is not used for stored procedures.

Result Sets

In addition to returning parameters, a stored procedure can return one or more result sets. A result set is a relational table implemented as a cursor from which the client program can fetch rows. This way, an ODBC client can retrieve data from DB2 without the end user's requiring privileges to the underlying tables. The user requires only execute privileges for the stored procedure.

Result sets from DB2 or nonrelational sources can be returned from either base tables or temporary tables to clients on any platform. In order for a stored procedure to return result sets to clients, the procedure needs to be defined for returning result sets, cursors must be opened using the WITH RETURN clause, and a DRDA client needs to support level 3 result sets.

Client programs on platforms other than z/OS must use the ODBC/CLI interfaces. DB2 for z/OS implements enhancements that allow embedded SQL programs to also retrieve result sets. The following is a skeleton example of coding to receive a stored-procedure result set when the application program is running on the same z/OS server running the stored procedure:

 EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS RESULT_SET_LOCATOR VARYING * locater1; EXEC SQL END DECLARE SECTION; EXEC SQL CALL SP1(:parm1, :parm2, ...); EXEC SQL ASSOCIATE LOCATOR (:locater1) WITH PROCEDURE SP1; EXEC SQL ALLOCATE CSR1 CURSOR FOR RESULT SET :locater1; EXEC SQL FETCH CSR1 INTO :acctno, :billingno; 

To allow result sets to be returned from the stored procedure on the server, the WITH RETURN clause must be used in the cursor definition in the stored procedure, and the cursors may not be closed before the program ends. DB2 will return the rows in the result set from the opened cursors to the application program when the stored procedure ends. The following example shows the stored-procedure code necessary to return the result set to the application code shown in the previous example:

 EXEC SQL DECLARE SP_CSR1 CURSOR                  WITH RETURN FOR                  SELECT ACCT_NO, BILLING_NO                  FROM ORDER_TABLE                  WHERE ITEM = :parm1; EXEC SQL OPEN SP_CSR1; RETURN; 

When returning a result set from a stored procedure, the cursor must not be unintentionally closed. The cursor that the stored procedure opens will be closed if the unit of work is terminated by a commit. This includes the commit generated internally by DB2 if the COMMIT ON RETURN option is specified during the creation of the stored procedure.

From a design standpoint, most of the result sets will be of the previous variety. But a very powerful way of building totally dynamic stored procedures can be used and still be able to interface with client processes. This process can be significant with the move toward Web-enabled applications that provide dynamic pages to a client.

The DESCRIBE PROCEDURE statement can be used to retrieve all the information about a procedure's result set. This statement is not required if result sets are predefined, and it cannot be dynamically prepared. It returns information that a "dynamic" client needs to use the result sets. The SQLDA (SQL Descriptor Area) contains the number of result sets, with one SQLVAR enTRy for each result set in the stored procedure. The SQLDATA(x) contains the result-set locator value, and the SQLNAME(x) contains the server application's cursor name. This is invoked by using either of the following:

 EXEC SQL DESCRIBE PROCEDURE SP1 INTO :sqlda EXEC SQL DESCRIBE PROCEDURE :hv INTO :sqlda 

The DESCRIBE CURSOR statement can be used to retrieve information about a cursor that was left open inside a stored procedure and returned as a result set. This works for any allocated cursor but is not needed for predefined result sets. This statement cannot be dynamically prepared. This feature returns information needed by dynamic clients, such as the name, length, and type for columns contained in a result set. The output SQLDA is very similar to that produced from the DESCRIBE of a PREPARE statement. This is invoked by using either of the following:

 DESCRIBE CURSOR SPC1 INTO sqlda DESCRIBE CURSOR :hv INTO sqlda 

Unit of Work

Stored procedures execute by using the same logical unit of work as the client program that executes the SQL CALL statement. Starting with version 7, stored-procedure programs can issue COMMIT statements. This commit will impact the entire unit of work, including any uncommitted changes made by the caller prior to invoking the stored procedure.

COMMIT ON RETURN

In addition to issuing a commit within a stored-procedure program, the stored procedure can be defined to commit on successful completion. This is done using the COMMIT ON RETURN clause on the CREATE or ALTER PROCEDURE statement.

The COMMIT ON RETURN option for stored procedures reduces network traffic and allows locking in a stored procedure to be performed on a predictable basis. Without COMMIT ON RETURN, the locks would be held until the client application issued the commit. The client commit could be some time later, and additional network messages would be required. With the COMMIT ON RETURN option, the locks are freed as soon as the stored procedure ends.

There are always tradeoffs. COMMIT ON RETURN cannot be used in nested stored procedures. The design of nested stored procedures requires some delicate planning.

Issuing COMMITs

Starting with version 7, DB2 can have commits issued in a stored procedure. In the first instantiation of stored procedures, there was no commit at all. Then came COMMIT ON RETURN, to reduce the network traffic. In version 7, the ability to commit or roll back in stored procedure was added. This has implications only on new stored procedures or on careful enhancements to existing stored procedures and will not be needed in the majority of existing stored procedures, as they were written with the knowledge that a stored procedure was simply a continuation of an existing thread. But with more movement to object-oriented programming, using stored procedures as one way of introducing classes and methods, this feature will have more use.

Nesting

Stored procedures and UDFs can be nested. This means that a stored-procedure program can itself issue an SQL CALL statement or include a UDF within another SQL statement. Triggers can also call UDFs or stored procedures. With nesting, careful planning is required to avoid looping. DB2 has a limit of 16 nesting levels for triggers, UDFs, and stored procedures.

Nesting has obvious impacts on resource consumption and the scope of special registers. When nesting stored procedures, the following DB2 special registers are saved when pushing deeper and are restored when popping back up:

• CURRENT DATE

• CURRENT DEGREE

• CURRENT PACKAGESET

• CURRENT PATH

• CURRENT RULES

• CURRENT SERVER

• CURRENT SQLID

• CURRENT TIME

• CURRENT TIMESTAMP

• CURRENT TIMEZONE

• CURRENT USER

Some important limitations apply when nesting stored procedures.

• Nesting between DB2-managed and workload managermanaged address spaces is not allowed, which is another reason to move away from DB2-managed address spaces.

• No COMMIT ON RETURN is executed when nested.

• Query result sets are returned onto the previous nesting level.

Careful communication among the application programmers, database procedural programmers, system programmers, and database administrators is needed in order to balance needs and make sure that unnecessary levels, trigger cascading, and looping cannot occur.