To execute the ALTER FUNCTION command, users must possess either overall SYSADM or SYSCTRL authority for the location, or be the owner of the function affected.
Bold text indicates clauses or options that are supported only syntactically.
ALTER FUNCTION function-name [(parameter-type[,...])] |
SPECIFIC FUNCTION specific-name
[EXTERNAL NAME {'string' | identifier}]
[LANGUAGE {ASSEMBLE | C | COBOL | PLI}]
[[NOT] DETERMINISTIC]
[{RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT}]
[{CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL}]
[[NO] EXTERNAL ACTION]
[{NO SCRATCHPAD | SCRATCHPAD length}]
[[NO] FINAL CALL]
[{ALLOW PARALLEL | DISALLOW PARALLEL}]
[[NO] DBINFO]
[CARDINALITY integer]
[{NO COLLID | COLLID collection-id}]
[WLM ENVIRONMENT {name | (name,*)}]
[ASUTIME {NO LIMIT | LIMIT integer}]
[STAY RESIDENT {NO | YES}]
[PROGRAM TYPE {SUB | MAIN}]
[SECURITY {DB2 | USER | DEFINER}]
[STOP AFTER SYSTEM DEFAULT FAILURES |
STOP AFTER integer FAILURES |
CONTINUE AFTER FAILURE]
[RUN OPTIONS run-time-options]
parameter-type:
{data-type [AS LOCATOR] | TABLE LIKE table-name as LOCATOR}
data-type:
{built-in-data-type | distinct-type-name}
built-in-data-type:
[SMALLINT]
[INTEGER | INT]
[{DECIMAL | DEC | NUMERIC} [(integer[,...])]
[{FLOAT [(integer)] | REAL [PRECISION] | DOUBLE}]
[{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) } [FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | I} |[{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] }
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] }]
[{BINARY LARGE OBJECT | BLOB} [(integer [K|M|G])]]
[{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB [(integer [K|M|G])] } [CCSID {EBCDIC | ASCII}]]
[DATE | TIME | TIMESTAMP]
[ROWID]
Identifies the external function by its function name. The name is implicitly or explicitly qualified with a schema name. If the name is not explicitly qualified, it is implicitly qualified with a schema name according to the following rules:
The identified function must be an external function. There must be exactly one function with function-name in the schema. The function can have any number of input parameters. If the schema does not contain a function with function-name or contains more than one function with this name, an error occurs.
Identifies the external function by its function signature, which uniquely identifies the function.
| function-name | gives the function name of the external function. If the function name is not qualified, it is implicitly qualified with a schema name as described in the preceding description for FUNCTION function-name. |
| (parameter-type,...) | identifies the number of input parameters of the function and their data types. |
The data type of each parameter must match the data type that was specified in the CREATE FUNCTION statement for the parameter in the corresponding position. The number of data types and the logical concatenation of the data types are used to uniquely identify the function. Therefore, you cannot change the number of parameters or the data types of the parameters.
For data types that have a length, precision, or scale attribute, you can specify a value or use a set of empty parentheses:
The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement
For data types with a subtype or encoding scheme attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that XDB ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.
A function with the function signature must exist in the explicitly or implicitly specified schema; otherwise, an error occurs.
Identifies the external function by its specific name. The name is implicitly or explicitly qualified with a schema name. A function with the specific name must exist in the schema; otherwise, an error occurs.
If the specific name is not qualified, it is implicitly qualified with a schema name as described in the preceding description for FUNCTION function-name.
The following clauses change the description of the function that has been identified to be changed.
Identifies the name of the module that contains the user-written code that implements the logic of the function. The external program name can be a string constant that is no longer than 8 characters or a short identifier.
The module is created when the program that contains the function body is compile . The module does not need to exist when the ALTER FUNCTION statement is executed. However, it must exist and be accessible by the current server when the function is invoked.
Specifies the application programming language in which the function is written. All programs must be designed to run in IBM's Language Environment environment .
| ASSEMBLE | The function is written in Assembler. (Not currently available with XDB Server) |
| C | The function is written in C or C++. (Not currently available with XDB Server) |
| COBOL | The function is written in COBOL, including the object-oriented language extensions. |
| PL/I | The function is written in PL/I. (Not currently available with XDB Server) |
Specifies whether the function returns the same results for identical input arguments.
| NOT DETERMINISTIC | The function might not return the same result for identical input arguments. The function depends on some state values that affect the results. XDB uses this information when processing a SELECT, UPDATE, DELETE, or INSERT statement to disable merging of views that refer to the function. An example of a function that is not deterministic is one that generates random numbers, or any function that contains SQL statements.
Some functions that are not deterministic can receive incorrect results if the function is executed by parallel tasks. Specify the DISALLOW PARALLEL clause for these functions. If a view or a materialized query table definition refers to the function, the function cannot be changed to NOT DETERMINISTIC. To change the function, drop any views or materialized query tables that refer to the function first. |
| DETERMINISTIC | The function always returns the same result for identical input arguments. XDB can use this information to optimize view processing for SELECT, UPDATE, DELETE, or INSERT statements. An example of a deterministic function is a function that calculates the square root of the input.
XDB does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC. |
Specifies whether the function is called if any of the input arguments is null at execution time.
| RETURNS NULL ON NULL INPUT | The function is not called if any of the input arguments is null. For an external scalar function, the result is the null value. For an external table function, the result is an empty table, which is a table with no rows. |
| CALLED ON NULL INPUT | The function is called regardless of whether any of the input arguments is null, making the function responsible for testing for null argument values. For an external scalar function, the function can return a null or nonnull value. For an external table function, the function can return an empty table, depending on its logic. |
Indicates whether the function issues any SQL statements and, if so, what type.
| NO SQL | The function cannot execute any SQL statements. |
| MODIFIES SQL DATA | The function can execute any SQL statement except those statements that are not supported in any function. Do not specify MODIFIES SQL DATA for external table functions or with ALLOW PARALLEL. |
| READS SQL DATA | The function cannot execute SQL statements that modify data. SQL statements that are not supported in any function return a different error. |
| CONTAINS SQL | The function cannot execute any SQL statements that read or modify data. SQL statements that are not supported in any function return a different error. |
This clause has no functional equivalent in the XDB Server operating environment.
Specifies whether XDB provides a scratchpad for the function. It is strongly recommended that external functions be reentrant, and a scratchpad provides an area for the function to save information from one invocation to the next.
Each time that the function is invoked, XDB passes an additional argument to the function that contains the address of the scratchpad.
Specifies whether a final call is made to the function. A final call enables the function to free any system resources that it has acquired. A final call is useful when the function has been defined with the SCRATCHPAD keyword and the function acquires system resource and anchors them in the scratchpad.
The effect of NO FINAL CALL or FINAL call depends on whether the external function is a scalar function or a table function.
For an external scalar function:
NO FINAL CALL
A final call is not made to the external scalar function. The function does not receive an additional argument that specifies the type of call.
FINAL CALL
A final call is made to the external scalar function. See the following description of call types for the characteristics of a final call. When FINAL CALL is specified, the function receives an additional argument that specifies the type of call to enable the function to differentiate between a final call and another type of call.
For an external table function:
NO FINAL CALL
A first and final call are not made to the external table function.
FINAL CALL
A first call and final call are made to the external table function in addition to one or more other types of calls.
This clause has no functional equivalent in the XDB Server operating environment.
Specifies whether specific information that XDB knows is passed to the function when it is invoked.
| NO DBINFO | Additional information is not passed. |
| DBINFO | An additional argument is passed when the function is invoked. The argument is a structure that contains information such as the application run-time authorization ID, the schema name, the name of a table or column that the function might be inserting into or updating, and identification of the database server that invoked the function. For details about the argument and its structure, see your DB2 Application Programming and SQL Guide. |
Specifies an estimate of the expected number of rows that the function returns. The number is used for optimization purposes. The value of integer must range from 0 to 2147483647.
If a function has an infinite cardinality--the function never returns the "end-of-table" condition and always returns a row, then a query that requires the "end-of-table" to work correctly, will need to be interrupted. Thus, avoid using such functions in queries that involve GROUP BY and ORDER BY.
Do not specify CARDINALITY for external scalar functions.
Identifies the package collection that is used when the function is executed.
| NO COLLID | The package collection for the function is the same as the package collection of the program that invokes the function. If a trigger invokes the function, the collection of the trigger package is used. If the invoking program does not use a package, XDB resolves the package by using the CURRENT PACKAGE PATH special register or the CURRENT PACKAGESET special register. |
| COLLID collection-id | The name of the package collection that is used when the function is executed. |
Specifies the total amount of processor time, in CPU service units, that a single invocation of the function can run. The value is unrelated to the ASUTIME column of the resource limit specification table.
When you are debugging a function, setting a limit can be helpful if the function gets caught in a loop.
The XDB Server operating environment only provides NO LIMIT at this time.
Specifies whether the load module for the function remains resident in memory when the function ends.
The XDB Server provides an operating environment where the function does not remain resident.
Specifies whether the routine is to be put in a stopped state after some number of failures. The following options must not be specified for SQL functions or sourced functions.
This clause is only implemented syntactically in the XDB Server operating environment.
This clause has no functional equivalent in the XDB Server operating environment.
Assume that there are two functions CENTER in the PELLOW schema. The first function has two input parameters with INTEGER and FLOAT data types, respectively. The specific name for the first function is FOCUS1. The second function has three parameters with CHAR(25), DEC(5,2), and INTEGER data types.
Using the specific name to identify the function, change the WLM environment in which the first function runs from WLMENVNAME1 to WLMENVNAME2.
ALTER SPECIFIC FUNCTION PELLOW.FOCUS1 WLM ENVIRONMENT WLMENVNAME2;
Change the second function that is described above so that it is not invoked when any of the arguments are null. Use the function signature to identify the function,
ALTER FUNCTION PELLOW.CENTER (CHAR(25), DEC(5,2), INTEGER)
RETURNS NULL ON NULL INPUT;
You can also code the ALTER FUNCTION statement without the exact values for the CHAR and DEC data types:
ALTER FUNCTION PELLOW.CENTER (CHAR(), DEC(), INTEGER)
RETURNS NULL ON NULL INPUT;
If you use empty parentheses, XDB ignores the length, precision, and scale attributes when looking for matching data types to find the function.