DB2 Stored procedure syntax CALL in COBOL module

Big data analytics Career guide
[big data analytics guide . You can change career]
What is the role of stored procedure in DB2

  • Workloads in a client/server environment can be moved from the client to the sever by creating one or more stored procedures.
  • Once a stored procedure has been created and registered with a database (by executing the CREATE PROCEDURE SQL statement), that procedure can be invoked, either interactively, using a utility such as the Command Line Processor, or from a client application.
  • Stored procedures are invoked by executing the CALL SQL statement.

DB2 Tutorial | Part-1 | Part-2 | Part-3

The basic syntax for this statement is:

ALL [ProcedureName](<[InputParameter]|
[OutputParameter] | NULL>, …)

Where: 
ProcedureName- Identifies the name assigned to the procedure to be invoked.
InputValue -Identifies one or more parameter values that are to be passed to the procedure being invoked.
OutputParameter – Identifies one or more parameter markers or host variables that are to receive return values from the procedure being invoked.

Creating a Stored Procedure

CREATE PROCEDURE get_sales 
(IN quota INTEGER, OUT retcode CHAR(5)) DYNAMIC RESULT
SETS 1 LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5); DECLARE sales_results
CURSOR WITH RETURN
FOR
SELECT sales_person,
       SUM(sales) AS total_sales
FROM sales
GROUP BY sales_person
HAVING SUM(sales) > quota; DECLARE EXIT
HANDLER
FOR
SQLEXCEPTION
SET retcode = SQLSTATE; OPEN sales_results;
SET retcode = SQLSTATE; END

This procedure could be invoked from the Command Line Processor by connecting to the appropriate database and executing a CALL statement that looks like this:

CALL get_sales (25, ?)
If you created this procedure and executed this statement after connecting to the SAMPLE database provided with DB2, the value 25 would be passed to the input parameter named QUOTA, and a question mark (?) would be used as a place-holder for the value that will be returned in the output parameter named RETCODE.

The procedure would then execute the SQL statements contained in it and return information that looks something like this:

Value of output parameters
 ---------------------------
 Parameter Name : RETCODE
 Parameter Value : 00000
 Result set 1
 -----------
 SALES_PERSON TOTAL_SALES
 ------------ ------------
 GOUNOT 50
 LEE 91
 2 record(s) selected.
 Return Status = 0
 ....
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.