How to Invoke a DB2 Stored Procedure in Mainframe

We can implement stored procedures for DB2 for z/OS including C, C++, COBOL, z/OS Assembler, PL/I, REXX, SQL Procedures language (both external and native), and Java.

The parameters required to Store procedures we define in the 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 Storeed 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 link-edited into an executable form. The DBRM must be bound into a package; no plan is required for the stored procedure.

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 will 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)