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.

Procedure

To create an external SQL procedure:

  1. Use one of the following methods to create the external SQL procedure:
    The preceding methods that you use to create an external SQL procedure perform the following actions:
    • Convert the external SQL procedure source statements into a C language program by using the Db2 precompiler
    • Create an executable load module and a Db2 package from the C language program.
    • Define the external SQL procedure to Db2 by issuing a CREATE PROCEDURE statement either statically or dynamically.
  2. Authorize the appropriate users to use the stored procedure by issuing the GRANT EXECUTE statement.

Example

For examples of how to prepare and run external SQL procedures, see Sample programs to help you prepare and run external SQL procedures.