A stored procedure is a user-written application program that is compiled and stored at the server. When the database manager is running in multiple user mode, local applications or remote DRDA applications can invoke the stored procedure. Since the SQL statements issued by a stored procedure are local to the server, they do not incur the high network costs of distributed statements. Instead, a single network send and receive operation is used to invoke a series of SQL statements contained in the stored procedure.
Figure 31 and Figure 32 illustrate how the use of stored procedures reduces network traffic by decreasing the number of commands that flow between the application requester and the application server.
Figure 31. Without Stored Procedures
Application Server
Application Requester *--------------------------*
*-----------------------* | |
| | | |
| EXEC SQL CREATE *----------> | Process statement and |
| TABLE ... |<-----------* return SQLCA |
| | | |
| EXEC SQL INSERT ... *----------> | Process statement and |
| |<-----------* return SQLCA |
| | | |
| EXEC SQL COMMIT *----------> | Process statement and |
| WORK ... |<-----------* return SQLCA |
| | | |
*-----------------------* | |
*--------------------------*
|
Figure 32. With Stored Procedures
Application Server Stored Procedure Server
*-----------------------* *------------------------*
Application Requester | | | |
*-------------------* | | | |
| | | | | |
| EXEC SQL CALL ... *---->|Send request to stored *--->|Invoke stored procedure |
| | |procedure server | |application |
| | | | | |
| | |Process statement and |<---*EXEC SQL INSERT ... |
| | |return SQLCA *--->| |
| | | | | |
| | |Process statement and |<---*EXEC SQL UPDATE ... |
| | |return SQLCA *--->| |
| | | | | |
| Process results |<----*Return results to |<---*Stored procedure |
| | |application requester | |completes and returns |
| | | | |results |
| | | | | |
| EXEC SQL COMMIT *---->|Process statement and | | |
| WORK ... |<----*return SQLCA | | |
*-------------------* | | | |
*-----------------------* *------------------------*
|
For information on the stored procedure environment, including stored procedure servers, refer to the DB2 Server for VSE & VM Database Administration manual.
There are several other benefits that can be gained through the use of stored procedures, including:
Stored procedure that are to be used on a DB2 Server for VSE & VM database can be written in PL/I, COBOL, C, or Assembler. Stored procedures are very much like regular application programs, with the following exceptions:
| Note: | Stored procedures must be written as MAIN programs; they cannot be SUB programs. |
The following is an example of a simple stored procedure. It contains one SQL statement that SELECTs the salary of a given employee from the SQLDBA.EMPLOYEE table. The employee number is provided as input, and the salary and the SQLCODE for the SELECT statement are returned.
IDENTIFICATION DIVISION.
PROGRAM-ID. SAMP1.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
DATA DIVISION.
FILE SECTION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CHAR6HV PIC X(6).
01 SALHV PIC S9(7)V9(2) COMPUTATIONAL-3.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
LINKAGE SECTION.
01 CHAR6 PIC X(6).
01 SALARY PIC S9(7)V9(2) COMPUTATIONAL-3.
01 SQLCD PIC S9(9) COMP.
PROCEDURE DIVISION USING CHAR6 SALARY SQLCD.
* TURN OFF SQL EXCEPTION PROCESSING *
EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
MOVE CHAR6 TO CHAR6HV.
EXEC SQL
SELECT SALARY INTO :SALHV FROM SQLDBA.EMPLOYEE
WHERE EMPNO = :CHAR6HV
END-EXEC.
MOVE SALHV TO SALARY.
MOVE SQLCODE TO SQLCD.
STOP RUN.
The following is an example of a CALL statement that could be used to invoke the procedure shown above:
CALL SAMP_PROC ('000250', :SALARY, :SQLCD)
The SQL CALL statement is discussed in more detail in Calling Stored Procedures.
Information about the execution of SQL statements within a stored procedure is not returned to the application that invoked the stored procedure. If SQLCODE, SQLSTATE, or any other information from the SQLCA is required by the calling application, that information must be included in the parameter list of the stored procedure and the parameters must be set explicitly in the stored procedure. This is because there are many situations in which a negative SQLCODE does not necessarily indicate a problem (such as dropping a table that does not exist). The person who writes the stored procedure application must determine what SQLCODEs should be returned to the caller.
See Writing Stored Procedures for an example of a stored procedure that returns an SQLCODE.
As mentioned previously, stored procedures must be LE-compliant. IBM Language Environment for MVS and VM and the IBM Language Environment for VSE/ESA establish a common run-time environment for different programming languages. It combines essential run-time services, such as condition handling and storage management. All of these services are available through a set of interfaces that are consistent across programming languages. With LE, you can use one run-time environment for your applications, regardless of the application's programming languages or system resource requirements.
Language Environment is the prerequisite run-time environment for applications generated with the following IBM compiler products:
Stored procedures can be written in assembly language as long as the assembly language program uses the required macros to operate as an IBM Language Environment application program.
For complete details, see the Language Environment documentation.
For DB2 Server for VM, once the stored procedure has been written, it must be preprocessed, compiled, and linked like any application program, and the load module must be put on a disk that can be accessed by the stored procedure server that will run the stored procedure. For DB2 Server for VSE, once the stored procedure has been written, it must be preprocessed, compiled, and linked like any application program, and the phase must be put in a library that is in the stored procedure server's search path. In addition, the CREATE PROCEDURE statement must be used to define the stored procedure to the database manager. See the DB2 Server for VSE & VM SQL Reference manual for information on the CREATE PROCEDURE statement.
Once a stored procedure has been created and the CREATE PROCEDURE statement has been used to define it, it can be invoked. The SQL CALL statement is used in an application program to invoke a stored procedure. The syntax of the CALL statement is shown in Figure 33.
Figure 33. Syntax of SQL CALL statement
>>-CALL----+-procedure-name-+----------------------------------->
'-host-variable--'
>-----+-------------------------------------+------------------><
+-(--+-------------------------+---)--+
| | .-,------------------. | |
| | V | | |
| '----+-host-variable-+--+-' |
| +-constant------+ |
| '-NULL----------' |
'-USING DESCRIPTOR--descriptor-name---'
|
For a complete description of the CALL statement, see the DB2 Server for VSE & VM SQL Reference manual.
As indicated in Figure 33, the procedure name can be a host variable or a constant, and parameters can be provided in a parameter list or in a descriptor (SQLDA). A simple example of a CALL statement might look like this:
EXEC SQL CALL PROC1 ('000250', :lastname, :salary, :sqlcd)
The CALL statement shown above assumes that none of the input parameters can have null values. If you need to allow for null values, use indicator variables with the host variables, as follows:
EXEC SQL CALL PROC1 (:empno :empnoi,
:lastname :lnamei,
:salary :salaryi,
:sqlcd :sqlcdi)
If you do not know the parameter structure of the procedure, or if you prefer to use one structure rather than several host variables, you would use the following form of the CALL statement:
EXEC SQL CALL PROC1 USING DESCRIPTOR :sqlda
where sqlda is the name of an SQLDA. The parameter information must be put in the SQLDA before the CALL is issued.
The final example provides maximum flexibility:
EXEC SQL CALL :procname USING DESCRIPTOR :sqlda
where sqlda is the name of an SQLDA. The parameter information must be put in the SQLDA before the CALL is issued.
Authorization for stored procedures is done on a package level. That is, the issuer of the CALL statement must be authorized to run the package associated with the stored procedure. See Chapter 10, Assigning Authority and Privileges for more information on authorization.
On the CREATE PROCEDURE statement, you can specify an AUTHID. If you do, then only a user with that AUTHID can run the stored procedure. The AUTHID corresponds to the SQL ID of a connected user. This facility is useful for testing modifications to a stored procedure. It allows the database administrator to create a private copy of the stored procedure, modify and test it, without affecting the copy of the stored procedure that is publicly accessible. Once the stored procedure is fully tested, it can replace the existing, publicly accessible stored procedure.
The parameters for a stored procedure are defined on the CREATE PROCEDURE statement. The CREATE PROCEDURE statement makes an entry in SYSTEM.SYSPARMS for each parameter. The entry in SYSTEM.SYSPARMS indicates the datatype, size, and purpose (input, output, or both) of the parameter.
The stored procedure must have a declaration for each parameter that is
passed to it. The declaration of each parameter must be compatible with
the datatype and size specified for it in SYSTEM.SYSPARMS. Figure 34 shows the compatible definitions for parameters in C, COBOL,
PL/I, and Assembler.
Figure 34. Definitions of Stored Procedure Parameters
| SYSPARMS | C | COBOL | PL/I | Assembler |
|---|---|---|---|---|
| CHAR(n) | char varname[n+1] | PIC X(n) | CHAR(n) | CLn |
| CHAR(1) | char | PIC X(1) | CHAR(1) | CL1 |
| VARCHAR(n) | char varname[n+1] |
01 parm
49 parml
PIC S9(4) COMP
49 parmd
PIC X(n)
| CHAR(n) VARYING | H,CLn |
| SMALLINT | short | PIC S9(4) COMP | BIN FIXED(15) | H |
| INTEGER | long | PIC S9(9) COMP | BIN FIXED(31) | F |
| DECIMAL(x,y) | DECIMAL[(p,[s])] or DEC[(p,[s])] | PIC S9(x-y)V9(y) COMP-3 | DEC FIXED(x,y) | PLn['decimal constant'] or P'decimal constant' |
| REAL | float | COMP-1 | BIN FLOAT(21) | E |
| FLOAT | double | COMP-2 | BIN FLOAT(53) | D |
| GRAPHIC(n) | not supported | PIC G(n) DISPLAY-1 or PIC N(n) | GRAPHIC(n) | not supported |
| VARGRAPHIC(n) | not supported |
01 parm
49 parml
PIC S9(4) COMP
49 parmd
PIC G(n)
USAGE IS DISPLAY-1
or
49 parmd PIC N(n)
| GRAPHIC(n) VARYING | not supported |
Each of the high-level language definitions for stored procedure parameters support only a single instance (scalar value) of the parameter. There is no support for structure, array, or vector parameters. In some applications, it may be necessary to return a table of results, where the table represents multiple occurrences of one or more of the parameters passed to the stored procedure. Since this support is not provided by the SQL CALL statement, one of the following techniques may be used by the application to provide the required capability:
The datatype of a parameter provided on the CALL does not have to be identical to the datatype expected by the stored procedure, but it must be compatible. That is, if the stored procedure expects a CHAR(4) parameter, the caller can provide a character or varchar value with a length of 4 or less. Similarly, if the procedure expects an integer, most numeric datatypes (decimal, smallint, float) are acceptable, as long as the number is not too large to be represented by an integer. In general, datatypes that are considered compatible in other SQL statements are also considered compatible in an SQL CALL. That is, if the value being provided on the SQL CALL could be inserted into a column that has the same datatype as the stored procedure parameter, then it is valid for the SQL CALL statement.
For more information on datatype compatibility, see the DB2 Server for VSE & VM SQL Reference manual.
When an SQL CALL statement is issued, DB2 Server for VSE & VM builds a parameter list for the stored procedure, containing the parameters provided on the SQL CALL statement. When the the initial parameter list is built, the parameters contain the values established on entry to the SQL CALL statement. Eventually, the database manager will run the stored procedure and return values for the parameters to the calling program. If a stored procedure fails to set one or more of the output parameters, the database manager will not detect this fact. Instead, it will return the output parameter(s) to the calling program, with the value(s) established on entry to the SQL CALL statement.
In order for the stored procedure to receive parameters correctly, the stored procedure must be coded to accept the parameter list supplied by the database manager. DB2 Server for VSE & VM supports two parameter list conventions. The parameter list convention is determined by the value of the PARAMETERSTYLE column in the SYSTEM.SYSROUTINES catalog table, which can be GENERAL or GENERAL WITH NULLS.
If the GENERAL linkage convention is used:
For performance reasons, the calling application may choose to pass null indicators with the output parameters on the SQL CALL statement. If the null indicator associated with an output parameter is negative on entry to the SQL CALL statement, the application requester transmits only the null indicator to the server. This can be beneficial when dealing with large output parameters, since the entire output parameter is not transmitted to the server. Upon successful completion of the SQL CALL statement, none of the null indicators associated with the output parameters will be null, since the stored procedure is restricted to non-null parameter values.
When the GENERAL parameter list format is used, register 1 points to a list of addresses, which in turn point to the individual parameters. Figure 35 describes the GENERAL parameter list convention.
Figure 35. GENERAL parameter list
|
This is the default. If the GENERAL WITH NULLS linkage convention is used:
The indicator variables are passed to the stored procedure as a single parameter - an array of SMALLINT variables with an element for each indicator variable.
Figure 36 describes the GENERAL WITH NULLS parameter list convention.
Figure 36. GENERAL WITH NULLS parameter list
 |
The stored procedure must determine which input parameters are null by examining the array of indicator variables. The stored procedure must also assign values to the indicator variables when returning the output parameters to the calling program.
The array of indicator variables is not defined in the PARMLIST column of SYSTEM.SYSROUTINES, and is not specified as a parameter in the SQL CALL statement. In the SQL CALL statement in the client program, the indicator variables are coded after each parameter, for example:
EXEC SQL CALL PROCX (:parm1:indicator1, :parm2:indicator2)
or
EXEC SQL CALL PROCX (:parm1 INDICATOR :indicator1, :parm2 INDICATOR :indicator2)
In order to support the linkage conventions described above, the high level language application must be coded to support the required parameter list convention.
For examples of how to code stored procedures to receive and return parameters in C, COBOL, PL/I, or Assembler, refer to the appendix for that language.
The PLIST(OS) run-time option must be supplied.
The NOEXECOPS procedure option must be supplied.
In addition to returning parameters, a stored procedure can return query data, known as result sets. A result set is defined by declaring a cursor with the WITH RETURN clause, opening the cursor within the stored procedure, and leaving it open when the procedure returns. The resulting rows of data that can be fetched constitute a result set.
Notes:
|If a stored procedure attempts to return more than "z" result sets, |the SQL CALL statement completes with SQLCODE +464 and SQLSTATE 01609 and the |database manager returns the first "z" result sets.
|If the stored procedure returns 1 to "z" result sets, the SQL CALL |statement completes with SQLCODE +466 and SQLSTATE 01610 and the database |manager returns all the result sets.
|A client application program can receive and process result sets | | over DRDA from a stored procedure by using the following SQL |Extensions: |
|A client application programmer should consider the following when calling |a stored procedure that may return result sets: |
|For information on how to process result sets on clients other than DB2 |Server for VSE & VM Requester, refer to the following manuals: |
|If the number of result sets and the characteristics of each result set |are know, the following steps need to be performed in order to access each |result set: |
|Figure 37 shows the relationship among the new SQL statements and the |new data type.
|Figure 37. Relationship Among the New SQL Statements and the New Data Type
|
|After the SQL CALL statement is executed, the ASSOCIATE LOCATORS statement |is issued. The ASSOCIATE LOCATORS statement associates the result sets |returned by the stored procedure with the result-set locator variables |declared previously and specified in the ASSOCIATE LOCATORS statement (see (1) |in Figure 37). For each result set returned, the ALLOCATE CURSOR |statement is issued to assign a local cursor name to the result set locator |variable (see (2) in Figure 37). Then, the rows of each result set can be processed |by using the FETCH statement specifying the local cursor name (see (3) in Figure 37).
|Note that the order of the association of result sets and result set |locator variables is the order that the stored procedure used in opening the |cursor; the first open cursor issued by the stored procedure is |associated with the first result set locator variable, the second open cursor |issued by the stored procedure is associated with the second result set |locator variable, and so on. Also, note that only cursors that were |opened with the option WITH RETURN, and remain open after the procedure |terminates, are returned.
|Multiple result sets can be processed in parallel. For example, the |first row of the first result set is processed, the first row of the second |result set is processed, then the second row of the first result set is |processed.
|After the client program issues an SQL CALL statement, the DESCRIBE |PROCEDURE statement can be used to obtain information about the result sets |returned by the stored procedure. The DESCRIBE PROCEDURE statement |should be used when the number of result sets the stored procedure returned is |unknown. The DESCRIBE PROCEDURE returns the number of result sets |returned from the stored procedure and places information about the results |sets in SQLDA.
|Likewise, after the client program issued an SQL CALL statement, the |DESCRIBE CURSOR statement can be used to obtain information about a specific |result set returned by the stored procedure. The DESCRIBE CURSOR |statement should be used when the column names and data types of a particular |result set are unknown. After execution of the DESCRIBE CURSOR |statement, the SQLDA contains the information belonging to each column in the |result set.
|
| Note: | When the server is DB2 Server for VSE & VM, private protocol is not supported. These new statements are only supported for distributed applications. If any of these statements is executed over private protocol, the user will receive SQLCODE -947. |
|After the client program issues an SQL CALL statement, the DESCRIBE |PROCEDURE statement can be used to obtain information about the result sets |returned by the stored procdure. Figure 38 shows the DESCRIBE PROCEDURE statement.
|Figure 38. DESCRIBE PROCEDURE Statement
 |
|The DESCRIBE PROCEDURE statement should be used when the number of result |sets returned by the stored procedure is unknown. The DESCRIBE |PROCEDURE returns the number of result sets returned from the stored procedure |and places information about the result sets in an SQLDA, which must be made |large enough to hold the maximum number of result sets that the stored |procedure may return.
|To use the SQLDATA field from the DESCRIBE PROCEDURE statement, a result |set locator variable needs to be set up. A subscript variable is not |valid in an ALLOCATE CURSOR statement. For instance, the following is |required to use the SQLDATA variable for a COBOL program:
|... | | * Redefine the SQLDATA pointer as PIC S9(9) comp. | | 03 SQLDATA POINTER. | 03 SQLDATANUM REDEFINES SQLDATA S9(9) COMP. |... | | * Declare a result set locator variable to move the SQLDATA | * POINTER field too, to be used in the ALLOCATE CURSOR statement. | * You need to redefine this variable as PIC S9(9) comp. | | 01 LOCPTR SQL TYPE IS | RESULT-SET-LOCATOR VARYING. | 01 LOCNUM REDEFINES LOCPTR S9(9) COMP. |... | | * After the DESCRIBE PROCEDURE statement you can | * move the SQLDATANUM variable to the LOCNUM variable | | MOVE SQLDATANUM(INDEX) TO LOCNUM. | | * You can now allocate the cursor for the result set. | | EXEC SQL ALLOCATE CURSOR1 CURSOR FOR RESULT SET | :LOCPTR | END-EXEC. |...
|An alternative to using the SQLDATA field as shown above is to use the |ASSOCIATE LOCATORS statement to assign values to locator |variables.
|Once the application program issues an SQL CALL statement, the DESCRIBE |CURSOR statement can be used to obtain information about a specific result set |returned by the stored procedure. Figure 39 shows the DESCRIBE CURSOR statement.
|Figure 39. DESCRIBE PROCEDURE Statement
 |
|The DESCRIBE CURSOR statement should be used when the column names and data |types of a particular result set are unknown. After execution of the |DESCRIBE CURSOR statement, the contents of the SQLDA are similar to the |execution of a SELECT statement: |
|In an SQLVAR entry: |
|The following summarizes the steps to code a client application to |process result sets: |
|Fetching rows from a result set is the same as fetching rows from a |table.
|The following sections are examples of C language code that accomplish each |of the steps discussed above.
|The following example of C language code shows how to receive result |sets when the number of result sets returned is known. Coding for other |languages is similar.
| /*************************************************************/ | /* Declare result set locators. For this example, */ | /* assume you know that two result sets will be returned. */ | /* Also, assume that you know the format of each result set. */ | /*************************************************************/ | EXEC SQL BEGIN DECLARE SECTION; | static volatile SQL TYPE IS RESULT_SET_LOCATOR *loc1, *loc2; | EXEC SQL END DECLARE SECTION; | |... | /*************************************************************/ | /* Call stored procedure P1. */ | /* Check for SQLCODE +466, which indicates that result sets */ | /* were returned. */ | /*************************************************************/ | EXEC SQL CALL P1(:parm1, :parm2, ...); | | if(SQLCODE==+466) | { | /*************************************************************/ | /* Establish a link between each result set and its */ | |... | /*************************************************************/ | /* Associate a cursor with each result set. */ | /*************************************************************/ | EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1; | EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :loc2; | /*************************************************************/ | /* Fetch the result set rows into host variables. */ | /*************************************************************/ | while(SQLCODE==0) | { | EXEC SQL FETCH C1 INTO :order_no, :cust_no; | |... | } | while(SQLCODE==0) | { | EXEC SQL FETCH C2 :order_no, :item_no, :quantity; | |... | } | | /*************************************************************/ | /* All result sets have been processed, close allocated */ | /* cursor. */ | /*************************************************************/ | EXEC SQL CLOSE C1; | EXEC SQL CLOSE C2; |... | }
|The following example of C language code shows how to receive result |sets when the number of result sets returned, or what is in each result set, |is unknown.
| /*************************************************************/ | /* Declare result set locators. For this example, */ | /* assume that no more than three result sets will be */ | /* returned, so declare three locators. Also, assume */ | /* that you do not know the format of the result sets. */ | /*************************************************************/ | EXEC SQL BEGIN DECLARE SECTION; | static volatile SQL TYPE IS RESULT_SET_LOCATOR *loc1, *loc2, *loc3; | EXEC SQL END DECLARE SECTION; | |... | | /*************************************************************/ | /* Call stored procedure P2. */ | /* Check for SQLCODE +466, which indicates that result sets */ | /* were returned. */ | /*************************************************************/ | EXEC SQL CALL P2(:parm1, :parm2, ...); | | if(SQLCODE==+466) | { | /*************************************************************/ | { | /*************************************************************/ | /* Determine how many result sets P2 returned, using the */ | /* statement DESCRIBE PROCEDURE. :proc_da is an SQLDA */ | /* with enough storage to accommodate up to three SQLVAR */ | /* entries. */ | /*************************************************************/ | EXEC SQL DESCRIBE PROCEDURE P2 INTO :proc_da; | |... | /*************************************************************/ | /* Now that you know how many result sets were returned, */ | /* establish a link between each result set and its */ | /* locator using the ASSOCIATE LOCATORS. For this example, */ | /* we assume that three result sets are returned. */ | /*************************************************************/ | EXEC SQL ASSOCIATE LOCATORS (:loc1, :loc2, :loc3) WITH PROCEDURE P2; | |... | /*************************************************************/ | /* Associate a cursor with each result set. */ | /*************************************************************/ | EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1; | EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :loc2; | EXEC SQL ALLOCATE C3 CURSOR FOR RESULT SET :loc3; | | /*************************************************************/ | /* Use the statement DESCRIBE CURSOR to determine the */ | /* format of each result set. */ | /*************************************************************/ | EXEC SQL DESCRIBE CURSOR C1 INTO :res_da1; | EXEC SQL DESCRIBE CURSOR C2 INTO :res_da2; | EXEC SQL DESCRIBE CURSOR C3 INTO :res_da3; |... | /*************************************************************/ | /* Assign values to the SQLDATA and SQLIND fields of the */ | /* SQLDAs that you used in the DESCRIBE CURSOR statements. */ | /* These values are the addresses of the host variables and */ | /* indicator variables into which DB2 will put result set */ | /* rows. */ | /*************************************************************/ | |... | /*************************************************************/ | /* Fetch the result set rows into the storage areas */ | /* that the SQLDAs point to. */ | /*************************************************************/ | while(SQLCODE==0) | { | EXEC SQL FETCH C1 USING :res_da1; | |... | } | while(SQLCODE==0) | { | EXEC SQL FETCH C2 USING :res_da2; | |... | } | while(SQLCODE==0) | { | EXEC SQL FETCH C3 USING :res_da3; |... | } | | /*************************************************************/ | /* All result sets have been processed, close allocated */ | /* cursor. */ | /*************************************************************/ | EXEC SQL CLOSE C1; | EXEC SQL CLOSE C2; | EXEC SQL CLOSE C3; |... | | } |