ALTER PROCEDURE (external)

The ALTER PROCEDURE statement changes the description of a stored procedure at the current server.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

To execute the ALTER PROCEDURE command, users must possess either overall SYSADM or SYSCTRL authority for the location, or be the owner of the stored procedure affected.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

ALTER PROCEDURE procedure-name 
    [DYNAMIC RESULT {SET | SETS} integer] 
    [EXTERNAL NAME {'string' | indentifier}] 
    [LANGUAGE {ASSEMBLE | C | COBOL | COMPJAVA | PLI | REXX}] 
    [PARAMETER STYLE {DB2SQL | GENERAL | GENERAL WITH NULLS | JAVA}] 
    [{DETERMINISTIC | NOT DETERMINISTIC}] 
    [{CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL}] 
    [{DBINFO | NODBINFO}] 
    [{NO COLLID | COLLID collection-id}] 
    [{WLM ENVIRONMENT {name | (name,*)}} | NO WLM ENVIRONMENT}] 
    [ASUTIME {NO LIMIT | LIMIT integer}] 
    [STAY RESIDENT {NO | YES}] 
    [PROGRAM TYPE {SUB | MAIN}] 
    [SECURITY {DB2 | USER | DEFINER}] 
    [RUN OPTIONS run-time-options] 
    [COMMIT ON RETURN {NO | YES}] 
    [CALLED ON NULL INPUT]
    [{STOP AFTER SYSTEM DEFAULT FAILURES | 
     STOP AFTER integer FAILURES | 
     CONTINUE AFTER FAILURE}]

Description

procedure-name

Identifies the stored procedure to be altered. The name is implicitly or explicitly qualified by a schema name. If the name is not explicitly qualified, it is implicitly qualified with a schema name according to the following rules:

  • If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
  • If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

DYNAMIC RESULT SET integer or DYNAMIC RESULT SETS integer

Specifies the maximum number of query result sets that the stored procedure can return. The value must be between 0 and 32767.

EXTERNAL NAME 'string' or identifier

Identifies the program that runs when the procedure name is specified in a CALL statement.

The name can be a string constant that is no longer than 8 characters or a short identifier.

The program does not need to exist when the ALTER PROCEDURE statement is executed. However, it must exist and be accessible by the current server when a CALL statement for the stored procedure is issued.

LANGUAGE

Specifies the application programming language in which the stored procedure is written. Assembler, C, COBOL, and PL/I programs must be designed to run in IBM's Language Environment.

ASSEMBLE The stored procedure is written in Assembler. (Not currently available with XDB Server.)
C The stored procedure is written in C or C++. (Not currently available with XDB Server.)
COBOL The stored procedure is written in COBOL, including the OO-COBOL language extensions.
COMPJAVA The stored procedure is written in Java and is a compiled program. (Not currently available with XDB Server.)
PL/I The stored procedure is written in PL/I. (Not currently available with XDB Server.)
REXX The stored procedure is written in REXX. Do not specify LANGUAGE REXX when PARAMETER STYLE DB2SQL or NO WLM ENVIRONMENT is in effect. (Not currently available with XDB Server.)

PARAMETER STYLE

Identifies the linkage convention used to pass parameters to the stored procedure. All of the linkage conventions provide arguments to the stored procedure that contain the parameters specified on the CALL statement. Some of the linkage conventions pass additional arguments to the stored procedure that provide more information to the stored procedure. For more information on linkage conventions, refer to appropriate DB2 documentation for a full explanation.

DB2SQL In addition to the parameters on the CALL statement, the following arguments are also passed to the stored procedure:
  • A null indicator for each parameter on the CALL statement
  • The SQLSTATE to be returned to DB2
  • The qualified name of the stored procedure
  • The specific name of the stored procedure
  • The SQL diagnostic string to be returned to DB2

If DBINFO is specified, an additional parameter, the DB2INFO structure, might also be passed. Do not specify DB2 SQL when LANGUAGE REXX is in effect.

GENERAL Only the parameters on the CALL statement are passed to the stored procedure. The parameters cannot be null.
GENERAL WITH NULLS In addition to the parameters on the CALL statement, another argument is also passed to the stored procedure. The additional argument contains a vector of null indicators for each of the parameters on the CALL statement that enables the stored procedure to accept or return null parameter values.
JAVA The stored procedure uses a convention for passing parameters that conforms to the Java and SQLJ specifications. INOUT and OUT parameters are passed as single-entry arrays. The DBINFO structure is not passed.

JAVA can be specified only if LANGUAGE is COMPJAVA.

For REXX stored procedures (LANGUAGE REXX), GENERAL and GENERAL WITH NULLS are the only valid values for PARAMETER STYLE. For LANGUAGE COMPJAVA, JAVA is the only valid value.

NOT DETERMINISTIC or DETERMINISTIC

Specifies whether the stored procedure returns the same result from successive calls with identical input arguments.

NOT DETERMINISTIC The stored procedure might not return the same result from successive calls with identical input arguments.
DETERMINISTIC The stored procedure returns the same result from successive calls with identical input arguments. XDB does not verify that the stored procedure code is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.

NO SQL, MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL DATA

Indicates whether the stored procedure issues any SQL statements and, if so, what type.

NO SQL The stored procedure cannot execute any SQL statements.
MODIFIES SQL DATA The stored procedure can execute any SQL statement except those statements that are not supported in any stored procedure.
READS SQL DATA The stored procedure cannot execute SQL statements that modify data. SQL statements that are not supported in any stored procedure return a different error.
CONTAINS SQL The stored procedure cannot execute any SQL statements that read or modify data. SQL statements that are not supported in any stored procedure return a different error.

NO DBINFO or DBINFO

Specifies whether specific information known by XDB is passed to the stored procedure when it is invoked.

NO DBINFO Additional information is not passed.
DBINFO An additional argument is passed when the stored procedure is invoked. The argument is a structure that contains information such as the application run-time authorization ID, the schema name, the name of a table or column that the procedure might be inserting into or updating, and identification of the database server that invoked the procedure.

NO COLLID or COLLID collection-id

Identifies the package collection that is used when the stored procedure is executed.

NO COLLID The package collection for the stored procedure is the same as the package collection of the program that invokes the stored procedure. If a trigger invokes the stored procedure, the collection of the trigger package is used. If the invoking program does not use a package, XDB resolves the package by using the CURRENT PACKAGE PATH special register or the CURRENT PACKAGESET special register.
COLLID collection-id The name of the package collection that is used when the function is executed.

WLM ENVIRONMENT

This clause has no functional equivalent in the XDB Server operating environment.

NO WLM ENVIRONMENT

Indicates that the stored procedure is to run in the XDB-established stored procedure address space.

Do not specify NO WLM ENVIRONMENT if the definition of the stored procedure implicitly or explicitly includes the following clauses or parameters:

  • The PROGRAM TYPE SUB clause
  • The SECURITY USER or SECURITY DEFINER clause
  • The LANGUAGE REXX clause
  • Parameters with a LOB data type or a distinct type based on a LOB data type

To change the procedure to run in the XDB-established stored procedure address space, you must have appropriate authority for the XDB-established stored procedure address space.

ASUTIME

Specifies the total amount of processor time, in CPU service units, that a single invocation of a stored procedure can run. The value is unrelated to the ASUTIME column in the resource limit specification table.

When you are debugging a stored procedure, setting a limit can be helpful in case the stored procedure gets caught in a loop.

NO LIMIT There is no limit on the service units.
LIMIT integer The limit on the service units is a positive integer in the range of 1 to 2GB. If the stored procedure uses more service units than the specified value, the stored procedure is canceled.

The XDB Server operating environment only provides NO LIMIT at this time.

STAY RESIDENT

Specifies whether the stored procedure load module remains resident in memory when the stored procedure ends.

NO The load module is deleted from memory after the stored procedure ends. Use NO for non-reentrant stored procedures.
YES The load module remains resident in memory after the stored procedure ends.

The XDB Server provides an operating environment where the function does not remain resident.

PROGRAM TYPE

Specifies whether the stored procedure runs as a main routine or a subroutine.

SUB The stored procedure runs as a subroutine.
MAIN The stored procedure runs as a main routine.

SECURITY

This clause has no functional equivalent in the XDB Server operating environment.

RUN OPTIONS run-time-options

This clause has no functional equivalent in the XDB Server operating environment.

COMMIT ON RETURN

Indicates whether XDB commits the transaction immediately on return from the stored procedure.

NO XDB does not issue a commit when the stored procedure returns.
YES XDB issues a commit when the stored procedure returns if the following statements are true:
  • The SQLCODE that is returned by the CALL statement is not negative.
  • The stored procedure is not in a must abort state.

The commit operation includes the work that is performed by the calling application process and the stored procedure.

If the stored procedure returns result sets, the cursors that are associated with the result sets must have been defined WITH HOLD to be usable after the commit.

CALLED ON NULL INPUT

Specifices that the stored procedure will be called even if any of the input arguments is null, making the procedure responsible for testing for null argument values. The result is the null value.

Note:

Changes are immediate: Any changes that the ALTER PROCEDURE statement cause to the definition of a procedure take effect immediately.

The changed definition is used the next time that the procedure is called.

Restrictions for nested stored procedures: A stored procedure, user-defined function, or trigger cannot call a stored procedure that is defined with the COMMIT ON RETURN clause.

STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER integer FAILURES, or CONTINUE AFTER FAILURE

Specifies whether the routine is to be put in a stopped state after some number of failures.

  • STOP AFTER SYSTEM DEFAULT FAILURES.
  • STOP AFTER integer FAILURES.
  • CONTINUE AFTER FAILURE.

This clause is only implemented syntactically in the XDB Server operating environment.

Example:

Assume that stored procedure SYSPROC.MYPROC is currently defined to run in WLM environment PARTSA and that you have appropriate authority on that WLM environment and WLM environment PARTSEC. Change the definition of the stored procedure so that it runs in PARTSEC.

ALTER PROCEDURE SYSPROC.MYPROC WLM ENVIRONMENT PARTSEC;