The ALTER FUNCTION (External Table) statement alters an external table 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 table function. For more information, see CREATE FUNCTION (External Table).
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-' parameter-type |----data-type--+------------+----------------------------------| '-AS LOCATOR-' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
option-list |--+-----------------------+------------------------------------> '-LANGUAGE--+-C-------+-' +-C++-----+ +-CL------+ +-COBOL---+ +-COBOLLE-+ +-JAVA----+ +-PLI-----+ +-REXX----+ +-RPG-----+ '-RPGLE---' >--+--------------------------------+---------------------------> '-+-PARAMETER STYLE DB2SQL-----+-' '-PARAMETER STYLE DB2GENERAL-' (1) >--+-------------------+------+-------------------+-------------> +-NOT DETERMINISTIC-+ +-MODIFIES SQL DATA-+ '-DETERMINISTIC-----' +-READS SQL DATA----+ +-CONTAINS SQL------+ '-NO SQL------------' >--+----------------------------+-------------------------------> +-CALLED ON NULL INPUT-------+ '-RETURNS NULL ON NULL INPUT-' >--+---------------------------+--+-----------------+-----------> '-INHERIT SPECIAL REGISTERS-' '-STATIC DISPATCH-' >--+-----------+--+--------------------+--+------------+--------> +-NO DBINFO-+ +-EXTERNAL ACTION----+ +-FENCED-----+ '-DBINFO----' '-NO EXTERNAL ACTION-' '-NOT FENCED-' >--+--------------------------------------+---------------------> '-EXTERNAL NAME--external-program-name-' >--+---------------+--+-------------------+---------------------> +-NO FINAL CALL-+ +-ALLOW PARALLEL----+ '-FINAL CALL----' '-DISALLOW PARALLEL-' >--+-------------------------+--+---------------------+---------| +-NO SCRATCHPAD-----------+ '-CARDINALITY--bigint-' | .-100-----. | '-SCRATCHPAD--+---------+-' '-integer-'
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 is preserved. If the external program or service program exists at the time the function is altered, 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.
General considerations for defining or replacing functions: See CREATE FUNCTION (External Table) for general information about defining a function. ALTER FUNCTION (External Table) 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 external table function to set the estimated cardinality to 10,000.
ALTER FUNCTION GET_TABLE ALTER CARDINALITY 10000