SQL procedures

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

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

DB2® for z/OS® supports the following two types of SQL procedures:
Native SQL procedures
A procedure with a procedural body that is written entirely in SQL and is created by issuing a single SQL statement, CREATE PROCEDURE. Native SQL procedures do not have an associated external application program.
External SQL procedures
A procedure whose body is written entirely in SQL, but is created, implemented, and executed like other external stored 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. 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 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.

Starting in Version 9.1, all SQL procedures that are created without the FENCED or EXTERNAL options in the CREATE PROCEDURE statement are native SQL procedures.

External SQL procedures

An external stored procedure is a procedure that is written in a host language. An external stored procedure is much like any other SQL application. It can include static or dynamic SQL statements, IFI calls, and DB2 commands that are issued through IFI. You prepare external stored procedures as you would normally prepare application programs. You precompile, compile, and link-edit them. Then, you bind the DBRM into a package. You also need to define the procedure to DB2 by using the CREATE PROCEDURE statement. Thus, the source code for an external stored procedure is separate from the definition for the stored procedure.

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