Skip to content

CREATE versus ALTER

There are several ways to change the Db2 catalog definition of a stored procedure, user defined function, or trigger.

  1. DROP the Db2 object and execute a CREATE with the new definition.

  2. Execute an ALTER that includes only the parameters you want to modify in the definition.

  3. For Native SQL SPs you can use ALTER to ADD a new VERSION.

    For all but Native SQL SPs, we recommend that you always stage a CREATE member to change a definition. The Db2 Option issues a DROP automatically before it executes a CREATE definition.

    If you use CREATE, then the latest version of the complete object definition is available in baseline for checkout. If you use ALTER, the next time you check out the definition, you can only see the parameters in the definition that you changed in the last ALTER. You can look in prior versions kept by ChangeMan ZMF for the complete definition, but you will have these difficulties.

  4. To come up with a complete, current definition, you have to apply the changes in each ALTER to the last CREATE definition.

  5. If you install more ALTERs for an object than the number of prior versions of the component type kept by ChangeMan ZMF, the CREATE will be lost.

    There are cases where you cannot use CREATE. When a Db2 object has dependencies, it can be difficult to DROP the object.

    When dependencies exist, you may have no alternative than to use ALTER to change the definition. If you must use ALTER, we recommend that you include the entire CREATE definition as comments inside the ALTER SQL member.

    For Native SQL SPs the same considerations apply but, in addition, you may wish to use ALTER ADD VERSION

    To add a new version of an SP to the target Db2 catalog. The ZMF Db2 option supplies mechanisms to aid with the automation of activating new and dropping old versions of native SQL SPs.