Regenerating an existing version of a native SQL procedure

When you apply Db2 maintenance that changes how native SQL procedures are generated, you need to regenerate any affected procedures. When you regenerate a version of a native SQL procedure, Db2 rebinds the associated package for that version of the procedure.

About this task

ALTER PROCEDURE REGENERATE is different than the REBIND PACKAGE command. When you specify REBIND PACKAGE, Db2 rebinds only the non-control SQL statements. Use this command when you think rebinding will improve the access path. When you specify ALTER PROCEDURE REGENERATE, Db2 rebinds the SQL control statements as well as the non-control statements.

Procedure

To regenerate an existing version of a native SQL procedure:

  1. Issue the ALTER PROCEDURE statement with the REGENERATE clause and specify the version to be regenerated.
  2. If copies of the package for the specified version of the procedure exist at remote sites, replace those packages. Issue the BIND PACKAGE command with the COPY option and appropriate location for each remote package.
  3. If copies of the package for the specified version of the procedure exist locally with different schema names, replace those packages. Issue the BIND PACKAGE command with the COPY option and appropriate schema for each local package.

Example

The following ALTER PROCEDURE statement regenerates the active version of the UPDATE_SALARY_1 procedure. 
ALTER PROCEDURE UPDATE_SALARY_1
REGENERATE ACTIVE VERSION