ALTER PROCEDURE (External)

The ALTER PROCEDURE (External) statement alters an external 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 different external program is specified, the privileges held by the authorization ID of the statement must also include the same privileges required to create a new external procedure. For more information, see CREATE PROCEDURE (External).

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure, Corresponding System Authorities When Checking Privileges to a Table or View, 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--------------------------------------><

parameter-type

|----data-type--+------------+----------------------------------|
                '-AS LOCATOR-'     

data-type

|--+-built-in-type------+---------------------------------------|
   +-distinct-type-name-+   
   '-array-type-name----'


option-list

|--+-----------------------+------------------------------------>
   '-LANGUAGE--+-C-------+-'   
               +-C++-----+     
               +-CL------+     
               +-COBOL---+     
               +-COBOLLE-+     
               +-JAVA----+     
               +-PLI-----+     
               +-REXX----+     
               +-RPG-----+     
               '-RPGLE---'     

>--+----------------------------------------+------------------->
   '-+-PARAMETER STYLE SQL----------------+-'   
     +-PARAMETER STYLE DB2SQL-------------+     
     +-PARAMETER STYLE GENERAL------------+     
     +-PARAMETER STYLE GENERAL WITH NULLS-+     
     +-PARAMETER STYLE JAVA---------------+     
     '-PARAMETER STYLE DB2GENERAL---------'     

                         (1)                          
>--+-------------------+------+-------------------+------------->
   +-NOT DETERMINISTIC-+      +-MODIFIES SQL DATA-+   
   '-DETERMINISTIC-----'      +-READS SQL DATA----+   
                              +-CONTAINS SQL------+   
                              '-NO SQL------------'   

   .-CALLED ON NULL INPUT-.  .-INHERIT SPECIAL REGISTERS-.   
>--+----------------------+--+---------------------------+------>

>--+------------------------------+--+-----------+-------------->
   '-DYNAMIC RESULT SETS--integer-'  +-NO DBINFO-+   
                                     '-DBINFO----'   

>--+---------------------+--+------------+---------------------->
   +-ALLOW DEBUG MODE----+  +-FENCED-----+   
   +-DISABLE DEBUG MODE--+  '-NOT FENCED-'   
   '-DISALLOW DEBUG MODE-'                   

>--+--------------------------------------+--------------------->
   '-EXTERNAL NAME--external-program-name-'   

>--+---------------------+--+----------------------+------------|
   +-OLD SAVEPOINT LEVEL +  +-COMMIT ON RETURN NO--+   
   '-NEW SAVEPOINT LEVEL '  '-COMMIT ON RETURN YES-'

Notes:

The clauses in the option-list can be specified in any order.


built-in-type

|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                          |   
   | | '-INT-----'  |                                                                          |   
   | '---BIGINT-----'                                                                          |   
   |                  .-(5,0)------------------------.                                         |   
   +-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+   
   | | '-DEC-----' |  |             .-,0--------.    |                                         |   
   | '-+-NUMERIC-+-'  '-(--integer--+-----------+--)-'                                         |   
   |   '-NUM-----'                  '-, integer-'                                              |   
   |          .-(--53--)------.                                                                |   
   +-+-FLOAT--+---------------+-+--------------------------------------------------------------+   
   | |        '-(--integer--)-' |                                                              |   
   | +-REAL---------------------+                                                              |   
   | |         .-PRECISION-.    |                                                              |   
   | '-DOUBLE--+-----------+----'                                                              |   
   |             .-(--34--)-.                                                                  |   
   +---DECFLOAT--+----------+------------------------------------------------------------------+   
   |             '-(--16--)-'                                                                  |   
   |                    .-(--1--)-------.                                                      |   
   +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+   
   | | | '-CHAR------'  '-(--integer--)-'          |  +-FOR BIT DATA---+                     | |   
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-'  +-FOR SBCS DATA--+                     | |   
   | |   | '-CHAR------'          |                   +-FOR MIXED DATA-+                     | |   
   | |   '-VARCHAR----------------'                   '-ccsid-clause---'                     | |   
   | |                                          .-(--1M--)-------------.                     | |   
   | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' |   
   |       | '-CHAR------'               |      '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |   
   |       '-CLOB------------------------'                    +-K-+       +-FOR MIXED DATA-+   |   
   |                                                          +-M-+       '-ccsid-clause---'   |   
   |                                                          '-G-'                            |   
   |                .-(--1--)-------.                                                          |   
   +-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+   
   | |              '-(--integer--)-'       |  '-ccsid-clause-'                                |   
   | +-+-GRAPHIC VARYING-+--(--integer--)---+                                                  |   
   | | '-VARGRAPHIC------'                  |                                                  |   
   | |             .-(--1M--)-------------. |                                                  |   
   | '---DBCLOB----+----------------------+-'                                                  |   
   |               '-(--integer--+---+--)-'                                                    |   
   |                             +-K-+                                                         |   
   |                             +-M-+                                                         |   
   |                             '-G-'                                                         |   
   |                             .-(--1--)-------.                                             |   
   +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+------------+   
   | | | +-NATIONAL CHAR------+  '-(--integer--)-'          |                     |            |   
   | | | '-NCHAR--------------'                             |                     |            |   
   | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-'                     |            |   
   | |   | +-NATIONAL CHAR------+          |                                      |            |   
   | |   | '-NCHAR--------------'          |                                      |            |   
   | |   '-NVARCHAR------------------------'                                      |            |   
   | |                                                   .-(--1M--)-------------. |            |   
   | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-'            |   
   |       | '-NCHAR--------------'               |      '-(--integer--+---+--)-'              |   
   |       '-NCLOB--------------------------------'                    +-K-+                   |   
   |                                                                   +-M-+                   |   
   |                                                                   '-G-'                   |   
   |             .-(--1--)-------.                                                             |   
   +-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+   
   | | |         '-(--integer--)-'         |                 |                                 |   
   | | '-+-BINARY VARYING-+--(--integer--)-'                 |                                 |   
   | |   '-VARBINARY------'                                  |                                 |   
   | |                              .-(--1M--)-------------. |                                 |   
   | '---+-BLOB----------------+----+----------------------+-'                                 |   
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'                                   |   
   |                                              +-K-+                                        |   
   |                                              +-M-+                                        |   
   |                                              '-G-'                                        |   
   +-+-DATE-------------------+----------------------------------------------------------------+   
   | |       .-(--0--)-.      |                                                                |   
   | +-TIME--+---------+------+                                                                |   
   | |            .-(--6--)-. |                                                                |   
   | '-TIMESTAMP--+---------+-'                                                                |   
   |             .-(--200--)-----.                                                             |   
   +---DATALINK--+---------------+--+--------------+-------------------------------------------+   
   |             '-(--integer--)-'  '-ccsid-clause-'                                           |   
   +---ROWID-----------------------------------------------------------------------------------+   
   '---XML-------------------------------------------------------------------------------------'   

ccsid-clause

|--CCSID--integer-----------------------------------------------|

Description

PROCEDURE or SPECIFIC PROCEDURE

Identifies the procedure to alter. procedure-name must identify an external procedure that exists at the current server.

The specified procedure is altered. The owner of the procedure is preserved. If the external program or service program exists at the time the procedure is altered, all privileges on the procedure are preserved.

PROCEDURE procedure-name

Identifies the procedure by its name. The procedure-name must identify exactly one external 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 external 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. Start of change

Parameters that have defaults must be included in this signature. End of change

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.

Start of change 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. End of change

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 XML or a distinct type based on a LOB or XML. End of change

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 an option is not specified, the value from the existing procedure definition is used. See CREATE PROCEDURE (External) for a description of each option.

Notes

General considerations for defining or changing a procedure: See CREATE PROCEDURE for general information about defining a procedure. ALTER PROCEDURE (External) allows individual attributes to be altered while preserving the privileges on the procedure.

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 procedure MYPROC to change the name of the external program that is invoked when the procedure is called. The name of the external program is PROG10A.

  ALTER PROCEDURE MYPROC 
    EXTERNAL NAME PROG10A