Stored procedures are primarily used to reduce multiple requests between client/server to execute multiple queries. Using stored procedure, all the SQL queries and procedural statements required are executed at once at the server side, by calling the stored procedure with the required input values.

Stored procedures can be Native (Completely SQL) or External Stored procedure where the actual code along with SQL is written in external language like COBOL.

For an external stored procedure, we need to follow below steps.

• We need a DB2 stored procedure created with the Language as COBOL and giving an External name (COBOL DB2 Program Name), where the actual logic resides

Sample Create Procedure Statement:

CREATE PROCEDURE SCHEMA.ACCTCLOSURE (
IN IP_ACCTNO INTEGER,
IN IP_CLOSE_DATE DATE FOR SBCS DATA CCSID EBCDIC,
OUT OP_FINAL_AMT DECIMAL(12, 0)
)
LANGUAGE COBOL
PARAMETER STYLE GENERAL WITH NULLS
EXTERNAL NAME ‘ACTC001’
FENCED
CALLED ON NULL INPUT
MODIFIES SQL DATA
COLLID AAAA000P
WLM ENVIRONMENT WLMPROD
PROGRAM TYPE MAIN
SECURITY DB2;

This Create Procedure statement will also be moved using the change management tool with a specific element Type. (In Endevor, STOP (Stored Procedure) is the Type that was used)

This Create statement will ensure the creation of the DB2 procedure and linking with the COBOL program ACTC001.

• Now the COBOL DB2 program ACTC001 needs to be coded as a normal COBOL DB2 program which has the IN and OUT parameters declared in the procedure as part of Linkage section (also as part of PROCEDURE DIVISION USING).

Using the IN parameters, required SQL and Procedural statements are executed and the OUT variables are populated with required values.

• Now we need to call the Procedure either from a Batch program or Online program or from external processes.

To call this new procedure from a COBOL program, below SQL CALL statement needs to be executed after populating the IN parameters.

EXEC SQL
CALL ACCTCLOSURE
( :ACTC001-IP-ACCTNO
, : ACTC001-IP-CLOSE-DATE
, : ACTC001-OP-FINAL-AMT)
END-EXEC.

This CALL will invoke the procedure and executes the code and returns the output variable value. We can check for a successful SQL CODE and proceed using OUT variable value that we have got.

Common SQLCODES:
-430 : Stored procedure was successfully invoked but terminated abnormally
-440 : DB2 can not find stored procedure, as the name provided is wrong
-444 : External COBOL element Load module is not found

Project Scenario

As part of my project, I had to write an external stored procedure using COBOL that has the following functionality. When customers of the bank want to close their account, they would need the final amount that needs to be paid to the bank, so that they are eligible for closure. The new stored procedure takes input of the Account number and the Date on which customer wants to end the account relationship, and then calculates the final amount that he has to pay, by combining all the day to day transactions, and any charges like finance charges or interests.

Testing was done by creating a Driver COBOL program which takes the required IN parameters values using PARM (in JCL) and displays the output in the SYSOUT. This Driver COBOL program internally calls the stored procedure with the IN values and displays the OUT value.

——————————————————————————————————–

In United States, If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog

——————————————————————————————————–

You may also like to look at: