Modifying an external stored procedure definition

You can modify the definition of an external stored procedure or the stored procedure source code. In either case, you need to prepare the stored procedure again.

Procedure

To modify an external stored procedure definition:

  1. Issue one of the following:
    • The CREATE PROCEDURE statement with the OR REPLACE clause and the SPECIFIC clause in the following cases:
      • When the parameter list of the existing procedure includes a table parameter.
      • When the CREATE statement specifies changes to the parameter list other than parameter names.
    • The ALTER PROCEDURE statement with the appropriate options.
    This new definition replaces the existing definition.
  2. Prepare the external stored procedure again, as you did when you originally created the external stored procedure.

Example

Suppose that an existing C stored procedure was defined with the following statement:

CREATE PROCEDURE B(IN V1 INTEGER, OUT V2 CHAR(9))
  LANGUAGE C
  DETERMINISTIC
  NO SQL
  EXTERNAL NAME SUMMOD
  COLLID SUMCOLL
  ASUTIME LIMIT 900
  PARAMETER STYLE GENERAL WITH NULLS
  STAY RESIDENT NO
  RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
  WLM ENVIRONMENT PAYROLL
  PROGRAM TYPE MAIN
  SECURITY DB2
  DYNAMIC RESULT SETS 10
  COMMIT ON RETURN NO;

Assume that you need to make the following changes to the stored procedure definition:

  • The stored procedure selects data from Db2 tables but does not modify Db2 data.
  • The parameters can have null values, and the stored procedure can return a diagnostic string.
  • The length of time that the stored procedure runs is unlimited.
  • If the stored procedure is called by another stored procedure or a user-defined function, the stored procedure uses the WLM environment of the caller.
Either of the following statements can make these changes:
CREATE OR REPLACE PROCEDURE B(IN V1 INTEGER, OUT V2 CHAR(9))
  LANGUAGE C
  DETERMINISTIC
  READS SQL DATA
  EXTERNAL NAME SUMMOD
  COLLID SUMCOLL
  ASUTIME NO LIMIT
  PARAMETER STYLE SQL
  STAY RESIDENT NO
  RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
  WLM ENVIRONMENT (PAYROLL,*)
  PROGRAM TYPE MAIN
  SECURITY DB2
  DYNAMIC RESULT SETS 10
  COMMIT ON RETURN NO;
ALTER PROCEDURE B
  READS SQL DATA
  ASUTIME NO LIMIT
  PARAMETER STYLE SQL
  WLM ENVIRONMENT (PAYROLL,*);