Logic to call COBOL program from stored Procedure

This concept is also called External stored procedure. In DB2, you can call COBOL module from stored procedure. Before you go into details, let me tell you about types of Stored procedures in DB2. You all know that there are 3 kinds of procedures:

1. External high-level language procedures
2. External SQL language procedures
3. 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 and this is an example I am giving here for COBOL.

1. 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.
2. 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.
3. This program we need to bound with only package not with Plan
4. Create definition of the stored procedure. Give name of the Load module and other parameters
5. Add the load module in STEPLIB DD of WLM JCL

COBOL Module with SQL:

ID DIVISION.
PROGRAM-ID. XFEREMP.
.
.
.
LINKAGE SECTION.
01 ...
PROCEDURE DIVISION USING ...
EXEC SQL
...
END-EXEC.
.
.
.
GOBACK.

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
LANGUAGE COBOL
PARAMETER STYLE GENERAL
MODIFIES SQL DATA
NO DBINFO
WLM ENVIRONMENT DB9AWLM
STAY RESIDENT NO

COLLID DEVL7083
PROGRAM TYPE SUB
COMMIT ON RETURN NO ;

The setup environment before you write Stored procedure:

• 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.

Related Posts