Chapter 13: Stored Procedures

This chapter introduces stored procedures, and describes how they work under OpenESQL and DB2.

A stored procedure is a compiled program that can execute SQL statements.

You need to use stored procedures if your application program operates in a client/server environment, and if either of the following two problems apply:

• The application accesses host variables for which you want to guarantee security and integrity.
• The application executes a series of SQL statements, creating many network send and receive operations, which significantly increase CPU and elapsed time costs.

DB2 Stored Procedures

As we have seen above, a stored procedure is a compiled program that can execute SQL statements. Stored procedures are stored at a local or remote DB2 LUW Server. Local DB2 LUW Server applications or remote DRDA applications can use the SQL statement CALL to invoke a stored procedure.

Use stored procedures to combine many of your application's SQL statements into a single message to the DB2 subsystem or DB2 LUW Server, reducing network traffic to a single send and receive operation for a series of SQL statements.

Working with Stored Procedures

To get a stored procedure up and running:

1. Either add your installation's executable directory (base\bin ) to the PATH statement

   or

   copy the COBOL run-time .dll (cblrtss.dll if using single threaded run-time ) to the directory from where the stored procedure will be executed. You need to do this for DB2 to be able to execute a COBOL stored procedure.

2. Code and prepare a stored procedure. See the section Writing and Preparing Stored Procedures for instructions.
3. Code and prepare an application that calls the stored procedure. An SQL statement, CALL, in that application must use the same parameter list and linkage convention as the stored procedure that it invokes. See the section Writing and Preparing Applications to Use Stored Procedures for instructions.
4. Define your stored procedure to the DB2 LUW Server by issuing a CREATE PROCEDURE command, which will place a row in the appropriate system table(s). See the section Defining Stored Procedures under DB2 LUW for additional details.
5. Compile your stored procedure. See the section Compiling Stored Procedures under DB2 LUW for additional details.
6. Debug and test your stored procedure. See the section Debugging Stored Procedures under DB2 LUW for additional details.

Writing and Preparing Stored Procedures

A stored procedure is an application program that runs in the DB2 LUW Server's address space. It can contain most statements that an application program normally contains. It can consist of more than one program. Your stored procedure can call other programs as well as nested stored procedures but there are restrictions.

The application program that invokes the stored procedure can be in any language that supports SQL statements. You can write a stored procedure using many languages such as C, JAVA, COBOL or now SQL Procedure Language which is consistent with the Persistent Stored Module definition of the ANSI SQL99 standard.

The store procedure can be written in one language, for example JAVA, with the client written in another language, for example COBOL. When the languages differ, DB2 transparently passes the values between the client and the stored procedure so each program gets the values in the expected format defined by the CREATE PROCEDURE statement.

Features of a Stored Procedure

A stored procedure is similar to any other SQL application.

The following restrictions apply to stored procedures:

• A C or COBOL stored procedure must be a dynamic link library (DLL) when built to run under Windows server.
• To use SQL Procedure Language, you need to have a C compiler installed since the SQL statements generate a C program.
• The following SQL statements are not allowed in a stored procedure:
  • CONNECT
  • DISCONNECT
  • SET CONNECTION

Preparing Stored Procedures

The following are tasks which must be completed before a stored procedure can be run on an DB2 LUW Server:

1. Prepare the application according to your embedded SQL documentation for creating stored procedure.
2. Define the stored procedure to the DB2 LUW Server. See Defining Stored Procedures under DB2 LUW.
3. Place the stored procedure DLL or JAVA routine on the DB2 LUW Server machine in a location specified in the CREATE PROCEDURE. If not specified, the default location is the sqllib\function subdirectory where DB2 LUW is installed.

How an Application Works With a Stored Procedure

A typical stored procedure contains two or more SQL statements, and some manipulative or logical processing. In this example, your application, CALLSTPR, runs on a workstation client and calls a stored procedure, GETEMPSVR. The following process occurs:

1. The workstation application establishes a connection to the DB2 LUW Server.
2. The SQL statement CALL tells the DB2 LUW Server that the application is going to run the stored procedure, GETEMPSVR. The calling application provides the necessary parameters.
3. The DB2 LUW Server searches the system tables for rows associated with stored procedure GETEMPSVR.
4. The DB2 LUW Server passes information about the request to the stored procedure.
5. The stored procedure executes SQL statements.
6. The stored procedure assigns values to the output parameters and then exits.
7. Control returns to the calling application, which receives the output parameters.

The application can call more stored procedures or it can execute more SQL statements. The application designer determines whether to COMMIT work in the stored procedure that runs on the server, or in the client as a single transaction.

Writing and Preparing Applications to Use Stored Procedures

To invoke a stored procedure and to pass a list of parameters to the procedure, use the SQL statement CALL. Your application program can call several stored procedures.

After connecting to its server, an application can mix calls to stored procedures with SQL statements sent to the server.

Executing the SQL Statement CALL

Use the CALL statement to execute each series of SQL statements in your application.

Example 1:

To call the stored procedure described in How an Application Works with a Stored Procedure, your application might use this statement:

EXEC SQL
 CALL GETEMPSVR (:V1, :V2)
END-EXEC

If you use host variables in the CALL statement, you must declare them before using them.

Example 2:

The example above is based on the assumption that none of the input parameters can have null values. To allow null values, code a statement like this:

EXEC SQL
 CALL GETEMPSVR (:V1 :IV1, :V2 :IV2)
END-EXEC

where :IV1 and :IV2 are indicator variables for the parameters.

Example 3:

To pass integer or character string constants or the null value to the stored procedure, code a statement like this:

EXEC SQL
 CALL GETEMPSVR (2, NULL)
END-EXEC

Example 4:

To use a host variable for the name of the stored procedure, code a statement like this:

EXEC SQL
 CALL :PROCNAME (:V1, :V2)
END-EXEC

Example 5:

Assume that the stored procedure name is GETEMPSVR. The host variable PROCNAME is a character variable of length 254 or less that contains the value GETEMPSVR. You should use this technique if you do not know in advance the name of the stored procedure, but you do know the parameter list convention.

To pass your parameters in a single structure, rather than as separate host variables, code a statement like this:

EXEC SQL
 CALL GETEMPSVR USING DESCRIPTOR :ADDVALS
END-EXEC

where ADDVALS is the name of an SQLDA.

Example 6:

To use a host variable name for the stored procedure with an SQLDA, code a statement like this:

EXEC SQL
 CALL :PROCNAME USING DESCRIPTOR :ADDVALS
END-EXEC

This form gives you extra flexibility because you can use the same CALL statement to invoke different stored procedures with different parameter lists.

Your client program must assign a stored procedure name to the host variable PROCNAME and load the SQLDA ADDVALS with the parameter information before making the SQL CALL statement.

Each of the above CALL statement examples uses an SQLDA. If you do not explicitly provide an SQLDA, the precompiler generates the SQLDA based on the variables in the parameter list.

You can execute the CALL statement only from an application program. You cannot use the CALL statement dynamically.

Parameter Conventions

When an application executes the CALL statement, the DB2 LUW Server builds a parameter list for the stored procedure, using the parameters and values provided in the statement. The DB2 LUW Server obtains information about parameters from the system tables. See the section Defining Stored Procedures under DB2 LUW for more information. Parameters are defined as one of these types:

• IN

  Input-only parameters, which provide values to the stored procedure.

• OUT

  Output-only parameters, which return values from the stored procedure to the calling program.

• INOUT

  Input/output parameters, which provide values to or return values from the stored procedure.

If a stored procedure fails to set one or more of the output-only parameters, the DB2 LUW Server simply returns the output parameters to the calling program, with the values established on entry to the stored procedure. COBOL supports three parameter list conventions. Other languages support other conventions. The parameter list convention is chosen based on the parameter style defined in the CREATE PROCEDURE statement.

Using Indicator Variables to Speed Processing

If any of your output parameters require a lot of storage, do not pass the entire storage areas to your stored procedure, but declare an indicator variable for every large output parameter in your SQL statement CALL. Indicator variables are used in the calling program to pass only a two-byte area to the stored procedure and to receive the entire area from the stored procedure.

Assign a negative value to each indicator value associated with a large output variable. Then include the indicator variables in the CALL statement. This technique can be used whether the stored procedure linkage is SIMPLE or SIMPLE WITH NULLS.

For example, suppose that a stored procedure, STPROC2 defined with the SIMPLE linkage convention takes one integer input parameter and one character output parameter of length 5000. It is wasteful to pass the 5000 byte storage area to the stored procedure. Instead, a COBOL program containing these statements passes only two bytes to the stored procedure for the output variable and receives all 5000 bytes from the stored procedure:

INNUM  PIC S9(9) COMP
OUTCHAR PIC X(5000)
IND  PIC S9(4) COMP
.
.
.
MOVE -1                      TO IND
EXEC SQL CALL STPROC2(:INNUM, :OUTCHAR :IND)  END-EXEC

Declaring Data Types for Passed Parameters

A stored procedure must declare each parameter passed to it. In addition, the DATA TYPE declared in the declaration must be valid for the data source being used and must use a compatible host variable data type when calling the stored procedure. For more information on data types, see the chapter SQL Data Types earlier in this book.

For example:

CREATE PROCEDURE GETEMPSVR
 (IN  EMPNO CHAR(6),
  INOUT SQLCD    INT ,
  OUT FIRSTNME CHAR(12),
  OUT LASTNAME CHAR(12),
  OUT HIREDATE CHAR(10),
  OUT SALARY   DEC(9,2) )
  LANGUAGE COBOL
  EXTERNAL NAME 'GETEMPSVR!GETEMPSVR'
  PARAMETER STYLE DB2SQL;

Limitations

IBM has not implemented the same support for all SQL syntax related to stored procedures in every supported language. For example, you cannot create COBOL stored procedures that use result sets or the EXEC SQL syntax that supports that functionality with the workstation version of DB2 LUW.

There is no support for structure, array, or vector parameters using the DB2 native precompiler. However, there is much more flexibility when using the OpenESQL precompiler and a ODBC connection. See the chapters OpenESQL and DB2 External Compiler Module for more information.

Defining Stored Procedures under DB2 LUW

A stored procedure is unusable until it is defined. Use the CREATE PROCEDURE command to do this. You can either use the DB2 command prompt or place the command in a program and compile and run it. If you use the DB2 command prompt, you first connect to the DB2 LUW Server where the stored procedure will be executed.

For example:

C:> db2 connect to sample

You can type in the command at the DB2 command prompt making sure you include continuation characters and command delimiters, or you can place the CREATE PROCEDURE in an ANSI text file. For example, if we placed the previous command in text file creproc.sql, the command that you would enter would be:

C:> db2 -td; -vf creproc.sql

where:

• the "-td" option indicates the next character is the delimiter to end the command. In our example, it is a semicolon (;).
• the "-vf" option indicates that the next token is the file to process that contains the SQL command script.

The create procedure statement must uniquely identify a stored procedure. If you want to change the stored procedure to either add or drop parameters or change functionality, you must use the DROP PROCEDURE command and then re-add it with the CREATE PROCEDURE command.

When DB2/2 was originally developed, DB2/2 did not support the CREATE PROCEDURE function, and it is possible to write COBOL stored procedures without doing a CREATE PROCEDURE. Examples of this method and the parmlist that is required are included in the DB2 LUW Application Development Client.

Compiling Stored Procedures under DB2 LUW

You can compile a COBOL stored procedure using DB2 LUW.

1. Compile the program you want to use as a stored procedure with the DB2 directive, just like any DB2 LUW program. Do this by adding a $SET statement to your program. See the topic DB2 Compiler Directive Options for information on available options.

   Note: If your program contains stored procedure CALL statements, you must specify the CALL_RESOLUTION DB2 directive to avoid an SQL0204 error.

2. After the program has been added to the project, package it as a Dynamic Link Library (.dll).
3. In the build setting for the program on the Link tab, click Advancedfrom the drop-down list.
4. In the Link with these Libs field, add db2api.lib; then click Close .
5. Righ-click the .dll file; then click Rebuild object from the context menu.
6. From the Rebuild object command, click Project > Deployment; then select the file and copy it to the sqllib\function directory.

Debugging Stored Procedures under DB2 LUW

Because debugging a stored procedure by its very nature implies stopping the server and stepping through the server code, it is imperative that the programmer debugging the stored procedure code is the only person using the database server. You should therefore debug DB2 LUW stored procedures using a database on your own workstation if possible.

You can test a COBOL stored procedure without coding a client program by using:

• The IBM Stored Procedure Builder to run a stored procedure. You connect to the database where the stored procedure is located, and then select the stored procedure you want to run. The Store Procedure Builder then prompts you for INPUT and INOUT values. The results and any errors are then displayed.
• The DB2 command prompt.

If you don't get the expected results, you might want to debug the COBOL stored procedure. The following is required to successfully debug a stored procedure:

• Before compiling your stored procedure, add a call to CBL_DEBUGBREAK at the place where you want to start debugging
• Link the COBOL stored procedure to load the dynamic COBOL runtime
• When the CBL_DEBUGBREAK message box appears, click Yes

Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.