Stored Procedures

Stored procedures are compiled routines stored in DB2 that bundle one or more SQL statements and logic into a single callable unit.

What is a stored procedure

• It is a named program stored in the database that can be called from COBOL, JCL jobs, SPUFI, or another SQL program.
• It reduces network traffic because only one CALL is sent instead of many SQL statements.
• Business logic stays in one place, so all programs use the same tested rules.
• It improves performance because the SQL inside is precompiled and its access path is stored in the DB2 catalog.

Types of stored procedures

• Native SQL procedures :- Written fully in SQL PL (SQL Procedural Language) with BEGIN...END blocks. Easiest to write and maintain.
• External procedures :- Written in COBOL, C, or Java and called from SQL. Used when complex logic or existing program code is needed.
• External SQL procedures :- SQL PL code stored as text in the catalog; DB2 generates a program from it at CREATE time.
• Procedures can take IN, OUT, and INOUT parameters to pass data in and return results back.

Example: creating and calling a stored procedure

• Example of a native SQL procedure that returns an employee name:-
  CREATE PROCEDURE GET_EMP_NAME (IN P_EMPNO CHAR(6), OUT P_NAME VARCHAR(30)) LANGUAGE SQL READS SQL DATA BEGIN SELECT FIRSTNME || ' ' || LASTNAME INTO P_NAME FROM EMPLOYEE WHERE EMPNO = P_EMPNO; END

Calling a stored procedure

• Call it with the CALL statement from SPUFI, QMF, or any tool:-
  CALL GET_EMP_NAME('000010', ?);
• Call it from a COBOL program like this:-
  EXEC SQL CALL GET_EMP_NAME(:HV-EMPNO, :HV-NAME) END-EXEC.
• DB2 must know where to run it, so the procedure is defined in a WLM (Workload Manager) application environment on z/OS.
• GRANT EXECUTE privilege to the users or roles that are allowed to call it.