This concept also called External stored procedure.
There are 3 kinds of procedures:
- External high-level language procedures
- External SQL language procedures
- Native SQL language procedures
External high-level language procedures: This kind of stored procedures we use when any program written with DB2 code with a language Cobol,PL/I, C, C++, Assembler, REXX, and Java. This kind of program we can call through stored procedure.
The process is as follows: This is an example I am giving here for Cobol.
- After writing Cobol programs with Static/Dynamic sqls do pre-compile,compile and link-edit the program.You will get load module now. While compiling take care that it is re-entrant.
- if your stored procedure cannot be reentrant, link-edit it as non-reentrant and non-reusable. The non-reusable attribute prevents multiple tasks from using a single copy of the stored procedure at the same time.
- This program we need to bound with only package not with Plan
- Create definition of the stored procedure. Give name of the Load module and other parameters
- Add the load module in STEPLIB DD of WLM JCL
Cobol Module with DB2 Sqls:
PROCEDURE DIVISION USING …
Create Stored Procedure-Definition:
CREATE PROCEDURE DEVL7083.EMPDTL1C
IN PEMPNO CHAR(6)
,OUT PFIRSTNME VARCHAR(12)
,OUT PMIDINIT CHAR(1)
,OUT PLASTNAME VARCHAR(15)
,OUT PWORKDEPT CHAR(3)
,OUT PHIREDATE DATE
,OUT PSALARY DEC(9,2)
,OUT PSQLCODE INTEGER
,OUT PSQLSTATE CHAR(5)
,OUT PSQLERRMC VARCHAR(250)
RESULT SETS 0
EXTERNAL NAME EMPDTL1C
PARAMETER STYLE GENERAL
MODIFIES SQL DATA
WLM ENVIRONMENT DB9AWLM
STAY RESIDENT NO
PROGRAM TYPE SUB
COMMIT ON RETURN NO ;
Before developing Stored procedure setup of environment is needed:
- The WLM environment must be set up.
- The LE environment must be set up.
- The stored procedure must be defined to DB2. Note in particular that if the stored procedure is designed to return result sets, the maximum number of result sets that can be returned is specified in the definition.
- Develop the stored procedure.
- Grant the necessary privileges to the authorization ID of the user that executes the stored procedure.
- Develop the calling application if needed.
Refer the IBM link for more details.