Example COBOL stored procedure with a GENERAL linkage convention

You can call a stored procedure that uses the GENERAL linkage convention from a COBOL program.

This example stored procedure does the following:

Searches the catalog table SYSROUTINES for a row matching the input parameters from the client program. The two input parameters contain values for NAME and SCHEMA.
Searches the Db2 catalog table SYSTABLES for all tables in which the value of CREATOR matches the value of input parameter SCHEMA. The stored procedure uses a cursor to return the table names.

This stored procedure is able to return a NULL value for the output host variables.

The linkage convention for this stored procedure is GENERAL.

The output parameters from this stored procedure contain the SQLCODE from the SELECT operation, and the value of the RUNOPTS column retrieved from the SYSROUTINES table.

The CREATE PROCEDURE statement for this stored procedure might look like this:

CREATE PROCEDURE GETPRML(PROCNM CHAR(18) IN, SCHEMA CHAR(8) IN,
  OUTCODE INTEGER OUT, PARMLST VARCHAR(254) OUT)
  LANGUAGE COBOL
  DETERMINISTIC
  READS SQL DATA
  EXTERNAL NAME "GETPRML"
  COLLID GETPRML
  ASUTIME NO LIMIT
  PARAMETER STYLE GENERAL
  STAY RESIDENT NO
  RUN OPTIONS "MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)"
  WLM ENVIRONMENT SAMPPROG
  PROGRAM TYPE MAIN
  SECURITY DB2
  RESULT SETS 2
  COMMIT ON RETURN NO;

 CBL RENT
 IDENTIFICATION DIVISION.
 PROGRAM-ID. GETPRML.
 AUTHOR. EXAMPLE.
 DATE-WRITTEN.   03/25/98.
 
 ENVIRONMENT DIVISION.
 INPUT-OUTPUT SECTION.
 FILE-CONTROL.
 DATA DIVISION.
 FILE SECTION.
 
 WORKING-STORAGE SECTION.
 
     EXEC SQL INCLUDE SQLCA END-EXEC.
***************************************************
*   DECLARE A HOST VARIABLE TO HOLD INPUT SCHEMA
***************************************************
 01  INSCHEMA PIC X(8).
 
***************************************************
*   DECLARE CURSOR FOR RETURNING RESULT SETS
***************************************************
*
     EXEC SQL DECLARE C1 CURSOR WITH RETURN FOR
       SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR=:INSCHEMA
     END-EXEC.
*
 LINKAGE SECTION.
***************************************************
*   DECLARE THE INPUT PARAMETERS FOR THE PROCEDURE
***************************************************
 01  PROCNM  PIC X(18).
 01  SCHEMA  PIC X(8).
*******************************************************
*   DECLARE THE OUTPUT PARAMETERS FOR THE PROCEDURE
*******************************************************
 01  OUT-CODE PIC S9(9) USAGE BINARY.
 01  PARMLST.
     49 PARMLST-LEN  PIC S9(4) USAGE BINARY.
     49 PARMLST-TEXT PIC X(254).
 
 PROCEDURE DIVISION USING PROCNM, SCHEMA,
           OUT-CODE, PARMLST.

*******************************************************
* Issue the SQL SELECT against the SYSIBM.SYSROUTINES
* DB2 catalog table.
*******************************************************
     EXEC SQL
       SELECT RUNOPTS INTO :PARMLST
         FROM SYSIBM.ROUTINES
         WHERE NAME=:PROCNM AND
         SCHEMA=:SCHEMA
     END-EXEC.
 
*******************************************************
*  COPY SQLCODE INTO THE OUTPUT PARAMETER AREA
*******************************************************
      MOVE SQLCODE TO OUT-CODE.
*******************************************************
* OPEN CURSOR C1 TO CAUSE DB2 TO RETURN A RESULT SET
* TO THE CALLER.
*******************************************************
     EXEC SQL OPEN C1
     END-EXEC.
 PROG-END.
      GOBACK.