How can a COBOL-DB2 program call a STORED PROCEDURE? Give an example.

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:

Example

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.

Example

CREATE PROCEDURE ORDERSTAT ( IN ORDER-ID int,
OUT ORDER-STAT char)

We can define the STORED PROCEDURE as below.

Example

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.

• The core logic and algorithm is stored centrally at DB2 and managed by DBMS. This helps in the reusability and saves effort of modification at only a single central location.
• The access to the stored procedures can be restricted based on the permissions set for different profiles within DB2.
• The logic is executed at the database server which reduces the traffic at DB2 network and hence decreasing the overall execution time.