DB2-stored procedure

Stored procedures can be written in COBOL, PL/1, REXX and Java etc.

Db2 stored procedures are handled by WLM -work Load Manager.

Main uses are reusability and security. How to write stored procedure?

The below code shows of it:

CREATE PROCEDURE UPDATE_SALARY (IN EMPLOYEE_NUM CHAR(6), IN RATE1 DECIMAL(5,2))

LANGUAGE SQL

WLM ENVIRONMENT SAMP1

COMMIT ON RETURN YES

IF RATE1 <= 0.7

THEN UPDATE EMP

SET SALARY = SALARY * RATE1

WHERE EMPNO= EMPLOYEE_NUM;

ELSE  UPDATE EMP

SET SALARY = SALARY * 0.7

WHERE EMPNO = EMPLOYEE_NUM;

END IF

There are three parameters passed to SP(stored procedure). IN- Input,  OUT-Output INOUT-Input and output.

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.