Designating the active version of a native SQL procedure

When a native SQL procedure is called, Db2 uses the version that is designated as the active version.

About this task

When you create a native SQL procedure, that first version is by default the active version. If you create additional versions of a stored procedure, you can designate another version to be the active version.

Exception: If an existing active version is still being used by a process, the new active version is not used until the next call to that procedure.

Procedure

To designate the active version of a native SQL procedure, issue an ALTER PROCEDURE statement with the following items:
  • The name of the native SQL procedure for which you want to change the active version.
  • The ACTIVATE VERSION clause with the name of the version that you want to be active.

When the ALTER statement is committed, the new version of the procedure becomes the active version and is used by the next call for that procedure.

Example

The following ALTER PROCEDURE statement makes version V2 of the UPDATE_BALANCE procedure the active version. 

ALTER PROCEDURE UPDATE_BALANCE
ACTIVATE VERSION V2;