How to write COBOL Program in DB2 stored procedure

DB2 Stored procedure jobs
DB2 Stored procedure jobs

I want to add more points on how to write COBOL program in DB2 stored procedures. The functionality of below stored procedures is:

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

Here procedure name is : GETPRML

Passing two input values and getting two output values. Usually, IBM Data studio will be used to develop stored procedures. Once a stored procedure is created in DB2, it can be accessed by different operating systems. Ex:-Mainframe-Zo/s, iseries (AS400), Windows, Linux etc.,. Because, Stored procedure is a database object, and it does not specific to any particular operating system.

Language: It is COBOL. Since we are writing stored procedure in COBOL

Deterministic: This is an instruction to DB2, how it responses output values. Read more…

Reads SQL Data: It means it does not modify SQL data. It only reads SQL data. Read more… 

External name: This definition tells it is for external stored procedure

COLLID : This is collection id. Related to DBRM of this procedure bound to a package collection id.

ASUTIME: Total amount of processor time

Parameter Style:  General- Passing input parameters. Read more..

Stay Resident: If stored procedure to remain in memory after procedure ends.

RUN Options: You need to pass run time options. Read more…

Program type: This can be MAIN (main program) or SUB (sub-program)

Security: This means security at DB2 level

Result sets: Specifies the maximum number of query result sets that the procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that the procedure can return no result sets. The value of integer must be between 0 and 32767.

Commit on return: NO/YES, if DB2 to issue commit after procedure ends.

How to write a Stored Procedure in COBOL

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. 10/03/2015.

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.

Related: Powerful steps to create Stored procedure

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.