We can implement stored procedure for DB2 for z/OS include C, C++, COBOL, z/OS Assembler, PL/I, REXX, SQL Procedures language (both external and native), and Java.
The parameters required to Stored procedures we define in Linkage section.
Nesting Stored Procedure: When one stored procedure calls another stored procedure, it is referred to as a nested procedure call. DB2 supports 16 levels of nesting. When more than 16 levels of nesting are attempted a –746 SQLCODE is returned
Some tips for Stores procedure:
The program preparation process for stored procedures is essentially the same as for any program that accesses DB2. The program code must be precompiled, compiled, and then link-edited into an executable form. The DBRM must be bound into a package; no plan is required for the stored procedure.
| Note |
A plan is still required for the calling program. Only the stored procedure (the called program) does not require a plan. The package for the stored procedure does not have to be bound in the package list of the calling program. |
When the program for the stored procedure is link-edited, you must include either DSNALI (for CAF) or DSNRLI (for RRSAF), depending on which attachment facility is to be used. Of course, you also can link-edit the stored procedure program to run in multiple environments if you choose.
No impact to the program preparation process is required for the calling program; you should use normal DB2 program preparation steps.
Calling stored proc:
EXEC SQL
CALL SAMPLE(‘ABC’)
END-EXEC.
Error handling:
STOP AFTER FAILURE, STOP AFTER n FAILURES, and CONTINUE AFTER FAILURE.
Related articles
- DB2- Zo/S Top Ten Common SQL Mistakes (srinimf.com)
Srinimf 
One thought
Comments are closed.