Chapter 10: 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 Universal Database Server. Local DB2 Universal Database 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 Universal Database Server, reducing network traffic to a single send and receive operation for a series of SQL statements.

Note: If you are running your stored procedure on DB2 for OS/390, through the DB2 Connect product, see your IBM DB2 documentation for other steps necessary to run the stored procedure.

Working with Stored Procedures

To get a stored procedure up and running:

Either add the Net Express 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.
Code and prepare a stored procedure. See the section Writing and Preparing Stored Procedures for instructions.
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.
Define your stored procedure to the DB2 Universal Database 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 Universal Database for additional details.
Compile your stored procedure. See the section Compiling Stored Procedures under DB2 Universal Database for additional details.
Debug and test your stored procedure. See the section Debugging Stored Procedures under DB2 Universal Database for additional details.

Writing and Preparing Stored Procedures

A stored procedure is an application program that runs in the DB2 Universal Database 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. See the DB2 Universal Database Application Development Guide for details.

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

See the DB2 Universal Database Application Development Guide for a complete list.

Preparing Stored Procedures

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

Prepare the application according to your embedded SQL documentation for creating stored procedure.
Define the stored procedure to the DB2 Universal Database Server. See Defining Stored Procedures under DB2 Universal Database.
Place the stored procedure DLL or JAVA routine on the DB2 Universal Database Server machine in a location specified in the CREATE PROCEDURE. If not specified, the default location is the sqllib\function subdirectory where DB2 Universal Database is installed. See the DB2 Universal Database Application Development Guide and DB2 Universal Database SQL Reference for additional options.

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:

The workstation application establishes a connection to the DB2 Universal Database Server.
The SQL statement CALL tells the DB2 Universal Database Server that the application is going to run the stored procedure, GETEMPSVR. The calling application provides the necessary parameters.
The DB2 Universal Database Server searches the system tables for rows associated with stored procedure GETEMPSVR.
The DB2 Universal Database Server passes information about the request to the stored procedure.
The stored procedure executes SQL statements.
The stored procedure assigns values to the output parameters and then exits.
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 Universal Database Server builds a parameter list for the stored procedure, using the parameters and values provided in the statement. The DB2 Universal Database Server obtains information about parameters from the system tables. See the section Defining Stored Procedures under DB2 Universal Database 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 Universal Database 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.

Parameter Style	Description
SIMPLE	Use SIMPLE (or GENERAL) to prevent the calling program passing null values for input parameters (IN or INOUT) to the stored procedure. The stored procedure must declare a variable for each parameter passed in the CALL statement.
SIMPLE WITH NULLS	Use SIMPLE WITH NULLS (or GENERAL WITH NULLS) to allow the calling program to supply a null value for any parameter passed to the stored procedure. The following rules apply: An indicator variable must follow each parameter in the calling program's CALL statement, using one of the following two forms: host variable :indicator variable or: host variable INDICATOR :indicator variable The stored procedure must declare a variable for each parameter passed in the CALL statement. The stored procedure must declare a null indicator structure containing an indicator variable for each parameter passed in the CALL statement. On entry, the stored procedure must examine all indicator variables associated with input parameters to determine which parameters contain null values. On exit, the stored procedure must assign values to all indicator variables associated with output variables. An indicator variable for an output variable that returns a null value to the caller must be assigned a negative number. Otherwise, the indicator variable must be assigned the value zero (0).
DB2SQL	In addition to the parameters on the CALL statement, the following arguments are passed to the stored procedure: a NULL indicator for each input parameter on the CALL statement. the SQLSTATE to be returned to DB2. the qualified name of the stored procedure. the specific name of the stored procedure. the SQL diagnostic string to be returned to DB2.

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.

Note: If you are using the SIMPLE WITH NULLS linkage convention, you must declare indicator variables for all of your parameters, so you do not need to declare another indicator variable for the large output parameters.

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 it1 . In addition, the PARMLIST column in the DECLARE PROCEDURE must contain a compatible SQL data type declaration for each parameter. For PARMLIST string and corresponding language declarations, see the SQL data types table in the Net Express Database Access manual.

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 Universal Database. This might change in the future. See the DB2 Universal Database SQL Reference and the DB2 Application Development Guide for details of which functions are supported and by what language.

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 Net Express Database Access manual for more details.

Defining Stored Procedures under DB2 Universal Database

A stored procedure is unusable until it is defined 1 - 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 Universal Database 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.

1 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 Universal Database Application Development Client.

Compiling Stored Procedures under DB2 Universal Database

To compile a COBOL stored procedure using Net Express and DB2 Universal Database, follow the steps below:

Compile the program which is to be used as a stored procedure with the DB2 directive, just like any DB2 Universal Database program. This can be done by adding a $SET statement to your program. See the Net Express Database Access manual for more details on DB2 directive options.
Note that if your program contains stored procedure CALL statements, you must specify the CALL_RESOLUTION DB2 directive or an SQL0204 error will occur.
After the program has been added to the Net Express project, select the program and package it as a Dynamic Link Library (.dll).
In the build setting for the program, select the Link tab and select the Advanced category from the drop-down list box. In the "Link with these Libs" entry field, add db2api.lib and then click the Close button.
To compile and link the stored procedure, you can then select the .dll file and select "Rebuild object" from the context menu.
Depending upon how you defined your CREATE PROCEDURE, you are now ready to either test your stored procedure or copy it to the sqllib\function subdirectory. From the "Rebuild object" command, select "Deployment" from the Project menu and select the file and specify where to copy it to.

Debugging Stored Procedures under DB2 Universal Database

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 Universal Database 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. If you are running Net Express under Windows 9x, you can just add a call to CBL_DEBUGBREAK to your stored procedure and the Net Express debugger will display when the statement is executed.

If you are running under Windows NT / 2000 / XP, DB2 stored procedures are executed under the db2dari process for versions previous to DB2 UDB version 8, and executed under the db2fmp process for versions DB2 UDB version 8 or later, which means that a call to CBL_DEBUGBREAK will not work by itself. You need to compile the stored procedure with a "sleep" function and attach to a running process. When the CBL_DEBUGBREAK window is displayed, reply "NO" since replying "YES" will fail.

Note that you need to attach to the db2dari or db2fmp process before the sleep function expires or you will not be able to animate the stored procedure. If this happens, you will have to repeat the whole process described below so it is important to pick a suitably large sleep value.

To set a sleep value, define a variable in Working-Storage that will define how long to put the variable to sleep. The call to the sleep function can be placed either before or after the call to CBL_DEBUGBREAK.

For example:

01  ws-wait                 pic 9(8) comp-5 value 30000.

Then in the program add this statement so that it executes before you want to start animating:

call DB2API 'Sleep' using by value ws-wait

where DB2API is defined as call-convention 74 in a Special Names paragraph.

To animate the stored procedure:

Invoke the stored procedure by using the IBM Stored Procedure Builder or running a client program.
Start Net Express and click the Step button to display the Start Animating window.
You must have Administrator authority to attach a debugger to a running process.
Click the Options button.
Check the "attach to running process" check box and then click the OK button.
Select the process associated with db2dari.exe if using a DB2 UDB version previous to version 8 or db2fmp.exe if using DB2 UDB version 8 or later, and then click the Debug button. If the db2dari or db2fmp process is not listed yet, click the Refresh button.
Click the Break button. The debugger is displayed.
Set a break point on the COBOL statement you want to start animating and then click the Run button. Modify the value of the wait time to "1" so that you do not have to wait for the sleep timer to expire.

When you have completed debugging the stored procedure, just exit from the Animator.

Tip: If the stored procedure is not working as expected, make sure the parameters passed to the stored procedure are getting passed in the expected format by examining each parameter in the Linkage Section.