The ALTER PROCEDURE (External) statement alters a procedure at the current
server.
This statement can be embedded in an application program or issued interactively.
It is an executable statement that can be dynamically prepared.
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
For information on 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.
>>-ALTER-------------------------------------------------------->
>--+-PROCEDURE--procedure-name--+--------------------------+-+-->
| '-(--+----------------+--)-' |
| '-parameter-type-' |
'-SPECIFIC PROCEDURE--specific-name-----------------------'
.-ALTER-.
>--+-------+--option-list--------------------------------------><
parameter-type:
|----data-type--+------------+----------------------------------|
'-AS LOCATOR-'
data-type:
|--+-built-in-type------+---------------------------------------|
'-distinct-type-name-'
option-list:
|--+-----------------------+------------------------------------>
'-LANGUAGE--+-C-------+-'
+-C++-----+
+-CL------+
+-COBOL---+
+-COBOLLE-+
+-FORTRAN-+
+-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-.
>--+----------------------+--+------------------------------+--->
'-DYNAMIC RESULT SETS--integer-'
>--+-----------+--+---------------------+--+------------+------->
+-NO DBINFO-+ +-ALLOW DEBUG MODE----+ +-FENCED-----+
'-DBINFO----' +-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--+-----------+--)-' |
| '-, integer-' |
| .-(--53--)------. |
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
| | '-(--integer--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(--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--)-------. |
+-+-+-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-----------------------------------------------------------------------------------'
ccsid-clause:
.-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
'-NORMALIZED-----'
- PROCEDURE or SPECIFIC PROCEDURE
- Identifies the procedure to alter. procedure-name must identify a 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 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 a 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.
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 name is specified, the database manager searches the SQL path to resolve
the schema name for the distinct 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 an option is not specified, the value from the existing procedure
definition is used.
- LANGUAGE
- Specifies the language that the external program or service program
is written in. The language clause is required if the external program is
a REXX procedure.
- C
- The external program is written in C.
- C++
- The external program is written in C++.
- CL
- The external program is written in CL.
- COBOL
- The external program is written in COBOL.
- COBOLLE
- The external program is written in ILE COBOL.
- FORTRAN
- The external program is written in FORTRAN.
- JAVA
- The external program is written in JAVA.
- PLI
- The external program is written in PL/I.
- REXX
- The external program is a REXX procedure.
- RPG
- The external program is written in RPG.
- RPGLE
- The external program is written in ILE RPG.
- PARAMETER STYLE
- Specifies the conventions used for passing parameters to and returning
the values from procedures:
- SQL
- Specifies that in addition to the parameters on the CALL statement,
several additional parameters are passed to the procedure. The parameters
are defined to be in the following order:
For more information about the parameters passed, see the include sqludf in the appropriate source file in library QSYSINC.
For example, for C, sqludf can be found in QSYSINC/H.
PARAMETER STYLE SQL
cannot be used with LANGUAGE JAVA.
- DB2GENERAL
- Specifies that the procedure will use a parameter passing convention
that is defined for use with Java™ methods.
PARAMETER STYLE DB2GENERAL
can only be specified with LANGUAGE JAVA. For details on passing parameters
in JAVA, see the IBM® Developer Kit for Java.
- DB2SQL
- Specifies that in addition to the parameters on the CALL statement,
several additional parameters are passed to the procedure. DB2SQL is identical
to the SQL parameter style, except that the following additional parameter
may be passed as the last parameter:
- A parameter for the dbinfo structure, if DBINFO was specified on the CREATE
PROCEDURE statement.
For more information about the parameters passed, see the include sqludf in the appropriate source file in library QSYSINC.
For example, for C, sqludf can be found in QSYSINC/H.
PARAMETER STYLE DB2SQL
cannot be used with LANGUAGE JAVA.
- GENERAL
- Specifies that the procedure will use a parameter passing mechanism
where the procedure receives the parameters specified on the CALL. Additional
arguments are not passed for indicator variables.
PARAMETER STYLE GENERAL
cannot be used with LANGUAGE JAVA.
- GENERAL WITH NULLS
- Specifies that in addition to the parameters on the CALL statement as
specified in GENERAL, another argument is passed to the procedure. This additional
argument contains an indicator array with an element for each of the parameters
of the CALL statement. In C, this would be an array of short INTs. For more
information about how the indicators are handled, see the SQL Programming book.
PARAMETER STYLE GENERAL WITH NULLS cannot be used
with LANGUAGE JAVA.
- JAVA
- Specifies that the procedure will use a parameter passing
convention that conforms to the Java language and ISO/IEC FCD 9075-13:2003, Information technology - Database languages - SQL - Part 13: Java Routines and Types (SQL/JRT) specification. INOUT and OUT
parameters will be passed as single entry arrays to facilitate returning values.
PARAMETER STYLE JAVA can only be specified with LANGUAGE JAVA. For increased
portability, you should write Java procedures that use the PARAMETER
STYLE JAVA conventions. For details on passing parameters in JAVA, see the IBM Developer Kit for Java book.
Note that the language of the external procedure determines how
the parameters are passed. For example, in C, any VARCHAR or CHAR parameters
are passed as NUL-terminated strings. For more information, see the SQL Programming book. For Java routines, see the IBM Developer
Kit for Java.
- EXTERNAL NAME external-program-name
- Specifies the program or service program that will be executed when
the procedure is called by the CALL statement. The program name must identify
a program or service program that exists at the application server at the time the
procedure is called. If the naming option is *SYS and the name is not qualified:
- The current path will be used to search for the program or service program
at the time the procedure is called.
- *LIBL will be used to search for the program or service program at the
time grants or revokes are performed on the procedure.
The validity of the name is checked at the application server. If the format
of the name is not correct, an error is returned.
The external program
or service program need not exist at the time the procedure is altered, but
it must exist at the time the procedure is called.
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.
- NOT DETERMINISTIC or DETERMINISTIC
- Specifies whether the procedure returns the same results each time the
procedure is called with the same IN and INOUT arguments.
- NOT DETERMINISTIC
- The procedure may not return the same result each time the procedure
is called with the same IN and INOUT arguments, even when the referenced data
in the database has not changed.
- DETERMINISTIC
- The procedure always returns the same results each time the procedure
is called with the same IN and INOUT arguments, provided the referenced data
in the database has not changed.
- MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, or NO SQL
- Specifies the classification of SQL statements that this procedure,
or any routine called by this procedure, can execute. The database manager
verifies that the SQL statements issued by the procedure and all routines
called by the procedure are consistent with this specification. For the classification
of each statement, see SQL statement data access indication in routines.
- MODIFIES SQL DATA
- Specifies that the procedure can execute any SQL statement except statements
that are not supported in procedures.
- READS SQL DATA
- Specifies that the procedure can execute statements with a data access
classification of READS SQL DATA or CONTAINS SQL.
- CONTAINS SQL
- Specifies that the procedure can only execute statements with a data
access classification of CONTAINS SQL.
- NO SQL
- The function does not execute SQL statements.
- CALLED ON NULL INPUT
- Specifies that the procedure will be called if any, or all, parameter
values are null.
- DYNAMIC RESULT SETS integer
- Specifies the maximum number of result sets that can be returned
from the procedure. integer must be greater than or equal to zero
and less than 32768. If zero is specified, no result sets are returned. If
the SET RESULT SETS statement is issued, the number of result sets returned
is the minimum of the number of results sets specified on this keyword and
the SET RESULT SETS statement. If the SET RESULT SETS statement specifies
a number larger than the maximum number of result sets, a warning is returned.
Note that any result sets from cursors that have a RETURN TO CLIENT attribute
are included in the number of result sets of the outermost procedure.
The result sets are scrollable if a cursor is used to return a result set
and the cursor is scrollable. If a cursor is used to return a result set,
the result set starts with the current position. Thus, if 5 FETCH NEXT operations
have been performed prior to returning from the procedure, the result set
will start with the 6th row of the result set.
Result sets are only
returned if both the following are true:
- the procedure is directly called or if the procedure is a RETURN
TO CLIENT procedure and is indirectly called from ODBC, JDBC, OLE DB, .NET,
the SQL Call Level Interface, or the iSeries Access Family Optimized SQL API, and
- the external program does not have an attribute of ACTGRP(*NEW).
For more information about result sets see SET RESULT SETS.
- DBINFO
- Specifies whether or not the procedure requires the database information
be passed.
- DBINFO
- Specifies that the database manager should pass a structure containing
status information to the procedure. Table 45 contains a description
of the DBINFO structure. Detailed information about the DBINFO structure can
be found in include sqludf in the appropriate source
file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.
DBINFO is only allowed with PARAMETER STYLE DB2SQL.
Table 45. DBINFO fields
| Field |
Data Type |
Description |
| Relational database |
VARCHAR(128) |
The name of the current server. |
| Authorization ID |
VARCHAR(128) |
The run-time authorization ID. |
| CCSID Information |
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
CHAR(8)
|
The CCSID information of the job. Three sets
of three CCSIDs are returned. The following information identifies the three
CCSIDs in each set:
- SBCS CCSID
- DBCS CCSID
- Mixed CCSID
Following the three sets of CCSIDs is an integer that indicates which
set of three sets of CCSIDs is applicable and eight bytes of reserved space.
If a CCSID is not explicitly specified for a parameter on the CREATE PROCEDURE
statement, the input string is assumed to be encoded in the CCSID of the job
at the time the procedure is executed. If the CCSID of the input string is
not the same as the CCSID of the parameter, the input string passed to the
external procedure will be converted before calling the external program. |
| Target Column |
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
|
Not applicable for a call to a procedure. |
| Version and release |
CHAR(8) |
The version, release, and modification level
of the database manager. |
| Platform |
INTEGER |
The server's platform type. |
- NO DBINFO
- Specifies that the procedure does not require the database information
to be passed.
- FENCED or NOT FENCED
- This parameter is allowed for compatibility with other products and
is not used by DB2 UDB for iSeries.
- DISALLOW DEBUG MODE, ALLOW DEBUG MODE, or DISABLE
DEBUG MODE
- Indicates whether the procedure can be debugged by the Unified Debugger. If DEBUG MODE is not specified, the procedure will be created with
the debug mode specified by the CURRENT DEBUG MODE special register.
DEBUG
MODE can only be specified with LANGUAGE JAVA procedures.
- DISALLOW DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISALLOW, the procedure can be subsequently
altered to change the debug mode attribute.
- ALLOW DEBUG MODE
- The procedure can be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is ALLOW, the procedure can be subsequently altered
to change the debug mode attribute.
- DISABLE DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISABLE, the procedure cannot be subsequently
altered to change the debug mode attribute.
- OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL
- Specifies whether a new savepoint level is to be created on entry to
the procedure.
- OLD SAVEPOINT LEVEL
- A new savepoint level is not created. Any SAVEPOINT statements issued
within the procedure with OLD SAVEPOINT LEVEL implicitly or explicitly specified
on the SAVEPOINT statement are created at the same savepoint level as the
caller of the procedure.
- NEW SAVEPOINT LEVEL
- A new savepoint level is created on entry to the procedure. Any savepoints
set within the procedure are created at a savepoint level that is nested deeper
than the level at which this procedure was invoked. Therefore, the name of
any new savepoint within the procedure will not conflict with any existing
savepoint levels (such as the savepoint level of the calling program) with
the same name.
- COMMIT ON RETURN
- Specifies whether the database manager commits the transaction immediately
on return from the procedure.
- NO
- The database manager does not issue a commit when the procedure returns.
- YES
- The database manager issues a commit when the procedure returns successfully.
If the procedure returns with an error, a commit is not issued.
The commit
operation includes the work that is performed by the calling application process
and the procedure.
If the procedure returns result sets, the cursors
that are associated with must have been defined as WITH HOLD to be usable
after the commit.
General considerations for defining or replacing procedures: See CREATE PROCEDURE for general information on 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.
Modify the definition for an external procedure so that SQL changes are
committed on return from the procedure.
ALTER PROCEDURE UPDATE_SALARY_1
ALTER COMMIT ON RETURN YES
[ Top of Page | Previous Page | Next Page | Contents |
Index ]