Multiple versions of native SQL procedures

You can define multiple versions of a native SQL procedure. Db2 maintains this version information for you.

One or more versions of a procedure can exist at any point in time at the current server, but only one version of a procedure is considered the active version. When you first create a procedure, that initial version is considered the active version of the procedure.

Using multiple versions of a native SQL procedure has the following advantages:
  • You can keep the existing version of a procedure active while you create another version. When the other version is ready, you can make it the active one.
  • When you make another version of a procedure active, you do not need to change any existing calls to that procedure.
  • You can easily switch back to a previous version of a procedure if the version that you switched to does not work as planned.
  • You can drop an unneeded version of a procedure.
A new version of a native SQL procedure can have different values for the following items:
  • Parameter names
  • Procedure options (except for the AUTONOMOUS option, which must be specified for all versions or none)
  • Procedure body
Restrictions:
  • A new version of a native SQL procedure cannot have different values for the following items:
    • Number of parameters
    • Parameter data types
    • Parameter attributes for character data
    • Parameter CCSIDs
    • Whether a parameter is an input or output parameter, as defined by the IN, OUT, and INOUT options
    If you need to specify different values for any of the preceding items, create a new native SQL procedure, instead of a new version.
  • When the AUTONOMOUS option is specified for one version of a procedure, it must be specified for every version of that procedure.