Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Updated on: 01-Dec-2020
3K+ Views
Advertisements