Creating new versions of native SQL procedures

A new version of a native SQL procedure can have different parameter names, procedure options, or procedure body.

About this task

All versions of a procedure must have the same procedure signature. Therefore, each version of the procedure must have the same of the following items:

  • Schema name
  • Procedure name
  • Number of parameters
  • Data types for corresponding parameters

When any single version of a procedure is defined as autonomous, all versions must be defined as autonomous.

Important: Do not create additional versions of procedures that are supplied with Db2 by specifying the VERSION keyword. Only versions that are supplied with Db2 are supported. Additional versions of such routines cause the installation and configuration of the supplied routines to fail.

Procedure

To create a new version of a procedure, issue one of the following:
  • Start of changeFL 507 The CREATE PROCEDURE statement with the following items:
    • The OR REPLACE clause.
    • The VERSION clause with a new version identifier.
    End of change
  • The ALTER PROCEDURE statement with the following items:
    • The ADD VERSION clause with a name for the new version.
Start of changeFor either statement, you must include the following:
  • The name of the native SQL procedure for which you want to create a new version.
  • The parameter list of the procedure that you want to change. For ALTER PROCEUDRE ADD VERSION, this parameter list must be the same as the original procedure.
  • Any procedure options. These options can be different than the options for other versions of this procedure. If you do not specify a value for a particular option, the default value is used, regardless of the value that is used by the current active version of this procedure.
  • A procedure body. This body can be different than the procedure body for other versions of this procedure.
End of change

Examples

Example 1

For example, the following CREATE PROCEDURE statement defines a new native SQL procedure called UPDATE_BALANCE. The version of the procedure is V1, and it is the active version.

CREATE PROCEDURE
UPDATE_BALANCE
(IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL(9,2))
VERSION V1
LANGUAGE SQL
READS SQL DATA
BEGIN
DECLARE CUSTOMER_NAME CHAR(20);
SELECT CUSTNAME
INTO CUSTOMER_NAME
FROM ACCOUNTS
WHERE CUSTNO = CUSTOMER_NO;
END
Example 2

The following ALTER PROCEDURE statement creates a new version of the UPDATE_BALANCE procedure. The version name of the new version is V2. This new version has a different procedure body. 

ALTER PROCEDURE
UPDATE_BALANCE
ADD VERSION V2
(IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL (9,2) )
MODIFIES SQL DATA
BEGIN
UPDATE ACCOUNTS
SET BAL = BAL + AMOUNT
WHERE CUSTNO = CUSTOMER_NO;
END
Example 3:
FL 507

The following CREATE PROCEDURE statement with the OR REPLACE clause creates a new version of the UPDATE_BALANCE procedure, assuming that version V3 does not already exist (if V3 already exists, this statement would replace the existing definition). This version changes the procedure body in the same way as in Example 2:

CREATE OR REPLACE PROCEDURE
UPDATE_BALANCE
(IN CUSTOMER_NO INTEGER,
IN AMOUNT DECIMAL(9,2))
VERSION V3
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
UPDATE ACCOUNTS
SET BAL = BAL + AMOUNT
WHERE CUSTNO = CUSTOMER_NO;
END

What to do next

After you create a new version, if you want that version to be invoked by all subsequent calls to this procedure, you need to make that version the active version. Start of changeYou can use the ACTIVATE VERSION clause on either an ALTER PROCEDURE statement or a CREATE PROCEDURE statement with the OR REPLACE clause. End of change