ALTER FUNCTION (External Table)
The ALTER FUNCTION (External Table) statement alters an external table function 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 function identified in the statement:
- The ALTER privilege for the function, and
- The system authority *EXECUTE on the library containing the function.
- 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 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.
Syntax
>>-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-'
Notes:
- The clauses in the option-list can be specified in any order.
Description
- FUNCTION or SPECIFIC FUNCTION
- Identifies
the function to alter. function-name must identify an external
table function that exists at the current server. It cannot identify
a built-in function, a sourced function, or an SQL function. An external
scalar function cannot be altered to be an external table function.
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.
- FUNCTION function-name
- Identifies the function by its name. The function-name must identify exactly one function. The function may have any number of parameters defined for it. If there is more than one function of the specified name in the specified or implicit schema, an error is returned.
- FUNCTION function-name (parameter-type,...)
- Identifies the function by its function signature, which uniquely
identifies the function. The function-name (parameter-type,...) must
identify a function with the specified function signature. The specified
parameters must match the data types in the corresponding position
that were specified when the function was created. The number of data
types, and the logical concatenation of the data types is used to
identify the specific function instance which is being altered. Synonyms
for data types are considered a match.
If function-name() is specified, the function identified must have zero parameters.
- function-name
- Identifies the name of the function.
- (parameter-type,...)
- Identifies the parameters of the function.
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 function 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 FUNCTION 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 FUNCTION 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 FUNCTION statement.
- AS LOCATOR
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.
- SPECIFIC FUNCTION specific-name
- Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.
- ALTER option-list
- Indicates that one or more of the options of the function are to be altered. If an option is not specified, the value from the existing function definition is used. See CREATE FUNCTION (External Table) for a description of each option.
- RESTRICT
- Indicates that the function will not be altered if it is referenced by any view, function, procedure, or materialized query table.
Notes
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:
- The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
- The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED ON NULL INPUT and RETURNS NULL ON NULL INPUT.
- The keyword DB2GENRL may be used as a synonym for DB2GENERAL.
- The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.
- The keywords IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.
Example
Modify the definition for an external table function to set the estimated cardinality to 10,000.
ALTER FUNCTION GET_TABLE
ALTER CARDINALITY 10000