Adapter for DB2 Stored Procedure Support (CLI Only)
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. After creating the procedure, you can use the CREATE SYNONYM command to generate a Master and Access File for the stored procedure. You can then run requests against the generated synonym to report against the stored procedure, 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.
Sample Stored Procedure
The following stored procedure uses IN and OUT parameters:
CREATE PROCEDURE SPSAMP (IN CNAME CHAR(30), OUT OUTVAL CHAR(50))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GETTBINFO CHAR(100);
DECLARE TRIMCNAME VARCHAR(35);
DECLARE C1 CURSOR WITH RETURN FOR S1;
SET TRIMCNAME = '''%' || TRIM(CNAME) || '%''';
SET GETTBINFO =
'SELECT COLCOUNT,NAME FROM SYSIBM.SYSTABLES WHERE CREATOR LIKE ' || TRIMCNAME;
SET OUTVAL='TABLES FOR USER ' || CNAME;
PREPARE S1 FROM GETTBINFO;
OPEN C1;
END
The following CREATE SYNONYM command generates a Master File and Access File for the stored procedure. Note that the supplied input parameter is a value, while the output parameter is represented by a question mark (?). For information about the CREATE SYNONYM command, see Generating a Master and Access File Using the CREATE SYNONYM Command.
CREATE SYNONYM FSPSAMP DROP
FOR USER1.SPSAMP
DBMS DB2
AT CON1
STOREDPROCEDURE
PARMS "'USER1',?"
END
The following Master File is generated as a result of the CREATE SYNONYM command.
FILENAME=FSPSAMP, SUFFIX=DB2 , $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=CNAME, ALIAS=P0001, USAGE=A30, ACTUAL=A30,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=OUTVAL, ALIAS=P0002, USAGE=A50, ACTUAL=A50,
MISSING=ON, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=COLCOUNT, ALIAS=COLCOUNT, USAGE=I6, ACTUAL=I2, $
FIELDNAME=NAME, ALIAS=NAME, USAGE=A128V, ACTUAL=A128V, $
The following Access File is generated as a result of the CREATE SYNONYM command.
SEGNAME=INPUT, CONNECTION=CON1, STPNAME=USER1.SPSAMP, $ SEGNAME=OUTPUT, STPRESORDER=0, $ SEGNAME=ANSWERSET1, STPRESORDER=1, $
The following request invokes the stored procedure, specifying a value for the input parameter, CNAME.
TABLE FILE FSPSAMP PRINT NAME COLCOUNT WHERE CNAME EQ 'SYSIBM' HEADING "<OUTVAL " " " END
The output is:
PAGE 1
TABLES FOR USER SYSIBM
COLCOUNT NAME
-------- ----
10 SYSOBDS
16 SYSCONTEXT
5 SYSCTXTTRUSTATTRS
7 SYSCONTEXTAUTHIDS
33 SYSCOPY
16 SYSCOLAUTH
41 SYSCOLUMNS
7 SYSFOREIGNKEYS
59 SYSINDEXES
35 SYSINDEXPART
8 SYSKEYS
16 SYSRELS
9 SYSSYNONYMS
30 SYSTABAUTH
46 SYSTABLEPART
59 SYSTABLES