Here’s a brief overview/definition of Db2 for z/OS Stored Procedures & SQL Procedures – including their evolution:

An SQL procedure is a stored procedure that contains only SQL statements.

The source code for these procedures (the SQL statements) is specified in CREATE PROCEDURE statement. The part of the CREATE PROCEDURE statement that contains SQL statements is called the procedure body.

Types of SQL procedures

Db2 for z/OS® supports the following types of SQL procedures:

Native SQL procedures

The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is contained and specified in the procedure definition along with various attributes of the procedure. A package is generated for a native SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2. Each time that the procedure is invoked, the package executes one or more times.

All SQL procedures that are created with a CREATE PROCEDURE statement that does not specify the FENCED or EXTERNAL options are native SQL procedures. More capabilities are supported for native SQL procedures, they usually perform better than external SQL procedures, and no associated C program is generated for them.

For more information, see Creating native SQL procedures.

External SQL procedures (deprecated)

The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is specified in the procedure definition along with various attributes of the procedure. A C program and an associated package are generated for an external SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2. Each time that the procedure is invoked, the package executes one or more times.

Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.

For more information, see Creating external SQL procedures (deprecated).

An external SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language (SQL PL). However, an external SQL procedure is created, implemented, and executed like other external stored procedures. All SQL procedures that were created prior to DB2® 9 are external SQL procedures.

Before you begin

Deprecated function: External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.

Before you create an external SQL procedure, Configure Db2 for running stored procedures and user-defined functions during installation or Configure Db2 for running stored procedures and user-defined functions during migration.

If you plan to use the Db2 stored procedure debugger or the Unified Debugger, do not use JCL. Use DSNTPSMP instead.

If you plan to use DSNTPSMP, you must set up support for external SQL procedures.

Native SQL procedures

A native SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language (SQL PL). A native SQL procedure is created by issuing a single SQL statement, CREATE PROCEDURE. Native SQL procedures do not require any other program preparation, such as precompiling, compiling, or link-editing source code. Native SQL procedures are executed as SQL statements that are bound in a Db2 package. Native SQL procedures do not have an associated external application program. Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.

Native SQL procedures have the following advantages:

·       You can create them in one step.

·       They do not run in a WLM environment.

·       They might be eligible for zIIP redirect if they are invoked remotely through a DRDA client.

·       They usually perform better than external SQL procedures.

·       They support more capabilities, such as nested compound statements, than external SQL procedures.

·       Db2 can manage multiple versions of these procedures for you.

·       You can specify that the SQL procedure commits autonomously, without committing the work of the calling application.

All SQL procedures that are created without the FENCED or EXTERNAL options in the CREATE PROCEDURE statement are native SQL procedures.

External SQL procedures (deprecated)

An external SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language (SQL PL). However, an external SQL procedure is created, implemented, and executed like other external stored procedures.

Deprecated function: External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.

All SQL procedures that were created prior to DB2® 9 are external SQL procedures. Starting in Version DB2 9, you can create an external SQL procedure by specifying FENCED or EXTERNAL in the CREATE PROCEDURE statement.

A stored procedure is a compiled program that can execute SQL statements.

Stored procedures are stored at theDb2 local or remote server where they run. A typical stored procedure contains two or more SQL statements and some manipulative or logical processing in a program. A client application program uses the SQL CALL statement to invoke the stored procedure.

An external stored procedure is a procedure that is written in a host language and can contain SQL statements. The source code for external procedures is separate from the definition. An external stored procedure is much like any other SQL application.