Creating an external SQL procedure

An external SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language. 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.

To create an external SQL procedure:

1. Use one of the following methods to create the external SQL procedure:
   • IBM® Data Studio. See Developing database routines (IBM Data Studio, IBM Optim Database Administrator, IBM infoSphere Data Architect, IBM Optim Development Studio).
   • JCL
   • The DB2 for z/OS® SQL procedure processor (DSNTPSMP)
   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.
   Restriction: If you plan to use the DB2 stored procedure debugger or the Unified Debugger, do not use JCL. Use either IBM Data Studio or DSNTPSMP.

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

2. Authorize the appropriate users to use the stored procedure by issuing the GRANT EXECUTE statement.