ALTER FUNCTION (SQL scalar)
The ALTER FUNCTION (SQL scalar) statement alters an SQL scalar 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.
- Database administrator 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 scalar function. For more information, see CREATE FUNCTION (SQL scalar).
- The authorization ID of the statement must have security administrator authority. See Administrative authority.
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
Description
- FUNCTION or SPECIFIC FUNCTION
- Identifies
the function to alter. function-name must identify an SQL scalar
function that exists at the current server.
The specified function is altered. The owner of the function and 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. Parameters
that have defaults must be included in this signature.
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 ALTER FUNCTION ALTER option-list is specified and an option is not specified, the value from the existing function definition is used. See CREATE FUNCTION (SQL scalar) for a description of each option.
- REPLACE routine-specification
- Indicates
that the existing function definition, including options and parameters,
is to be replaced by those specified in this statement. The values
of all options are replaced when a function is replaced. If an option
is not specified, the same default is used as when a new SQL scalar
function is created, for more information see CREATE FUNCTION (SQL scalar).
If the routine has a comment or label, they are removed from the routine definition.
- RESTRICT
- Indicates that the function will not be altered or replaced if it is referenced by any function, materialized query table, procedure, trigger, or view.
- (parameter-declaration,…)
- Specifies the number of parameters of the function, the data type
of each parameter, and the name of each parameter.
The maximum number of parameters allowed in an SQL function is 1024.
- parameter-name
- Names the parameter. The name is used to refer to the parameter within the body of the function. The name cannot be the same as any other parameter-name in the parameter list.
- data-type3
- Specifies the data type of the input parameter. If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the function. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the function is invoked.
- default-clause
- Specifies a default value for the parameter. The default can be
a constant, a special register, a global variable, an expression,
or the keyword NULL. The expression is any expression defined in Expressions, that does not include an aggregate
function or column name. If a default value is not specified, the
parameter has no default and cannot be omitted on invocation. The
maximum length of the expression string is 64K.
The default expression must be assignment compatible to the parameter data type.
Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.
A default cannot be specified for a parameter of type array.
- RETURNS
- Specifies
the output of the function.
- data-type2
- Specifies the data
type and attributes of the output.
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).
- option-list
- List of options for the function being altered. These options are the same ones that are listed above under ALTER option-list. If a specific option is not specified, the same default that is used when a new function is created is used. For more information see CREATE FUNCTION (SQL scalar).
- SET OPTION-statement
- Specifies the options that will be used to create the function.
For example, to create a debuggable function, the following statement
could be included:
For more information, see SET OPTION.SET OPTION DBGVIEW = *SOURCE
The options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the ALTER FUNCTION statement. The following options are used when processing default value expressions: ALWCPYDTA, CONACC, DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE, SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP.
- SQL-routine-body
- Specifies a single SQL statement, including a compound statement.
See SQL control statements for more information
about defining SQL functions.
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.
Notes
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.
Cascaded effects: If REPLACE is specified without RESTRICT and the function signature or result data type is altered, the results from any function, materialized query table, procedure, trigger, or view that references the function may be unpredictable. Any referenced objects should be recreated.
Obfuscated statements: A function that was created using an obfuscated statement can be altered. When the statement is altered the encoded version of the statement that is saved in the catalog is modified and could exceed the maximum length for an SQL statement. If this happens an error will be issued and the alter will fail.
Altering a function from NOT SECURED to SECURED: The function is considered secure after the ALTER FUNCTION statement is executed. DB2® treats the SECURED attribute as an assertion that declares that the user has established an audit procedure for all changes to the user-defined function. DB2 assumes that all subsequent ALTER FUNCTION statements are being reviewed through this audit process.
Invoking other user-defined functions in a secure function: When a secure user-defined function is referenced in an SQL data change statement that references a table that is using row access control or column access control, and if the secure user-defined function invokes other user-defined functions, the nested user-defined functions are not validated as secure. If those nested functions can access sensitive data, a user authorized to the Database Security Administrator function of IBM® i needs to ensure that those functions are allowed to access that data and should ensure that a change control audit procedure has been established for all changes to those functions.
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 keywords IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.
Example
Modify the definition for an SQL scalar function to indicate that the function is deterministic.
ALTER FUNCTION MY_UDF1
DETERMINISTIC