ALTER PROCEDURE (SQL)

The XDB Server operating environment only provides syntax support for external Procedures (SQL) at this time.
Restriction: This topic applies to Windows environments only.

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' | identifier}] 
    [{DETERMINISTIC | NOT DETERMINISTIC}] 
    [{CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA}] 
    [{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}] 
    [INHERIT SPECIAL REGISTERS | DEFAULT SPECIAL REGISTERS]
    [{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

Specifies the name of the MVS load module for the program that runs when the procedure name is specified in an SQL CALL statement. The value must conform to the naming conventions for MVS load modules: the value must be less than or equal to 8 bytes, and it must conform to the rules for an ordinary identifier with the exception that it must not contain an underscore.

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.

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

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

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 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 stored procedure is executed.

WLM ENVIRONMENT

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

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 of 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 2 GB. 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.
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 as WITH HOLD to be usable after the commit.

Note:

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.

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:

Modify the definition for an SQL procedure so that SQL changes are committed on return from the SQL procedure and the SQL procedure runs in the WLM environment named WLMSQLP. 

 ALTER PROCEDURE UPDATE_SALARY_1 
    COMMIT ON RETURN YES 
    WLM ENVIRONMENT WLMSQLP;
Parent topic: SQL Statements