Adapter for DB2 Stored Procedure Support (CLI Only)

How to:

DB2 stored procedures are procedures that are compiled and stored in the DB2 database. These procedures must be developed within DB2 using the CREATE PROCEDURE command. Once created, requests can be run against a stored procedure using SQL Passthru, if you are using the CLI version of the adapter.

The adapter supports stored procedures with IN, OUT, and INOUT parameters.

The output parameter values that are returned by stored procedures are available as result sets. These values form a single-row result set that is transferred to the client after all other result sets are returned by the invoked stored procedure. The names of the output parameters (if available) become the column titles of that result set.

Note that only the output parameters (and the returned value) referenced in the invocation string are returned to the client. As a result, users have full control over which output parameters have values displayed.

FOCUS supports invocation of stored procedures written according to the rules of the underlying DBMS. Note that the examples shown in this section are SQL-based. See the DBMS documentation for rules, languages, and additional programming examples.

Top of page
x
Syntax: How to Invoke a Stored Procedure
SQL DB2 
  EX procname [parameter_specification1] 
[,parameter_specification2]...
END

where:

DB2

Is the ENGINE suffix for DB2.

procname

Is the name of the stored procedure. It is the fully or partially qualified name of the stored procedure in the native RDBMS syntax.

parameter_specification

IN, OUT, and INOUT parameters are supported. Use the variation required by the stored procedure:

IN

Is a literal (for example, 125, 3.14, 'abcde'). You can use reserved words as input. Unlike character literals, reserved words are not enclosed in quotation marks (for example, NULL). Input is required.

OUT

Is represented as a question mark (?). You can control whether output is passed to an application by including or omitting this parameter. If omitted, this entry will be an empty string (containing 0 characters).

INOUT

Consists of a question mark (?) for output and a literal for input, separated by a slash: /. (For example: ?/125, ?/3.14, ?/'abcde'.) The out value can be an empty string (containing 0 characters).
Example: Invoking a Stored Procedure

In this example, a user invokes a stored procedure, USER1.TEST.PROC01, supplies input values for parameters 1, 3, 5 and 7, and requests the returned value of the stored procedure, as well as output values for parameters 2 and 3.

Note that parameters 4 and 6 are omitted. The stored procedure will use their default values, as specified at the time of its creation.

SQL DB2
 EX USER1.TEST.PROC01 125,?,?/3.14,,'abc',,'xyz'
END
Example: Sample Stored Procedure

The following stored procedure uses an IN parameter:

CREATE PROCEDURE STPROC1                                  
(IN FNAME CHAR(30))                                      
RESULT SETS 1                                            
LANGUAGE SQL                                             
BEGIN                                                    
                                                         
DECLARE CSNUM INTEGER;                                   
                                                         
DECLARE GETTBINFO CHAR(50);                              
DECLARE C1 CURSOR WITH RETURN FOR S1;                    
                                                         
SET GETTBINFO = 'SELECT CREATOR FROM SYSIBM.SYSTABLES';  
                                                         
PREPARE S1 FROM GETTBINFO;                               
OPEN C1;                                                 
                                                         
END

The following Direct SQL Passthru command runs the stored procedure and issues a report request against the SQLOUT Master File that is generated for any Direct SQL Passthru SELECT statement.

SQL DB2           
EX STPROC1('ABC');
TABLE FILE SQLOUT 
PRINT *           
IF READLIMIT EQ 10
END

The output is:

CREATOR 
------- 
DB2GSE  
DB2GSE  
DB2GSE  
DB2GSE  
DB2GSE  
DB2GSE  
DB2GSE  
DB2PM   
DB2PM   
DB2PM

For more information about Direct SQL Passthru, see Direct SQL Passthru.

Information Builders