ALTER FUNCTION

The ALTER FUNCTION statement changes the description of an external scalar or external table function at the current server.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

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.

Syntax

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]

Description

One of the following three clauses identifies the function to be changed.

FUNCTION function-name

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:

  • If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
  • If the statement is prepared dynamically, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

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.

FUNCTION function-name (parameter-type,...)

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:

  • Empty parentheses indicate that XDB ignores the attribute when determining whether the data types match.FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).
  • If you use a specific value for a length, precision, or scale attribute, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.
  • The specific value for FLOAT(n) does not have exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default length of the data type is implied. For example:
    CHAR CHAR(1)
    GRAPHIC GRAPHIC(1)
    DECIMAL DECIMAL(5,0)
    FLOAT DOUBLE (length of 8)

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.

SPECIFIC FUNCTION specific-name

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.

EXTERNAL NAME 'string' or identifier

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.

LANGUAGE

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)

NOT DETERMINISTIC or DETERMINISTIC

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.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

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.

NO SQL, MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL

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.

NO EXTERNAL ACTION or EXTERNAL ACTION

This clause has no functional equivalent in the XDB Server operating environment.

NO SCRATCHPAD or SCRATCHPAD

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.

NO SCRATCHPAD A scratchpad is not allocated and passed to the function.
SCRATCHPAD When the function is invoked for the first time, XDB allocates memory for a scratchpad. A scratchpad has the following characteristics:
  • Length must be between 1 and 32767. The default value is 100 bytes.
  • XDB initializes the scratchpad to all binary zeros (X'00's).
  • The scope of a scratchpad is the SQL statement. For each reference to the function in an SQL statement, there is one scratchpad. For example, assuming that function UDFX was defined with the SCRATCHPAD keyword, three scratchpads are allocated for the three references to UDFX in the following SQL statement:
    SELECT A, UDFX(A) FROM TABLEB WHERE UDFX(A) > 103 OR UDFX(A) < 19;

    If the function is run under parallel tasks, one scratchpad is allocated for each parallel task of each reference to the function in the SQL statement. This can lead to unpredictable results. For example, if a function uses the scratchpad to count the number of times that it is invoked, the count reflects the number of invocations done by the parallel task and not the SQL statement. Specify the DISALLOW PARALLEL clause for functions that do not work correctly with parallelism.

  • The scratchpad is persistent. XDB preserves its content from one invocation of the function to the next. Any changes that the function makes to the scratchpad on one call are still there on the next call. XDB initializes the scratchpads when it begins to execute an SQL statement. XDB does not reset scratchpads when a correlated subquery begins to execute.
  • The scratchpad can be a central point for the system resources that the function acquires. If the function acquires system resources, specify FINAL CALL to ensure that XDB calls the function one more time so that the function can free those system resources.

Each time that the function is invoked, XDB passes an additional argument to the function that contains the address of the scratchpad.

NO FINAL CALL or FINAL CALL

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.

ALLOW or DISALLOW PARALLEL

This clause has no functional equivalent in the XDB Server operating environment.

NO DBINFO or DBINFO

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.

CARDINALITY integer

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.

NO COLLID or COLLID collection-id

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.

WLM ENVIRONMENT

This clause has no functional equivalent in the XDB Server operating environment

ASUTIME

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.

NO LIMIT There is no limit on the service units.
LIMIT integer The limit on the service units is a positive integer in the range of 1 to 2GB. If the function uses more service units than the specified value, the function is canceled.

The XDB Server operating environment only provides NO LIMIT at this time.

STAY RESIDENT

Specifies whether the load module for the function remains resident in memory when the function ends.

NO The load module is deleted from memory after the function ends. Use NO for non-reentrant functions.
YES The load module remains resident in memory after the function ends. Use YES for reentrant functions.

The XDB Server provides an operating environment where the function does not remain resident.

PROGRAM TYPE

Specifies whether the function program runs as a main routine or a subroutine.

SUB The function runs as a subroutine.
MAIN The function runs as a main routine.

SECURITY

This clause has no functional equivalent in the XDB Server operating environment.

STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER integer FAILURES, or CONTINUE AFTER FAILURE

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.

  • STOP AFTER SYSTEM DEFAULT FAILURES.
  • STOP AFTER integer FAILURES.
  • CONTINUE AFTER FAILURE.

This clause is only implemented syntactically in the XDB Server operating environment.

RUN OPTIONS run-time-options

This clause has no functional equivalent in the XDB Server operating environment.

Note:

Changes are immediate: Any changes that the ALTER FUNCTION statement causes to the definition of an external function take effect immediately. The changed definition is used the next time that the function is invoked.

Examples:

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.