The ALTER FUNCTION (SQL Scalar) statement alters an SQL scalar function 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:
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 scalar function. For more information, see CREATE FUNCTION (SQL Scalar).
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.
>>-ALTER--------------------------------------------------------> >--+-FUNCTION--function-name--+------------------------------+-+--> | '-(--+--------------------+--)-' | | | .-,--------------. | | | | V | | | | '---parameter-type-+-' | '-SPECIFIC FUNCTION--specific-name--------------------------' .-ALTER-. >--+-+-------+--+----------+--option-list---------+------------>< | '-RESTRICT-' | '-REPLACE--+----------+--routine-specification-' '-RESTRICT-' parameter-type |----data-type1--+------------+---------------------------------| '-AS LOCATOR-' data-type1, data-type2,data-type3 |--+-built-in-type------+---------------------------------------| '-distinct-type-name-' routine-specification |----(--+---------------------------+--)------------------------> | .-,---------------------. | | V | | '---parameter-declaration-+-' >----RETURNS--data-type2----+-------------+---------------------> '-option-list-' >--+----------------------+--SQL-routine-body-------------------| '-SET OPTION-statement-' parameter-declaration |--parameter-name--data-type3-----------------------------------|
option-list .-LANGUAGE SQL-. (1) .-NOT DETERMINISTIC-. |--+--------------+------+-------------------+------------------> '-DETERMINISTIC-----' .-READS SQL DATA----. .-CALLED ON NULL INPUT-------. >--+-------------------+--+----------------------------+--------> +-MODIFIES SQL DATA-+ '-RETURNS NULL ON NULL INPUT-' '-CONTAINS SQL------' .-INHERIT SPECIAL REGISTERS-. .-STATIC DISPATCH-. >--+---------------------------+--+-----------------+-----------> .-EXTERNAL ACTION----. .-FENCED-----. >--+--------------------+--+------------+-----------------------> '-NO EXTERNAL ACTION-' '-NOT FENCED-' >--+-------------------+----------------------------------------> +-ALLOW PARALLEL----+ '-DISALLOW PARALLEL-' >--+---------------------------------------------------------------+--| | .-DEFAULT---------------------. | '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-' | '-U-----------------------' | '-+-WAIT FOR OUTCOME-+--------' '-W----------------'
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-----------------------------------------------|
The specified function is altered. The owner of the function and all privileges on the function are preserved.
If function-name() is specified, the function identified must have zero parameters.
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:
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 FUNCTION statement.
Specifies that the function 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.
If the routine has a comment or label, they are removed
from the routine definition.
The maximum number of parameters allowed in an SQL function is 90.
You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC) or a distinct type.
If a CCSID is specified and the CCSID of the return data is encoded in a different CCSID, the data is converted to the specified CCSID.
If a CCSID is not specified the return data is converted to the CCSID of the job (or associated graphic CCSID of the job for graphic string return values), if the CCSID of the return data is encoded in a different CCSID. To avoid any potential loss of characters during the conversion, consider explicitly specifying a CCSID that can represent any characters that will be returned from the function. This is especially important if the data type is graphic string data. In this case, consider using CCSID 1200 or 13488 (Unicode graphic string data).
SET OPTION DBGVIEW = *SOURCE
For
more information, see SET OPTION. The options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the ALTER FUNCTION statement.
A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK, and SET TRANSACTION statement is not allowed in a function.
The SQL-routine-body must contain at least one RETURN statement and a RETURN statement must be executed when the function is called.
ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
General considerations for defining or replacing functions: See CREATE FUNCTION (SQL Scalar) for general information about defining a function. ALTER FUNCTION (SQL Scalar) allows individual attributes to be altered while preserving the privileges on the function.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Modify the definition for an SQL scalar function to indicate that the function is deterministic.
ALTER FUNCTION MY_UDF1 DETERMINISTIC