Altering stored procedures

The process that you follow to alter a stored procedure depends on the type of stored procedure and how you want to alter it.

About this task

You can alter stored procedures in the following ways:
  • For a native SQL procedure, you can alter the options and the body, and you can manage multiple versions.
  • For an external stored procedure (a procedure that is written in a host language), you can alter the procedure options. If you change the host language code, you need to prepare the code again (compile, link-edit, bind, …).
  • For an external SQL procedure, you can alter only the options.
    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.

Procedure

To alter an existing stored procedure:

  1. Follow the process for the type of change that you want to make:
    • To alter the host language code for an external stored procedure, modify the source and prepare the code again. (Precompile, compile, and link-edit the application, and then bind the DBRM into a package.)
    • Start of changeFL 507 To alter the body of a native SQL procedure, issue the ALTER PROCEDURE statement with the REPLACE clause or the CREATE PROCEDURE statement with the OR REPLACE clause.End of change
    • To alter the procedure options of any type of stored procedure, issue the ALTER PROCEDURE statement with the options that you want. Start of changeFL 507Or, for a native SQL procedure, or an external procedure, you can issue the CREATE PROCEDURE statement with the OR REPLACE clause.End of change
  2. Refresh the WLM environment if either of the following situations applies:
    • For external SQL procedures or external procedures, you changed the stored procedure logic or parameters.
    • You changed the startup JCL for the stored procedures address space.
      Restriction: In some cases, refreshing the WLM environment might not be enough. For example, if the change to the JCL is to the NUMTCB value, refreshing the WLM environment is not enough. The refresh fails because it cannot start a new WLM address space that has a different NUMTCB from the existing one. In this case, you need to do a WLM quiesce, followed by a WLM resume.
    Tip: To refresh the WLM environment, use the Db2-supplied WLM_REFRESH stored procedure rather than the REFRESH command. (The REFRESH command starts a new WLM address space and stops the existing one.)
  3. If you disabled automatic rebinds, rebind any plans or packages that refer to the stored procedure that you altered.

Examples

Begin general-use programming interface information.

Example 1: changing the WLM environment
The following example changes the external stored procedure SYSPROC.MYPROC to run in the WLM environment PARTSEC: 
ALTER PROCEDURE SYSPROC.MYPROC
   WLM ENVIRONMENT PARTSEC;
Example 2: changing the stored procedure to use another authorization ID

Assume that you defined the stored procedure SYSPROC.MYPROC with the SECURITY DEFINER option. When you specify the SECURITY DEFINER option, the external security environment for the stored procedure uses the authorization ID of the owner of the stored procedure to control access to non-SQL resources. The following example changes the stored procedure SYSPROC.MYPROC so that it uses the authorization ID of the person who is running the stored procedure to control access to non-SQL resources:

ALTER PROCEDURE SYSPROC.MYPROC
   SECURITY USER;

End general-use programming interface information.