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.

Click to collapse

Advertisements

One thought on “DB2- How to invoke a procedure in mainframe?

Comments are closed.