DB2-Calling Cobol,C,Java Programs from Stored Proc

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.

  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 DB2 Sqls:

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 ;

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.

About these ads