A STORED PROCEDURE generally contains the SQLs which are often used in one or more programs. The main advantage of STORED PROCEDURE is that it reduces the data traffic between the COBOL and DB2 as the STORED PROCEDURES resides in DB2.
A COBOL-DB2 program can call a STORED PROCEDURE using a CALL statement and we can have nested STORED PROCEDURE upto 16 levels. For example, if we have STORED PROCEDURE with a name ORDERSTAT, then we can call it in our COBOL-DB2 program using the below command:
EXEC SQL CALL ORDERSTAT (:WS-ORDER-ID, :WS-ORDER-STATUS) END-EXEC
In order to create a DB2 procedure, we can give definition as below.
CREATE PROCEDURE ORDERSTAT ( IN ORDER-ID int, OUT ORDER-STAT char)
We can define the STORED PROCEDURE as below.
LANGUAGE SQL PROCA: BEGIN DECLARE ORDERID int; SELECT ORDER_STAT FROM ORDERS WHERE ORDER_ID = ORDERID; END P1
Below are some of the advantages of using STORED PROCEDURE.