Migrating an external SQL procedure to a native SQL procedure

You can migrate an existing external SQL procedure, which is deprecated, to a native SQL procedure by dropping the existing procedure and creating it again as a native SQL procedure. Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.

Before you begin

If you created the external SQL procedure in a previous release of Db2, consider the release incompatibilities for applications that use stored procedures, examine your external SQL procedure source code, and make any necessary adjustments. See Application and SQL release incompatibilities.

About this task

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.

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.

Procedure

To migrate an external SQL procedure to a native SQL procedure, complete the following steps:

  1. Find and save the existing CREATE PROCEDURE and GRANT EXECUTE statements for the existing external SQL procedure.
  2. Drop the existing external SQL procedure by using the DROP PROCEDURE statement.
  3. Re-create the procedure as a native SQL procedure by using the same CREATE PROCEDURE statement that you used to originally create the procedure, with both of the following changes:
    • If the procedure was defined with the options FENCED or EXTERNAL, remove these keywords.
    • Either remove the WLM ENVIRONMENT keyword, or add the FOR DEBUG MODE clause.
    • If the procedure body contains statements with unqualified names that could refer to either a column or an SQL variable or parameter, qualify these names. Otherwise, you might need to change the statement.

      Db2 resolves these names differently depending on whether the procedure is an external SQL procedure or a native SQL procedure. For external SQL procedures, Db2 first treats the name as a variable or parameter if one exists with that name. For native SQL procedures, Db2 first treats the name as a column if a column exists with that name. For example, consider the following statement:

      CREATE PROCEDURE P1 (INOUT C1 INT) ... SELECT C1 INTO xx FROM T1

      In the preceding example, if P1 is an external SQL procedure, C1 is a parameter. For native SQL procedures, C1 is a column in table T1. If such a column does not exist, C1 is a parameter.

  4. Issue the same GRANT EXECUTE statements that you used to originally grant privileges for this stored procedure.
  5. Increase the value of the TIME parameter on the job statement for applications that call stored procedures.
    Important: This change is necessary because time for SQL external stored procedures is charged to the WLM address space, while time for native SQL stored procedures is charged to the address space of the task.
  6. Test your new native SQL procedure.