ALTER PROCEDURE (SQL)
The ALTER PROCEDURE (SQL) statement alters a procedure at the current server.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
- For the procedure identified in the statement:
- The ALTER privilege for the procedure, and
- The system authority *EXECUTE on the library containing the procedure.
- Administrative authority
If a distinct type is referenced in a parameter-declaration, the privileges held by the authorization ID of the statement must include at least one of the following:
- For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
- Administrative authority
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure and Corresponding System Authorities When Checking Privileges to a Distinct Type.
Syntax
>>-ALTER--------------------------------------------------------> >--+-PROCEDURE--procedure-name--+------------------------------+-+--> | '-(--+--------------------+--)-' | | | .-,--------------. | | | | V | | | | '---parameter-type-+-' | '-SPECIFIC PROCEDURE--specific-name---------------------------' .-ALTER-. >--+-+-------+--option-list---------+-------------------------->< '-REPLACE--routine-specification-' routine-specification |--+-------------------------------------+--+-------------+-----> '-(--+---------------------------+--)-' '-option-list-' | .-,---------------------. | | V | | '---parameter-declaration-+-' >--+----------------------+--SQL-routine-body-------------------| '-SET OPTION-statement-' parameter-declaration .-IN----. |--+-------+--parameter-name--data-type--+----------------+-----| +-OUT---+ '-default-clause-' '-INOUT-' parameter-type |----data-type--------------------------------------------------| data-type |--+-built-in-type------+---------------------------------------| +-distinct-type-name-+ '-array-type-name----' default-clause |--DEFAULT--+-NULL-------------+--------------------------------| +-constant---------+ +-special-register-+ +-global-variable--+ '-(--expression--)-' option-list .-LANGUAGE SQL-. .-NOT DETERMINISTIC-. (1) |--+--------------+--+-------------------+----------------------> '-DETERMINISTIC-----' .-MODIFIES SQL DATA-. .-CALLED ON NULL INPUT-. >--+-------------------+--+----------------------+--------------> +-READS SQL DATA----+ '-CONTAINS SQL------' .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--------------------------------> .-DYNAMIC RESULT SETS 0--------. >--+------------------------------+--+---------------------+----> '-DYNAMIC RESULT SETS--integer-' +-ALLOW DEBUG MODE----+ +-DISABLE DEBUG MODE--+ '-DISALLOW DEBUG MODE-' .-FENCED-----. .-OLD SAVEPOINT LEVEL . >--+------------+--+---------------------+----------------------> '-NOT FENCED-' '-NEW SAVEPOINT LEVEL ' .-COMMIT ON RETURN NO--. >--+----------------------+-------------------------------------> '-COMMIT ON RETURN YES-' >--+---------------------------------------------------------------+--| | .-DEFAULT---------------------. | '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-' | '-U-----------------------' | '-+-WAIT FOR OUTCOME-+--------' '-W----------------'
- The clauses in the option-list can be specified in any order.
Description
- PROCEDURE or SPECIFIC PROCEDURE
- Identifies
the procedure to alter. procedure-name must identify an
SQL procedure that exists at the current server.
The specified procedure is altered. The owner of the procedure and all privileges on the procedure are preserved.
- PROCEDURE procedure-name
- Identifies the procedure by its name. The procedure-name must identify exactly one SQL procedure. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit schema, an error is returned.
- PROCEDURE procedure-name (parameter-type,...)
- Identifies the procedure by its procedure signature, which uniquely
identifies the procedure. The procedure-name (parameter-type,...) must
identify an SQL procedure with the specified procedure signature.
The specified parameters must match the data types in the corresponding
position that were specified when the procedure was created. The number
of data types and the logical concatenation of the data types is used
to identify the specific procedure instance which is being altered.
Synonyms for data types are considered a match.
Parameters
that have defaults must be included in this signature.
If procedure-name() is specified, the procedure identified must have zero parameters.
- procedure-name
- Identifies the name of the procedure.
- (parameter-type,...)
- Identifies the parameters of the procedure. If
an unqualified distinct type or array type name is specified, the
database manager searches the SQL path to resolve the schema name
for the distinct type or array type.
For data types that have a length, precision, or scale attribute, use one of the following:
- Empty parenthesis indicates that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its precision value indicates a specific data type (REAL or DOUBLE).
- If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
- If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
- AS LOCATOR
- Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or a distinct type based on a LOB.
- SPECIFIC PROCEDURE specific-name
- Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
- ALTER option-list
- Indicates that one or more of the options of the procedure are to be altered. If ALTER PROCEDURE ALTER option-list is specified and an option is not specified, the value from the existing procedure definition is used. See CREATE PROCEDURE (SQL) for a description of each option.
- REPLACE routine-specification
- Indicates
that the existing procedure definition, including options and parameters,
is to be replaced by those specified in this statement. The values
of all options are replaced when a procedure is replaced. If an option
is not specified, the same default is used as when a new SQL procedure
is created, for more information see CREATE PROCEDURE (SQL).If the routine has a comment or label, they are removed
from the routine definition.
- (parameter-declaration,…)
- Specifies the number of parameters of the procedure, the data
type of each parameter, and the name of each parameter. A parameter
for a procedure can be used for input only, for output only, or for
both input and output.
The maximum number of parameters allowed in an SQL procedure is 1024.
- IN
- Identifies the parameter as an input parameter to the procedure.
- OUT
- Identifies the parameter as an output parameter that is returned by the procedure. If the parameter is not set within the procedure, the null value is returned.
- INOUT
- Identifies
the parameter as both an input and output parameter for the procedure.
If the parameter is not set within the procedure, its input value
is returned. If an INOUT parameter is defined with
a default and the default is used when calling the procedure, no value
for the parameter is returned.
- parameter-name
- Names the parameter for use as an SQL variable. The name cannot be the same as any other parameter-name for the procedure.
- data-type
- Specifies the data type of the parameter. If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the procedure. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the procedure is called.
- default-clause
- Specifies a default value for the parameter. The default can be
a constant, a special register, a global variable, an expression,
or the keyword NULL. The expression is any expression defined in Expressions, that does not include an aggregate
function or column name. If a default value is not specified, the
parameter has no default and cannot be omitted on invocation. The
maximum length of the expression string is 64K.
The default expression must not modify SQL data. The expression must be assignment compatible to the parameter data type. All objects referenced in a default expression must exist when the procedure is created.
Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.
A default cannot be specified:- for an OUT parameter.
- for a parameter of type array.
- option-list
- List of options for the procedure being altered. These options are the same ones that are listed above under ALTER option-list. If a specific option is not specified, the same default that is used when a new procedure is created is used. For more information see CREATE PROCEDURE (SQL).
- SET OPTION-statement
- Specifies the options that will be used to create the procedure.
For example, to create a debuggable procedure, the following statement
could be included:
For more information, see SET OPTION.SET OPTION DBGVIEW = *SOURCEThe options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the ALTER PROCEDURE statement.
The following options
are used when processing default value expressions: ALWCPYDTA, CONACC,
DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE,
SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP. - SQL-routine-body
- Specifies a single SQL statement, including a compound statement.
See SQL control statements for more information
about defining SQL procedures.
CONNECT, SET CONNECTION, RELEASE, DISCONNECT, and SET TRANSACTION statements are not allowed in a procedure that is running on a remote application server. COMMIT and ROLLBACK statements are not allowed in an ATOMIC SQL procedure or in a procedure that is running on a connection to a remote application server.
ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
Notes
General considerations for defining or replacing procedures: See CREATE PROCEDURE for general information about defining a procedure. ALTER PROCEDURE (SQL) allows individual attributes or the routine specification to be altered while preserving the privileges on the procedure.
Alter Procedure Replace considerations: When an SQL procedure definition is replaced, SQL creates a temporary source file that will contain C source code with embedded SQL statements. A program object is then created using the CRTPGM command. The SQL options used to create the program are the options that are in effect at the time the ALTER PROCEDURE (SQL) statement is executed. The program is created with ACTGRP(*CALLER).
When an SQL procedure is altered, a new *PGM
or *SRVPGM object is created and the procedure's attributes are stored
in the created program object. If the *PGM or *SRVPGM object is saved
and then restored to this or another system, the catalogs are automatically
updated with those attributes.
The specific name is used as the name of the member in the source file and the name of the program object, if it is a valid system name. If the procedure name is not a valid system name, a unique name is generated. If a source file member with the same name already exists, the member is overlaid. If a module or a program with the same name already exists, the objects are not overlaid, and a unique name is generated. The unique names are generated according to the rules for generating system table names.
Target release considerations: When an SQL procedure definition is replaced, the target release will be the current release in which the ALTER statement is executed unless the user explicitly specifies a different target release. The target release can be explicitly specified using the TGTRLS keyword in the SET OPTION statement. If the ALTER is specified in the source for a RUNSQLSTM or CRTSQLxxx command, the TGTRLS keyword can also be specified on the command.
If the procedure definition is not replaced, the target release of the existing procedure will be preserved unless the target release level of the procedure is earlier than the earliest supported release level. In this case, the target release will be changed to the earliest supported release level.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
- The keywords NULL CALL can be used as synonyms for CALLED ON NULL INPUT.
- DYNAMIC RESULT SET, RESULT SETS, and RESULT SET may be used as synonyms for DYNAMIC RESULT SETS.
Examples
Modify the definition for an SQL procedure so that SQL changes are committed on return from the SQL procedure.
ALTER PROCEDURE UPDATE_SALARY_2
ALTER COMMIT ON RETURN YES