CREATE FUNCTION (aggregate interface) statement

The CREATE FUNCTION (aggregate interface) statement is used to register a user-defined aggregate function at the current server.

An aggregate function returns a single value that is the result of an evaluation of a set of like values, such as those in a column within a set of rows.

Invocation

This statement can be embedded in an application program or issued in dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges that are held by the authorization ID of the statement must include at least one of the following authorities:

IMPLICIT_SCHEMA authority on the database, if the schema name of the function does not refer to an existing schema
CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema
DBADM authority

The privileges that are held by the authorization ID of the statement must also include EXECUTE privilege on the following routines, if the authorization ID of the statement does not have DATAACCESS authority:

INITIATE
ACCUMULATE
MERGE
FINALIZE

Group privileges, except for PUBLIC, are not considered on any dependent object specified in the CREATE FUNCTION statement.

To replace an existing function, the authorization ID of the statement must be the owner of the existing function (SQLSTATE 42501).

If the SECURED option is specified, the authorization ID of the statement must include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).

Syntax

parameter-declaration

data-type1, data-type2, data-type3, data-type4, data-type5

built-in-type

default-clause

option-list

state-variable-declaration

procedure-designator

function-designator

Notes:

¹ The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
² DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
³ For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.

Description

OR REPLACE

Specifies to replace the definition for the function if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the function are not affected. This option can be specified only by the owner of the object. This option is ignored if a definition for the function does not exist at the current server. An existing function can be replaced if either of the following conditions apply:

The specific name and function name of the new definition must be the same as the specific name and function name of the old definition
The signature of the new definition must match the signature of the old definition

Otherwise, a new function is created.

If the function is referenced in the definition of a row permission or a column mask, the function cannot be replaced (SQLSTATE 42893).

function-name

Names the function that is being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier. The qualified name must not be the same as the data type of the first parameter, if that first parameter is a structured type.

The function signature must not identify a function or method described in the catalog (SQLSTATE 42723). When the name is being assessed, the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) is considered. The unqualified name, together with the number and data types of the parameters, must be unique within its schema. However, the name does not need to be unique across schemas.

If a two-part name is specified, the schema-name cannot begin with SYS (SQLSTATE 42939).

A number of names that are used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.

In general, the same name can be used for more than one function if the function signatures are different.

Although it is not prohibited, do not give an aggregate interface function the same name as a built-in function, unless it is an intentional override. Examples of such functions are MAX, MIN, and AVG. Creating a user-defined function that has different behavior, yet the same name, and consistent arguments as a built-in scalar or aggregate function, can lead to problems. Examples include:

Problems in dynamic SQL statements
Static SQL applications can fail when they are rebound
Applications might appear to run successfully but provide a different result

(parameter-declaration,...)

Identifies the number of input parameters of the function, and specifies the mode, name, data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the function expects to receive. Up to 90 parameters can be specified (SQLSTATE 54023).

You can register a function that has no parameters; the parentheses must still be coded, with no intervening data types. For example:

   CREATE FUNCTION WOOFER() ...

Two functions with identical names in the same schema cannot have the same type for all corresponding parameters. Lengths, precisions, and scales are not considered in this type comparison. Therefore, CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). A weakly typed distinct type that is specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. Further bundling of types causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature returns an error (SQLSTATE 42723).

IN

Identifies the parameter as an input parameter to the function. Any changes that are made to the parameter within the function are not available to the invoking context when control is returned.

parameter-name

Specifies an optional name for the parameter. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734).

data-type1

Specifies the data type of the parameter. The data type can be a built-in data type. For a complete description of each built-in data type, see CREATE TABLE statement. The data type must not be XML, CLOB, DBCLOB, or BLOB. (SQLSTATE 42815). The data type must not be a distinct type (SQLSTATE 42611).

DEFAULT

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 special registers that can be specified as the default are the same as those special registers that can be specified for a column default (see default-clause in the CREATE TABLE statement). Other special registers can be specified as the default by using an expression.

The expression can be any expression of the type described in Expressions. If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64 KB.

The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).

RETURNS

This mandatory clause identifies the output of the function.

data-type2

Specifies the data type of the output.

In this case, the same considerations apply as described in data-type1 for function parameters.

The data type must not be a distinct type (SQLSTATE 42611).

data-type3 CAST FROM data-type4

Specifies the data type of the output.

This form of the RETURNS clause returns a different data type to the invoking statement than the data type that was returned by the function code of the FINALIZE function. Example:

   CREATE FUNCTION GET_HIRE_DATE(CHAR(6))
     RETURNS DATE CAST FROM CHAR(10)
     ...

In the preceding code, the function code returns a CHAR(10) value to the database manager. The database manager then converts it to a DATE and passes that value to the invoking statement. The data-type4 must be castable to the data-type3 parameter. If it is not castable, an error (SQLSTATE 42880) is returned.

The length, precision, or scale for data-type3 can be inferred from data-type4. Although you can specify the length, precision, or scale for parameterized types for data-type3, it is not necessary. Instead, empty parentheses can be used. For example, VARCHAR() can be used). FLOAT() cannot be used (SQLSTATE 42601) since parameter value indicates different data types (REAL or DOUBLE).

Distinct types are not valid as the type specified in data-type3 or data-type4 (SQLSTATE 42815).

The cast operation is also subject to runtime checks that might result in conversion errors.

built-in-type

See CREATE TABLE statement for the description of built-in data types.

option-list

SPECIFIC specific-name

Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance or method specification that exists at the application server; otherwise, an error (SQLSTATE 42710) is returned.

The specific-name can be the same as an existing function-name.

If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error (SQLSTATE 42882) is returned.

If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.

DETERMINISTIC or NOT DETERMINISTIC

This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same result from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC. An example of a NOT DETERMINISTIC function would be a random-number generator. An example of a DETERMINISTIC function would be a function that determines the square root of the input. If DETERMINISTIC is specified, then the INITIALIZE, ACCUMULATE, MERGE, and FINALIZE routines that are identified must also be DETERMINISTIC (SQLSTATE 428IA).

If the PARAMETER STYLE is HIVE, NOT DETERMINISTIC cannot be specified or implied. DETERMINISTIC must be explicitly specified.

CALLED ON NULL INPUT

CALLED ON NULL INPUT always applies to aggregate interface functions. In other words, the function is called regardless of whether any argument's set of values are all null. The INITIALIZE, ACCUMULATE, and MERGE procedures that are identified are also always CALLED ON NULL INPUT since they are procedures. The FINALIZE function that is identified must also be CALLED ON NULL INPUT (SQLSTATE 428IA). Any parameter of the component routines can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the component routine.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the function has actions that change the state of an object that the database manager does not manage. An example of an external action is sending a message or writing a record to a file. The default is EXTERNAL ACTION.

If the PARAMETER STYLE is HIVE, EXTERNAL ACTION cannot be specified or implied. NO EXTERNAL ACTION must be explicitly specified.

EXTERNAL ACTION: Specifies that the function has actions that change the state of an object that the database manager does not manage.
A function with external actions might return incorrect results if the function is executed by parallel tasks. For example, if the function sends a note for each initial call to it, one note is sent for each parallel task instead of once for the function.
NO EXTERNAL ACTION: Specifies that the function does not have actions that change the state of an object that the database manager does not manage. The database manager uses this information during optimization of SQL statements. If NO EXTERNAL ACTION is specified, then the INITIALIZE, ACCUMULATE, MERGE, and FINALIZE routines that are identified must also be NO EXTERNAL ACTION (SQLSTATE 428IA).

NO SCRATCHPAD

This optional clause can be used to specify whether a scratchpad is to be provided for an external function. NO SCRATCHPAD is allowed for an aggregate interface function. The INITIALIZE, ACCUMULATE, and MERGE procedures identified always have NO SCRATCHPAD since they are procedures. The FINALIZE function that is identified must also be NO SCRATCHPAD (SQLSTATE 428IA).

NO FINAL CALL

This optional clause specifies whether a final call is to be made to an external function. NO FINAL CALL is allowed for an aggregate interface function. The INITIALIZE, ACCUMULATE, and MERGE procedures that are identified always are NO FINAL CALL since they are procedures. The FINALIZE function that is identified must also be NO FINAL CALL (SQLSTATE 428IA).

ALLOW PARALLEL

This optional clause specifies whether, for a single reference to the function, the invocation of the function can be parallelized. For aggregate interface functions, only ALLOW PARALLEL is supported.

NOT SECURED or SECURED

Specifies whether the function is considered secure for row and column access control. The default is NOT SECURED.

NOT SECURED: Indicates that the function is not considered secure. When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled and column level access control is activated for its table (SQLSTATE 428HA). This rule applies to the non-secure user-defined functions that are invoked anywhere in the statement.
SECURED: Indicates that the function is considered secure.

AGGREGATE

This mandatory clause indicates that the CREATE FUNCTION statement is being used to register a user-defined aggregate function.

WITH

This clause is used to specify the state variables that are available between the stages of aggregate function processing. There must be at least one state variable defined.

state-variable-name

Specifies an optional name for the state variable. The name cannot be the same as any other state-variable-name in the list of state variables for this function definition (SQLSTATE 42734).

data-type5

Specifies the data type of a state variable.

The considerations that apply for the parameters of functions (as described in data-type1) also apply for function parameters. However, XML, CLOB, DBCLOB, and BLOB data types are not allowed as state variable data types (SQLSTATE 42611). The data type for data-type5 must not be a distinct type (SQLSTATE 42611).

The total byte count of the state variables cannot be bigger than 32677 using the table row size approach to counting bytes (SQLSTATE 42611).

USING

This mandatory clause specifies how the aggregate function processing is implemented. The USING clause specifies a set of user-defined procedures and a user-defined scalar function that implement the phases of aggregate function processing.

IN MODULE module-name: This optional clause specifies that the three procedures and one function that are specified in the INITIALIZE, ACCUMULATE, MERGE, and FINALIZE clauses are in module module-name. If this clause is specified, the procedure-name, function-name, or specific-name specified in procedure-designator and function-designator must be unqualified names (SQLSTATE 42601).

INITIALIZE procedure-designator

Uniquely identifies a single procedure that implements the initialization phase of the aggregation.

The procedure that is selected must have output parameters only. The number of output parameters must be the same as the number of state variables specified in the AGGREGATE WITH clause (SQLSTATE 428IA). The data type of each output parameter in the procedure that is selected must have the exact same type as the corresponding data type specified in the AGGREGATE WITH clause (SQLSTATE 428IA).

Specify the following combinations of options for the selected procedure (SQLSTATE 428IA):

LANGUAGE C and NO SQL
LANGUAGE JAVA and NO SQL

The procedure must exist when this statement is run, unless the AUTO_REVAL database configuration parameter is set to DEFERRED_FORCE.

ACCUMULATE procedure-designator

Uniquely identifies a single procedure that implements the accumulate phase of the aggregation.

The procedure that is selected must meet the following criteria (SQLSTATE 428IA):

The procedure that is selected must first have the same number of input-only parameters as the number of the parameters specified in the aggregation function.
The procedure that is selected must then have the same number of INOUT parameters as the number of the state variables specified in the AGGREGATE WITH clause.

The data type of each input-only parameter in the procedure that is selected must have the exact same type as the corresponding data type specified in parameter-declaration (SQLSTATE 428IA). The data type of each INOUT parameter in the procedure that is selected must have the exact same type as the corresponding data type specified in the AGGREGATE WITH clause (SQLSTATE 428IA).

Specify the following combinations of options for the selected procedure (SQLSTATE 428IA):

LANGUAGE C and NO SQL
LANGUAGE JAVA and NO SQL

The procedure must exist when this statement is run, unless the AUTO_REVAL database configuration parameter is set to DEFERRED_FORCE.

MERGE procedure-designator

Uniquely identifies a single procedure that implements the merge phase of the aggregation.

The procedure that is selected must meet the following criteria (SQLSTATE 428IA):

The procedure that is selected must first have the same number of input-only parameters as the number of the state variables specified in the AGGREGATE WITH clause.
The procedure that is selected must then have the same number of INOUT parameters as the number of the state variables specified in the AGGREGATE WITH clause.

The data type of each input-only parameter in the procedure that is selected must have the exact same data type as the corresponding data type specified in the AGGREGATE WITH clause (SQLSTATE 428IA). The data type of each INOUT parameter in the procedure that is selected must have the exact same type as the corresponding data type specified in the AGGREGATE WITH clause (SQLSTATE 428IA).

Specify the following combinations of options for the selected procedure (SQLSTATE 428IA):

LANGUAGE C and NO SQL
LANGUAGE JAVA and NO SQL

The procedure must exist when this statement is run, unless the AUTO_REVAL database configuration parameter is set to DEFERRED_FORCE.

FINALIZE function-designator

Uniquely identifies a single user-defined scalar function that implements the final result phase of the aggregation.

The function that is selected must have the same number of input-only parameters as the number of the state variables specified in the AGGREGATE WITH clause (SQLSTATE 428IA). The data type of each input-only parameter in the function that is selected must have the exact same data type as the corresponding data type specified in the AGGREGATE WITH clause (SQLSTATE 428IA). The output data type of the function that is selected must have the exact same type as the output data type specified in the RETURNS clause (SQLSTATE 428IA).

Specify the following combinations of options for the selected procedure (SQLSTATE 428IA):

LANGUAGE C and NO SQL
LANGUAGE JAVA and NO SQL

The function must exist when this statement is run, unless the AUTO_REVAL database configuration parameter is set to DEFERRED_FORCE.

In the descriptions for the INITIALIZE, ACCUMULATE, MERGE, and FINALIZE routines, exact same data type means that lengths, precisions, scales, string units, and CCSIDs are considered in this type comparison. Therefore, the following data types are considered different:

CHAR(8) and CHAR(35)
VARCHAR(10 OCTETS) and VARCHAR(10 CODEUNIT32)
DECIMAL(11,2) and DECIMAL (4,3)

A weakly typed distinct type is considered to be a different data type as the source type of the distinct type. CHAR(13) and GRAPHIC(13) are considered to be different types, even in a Unicode database.

procedure-designator

PROCEDURE procedure-name

Identifies a particular procedure, and is valid only if exactly one procedure instance with the name procedure-name exists in the schema. The identified procedure can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no procedure by this name exists in the named or implied schema, an error (SQLSTATE 42704) is returned. If there is more than one instance of the procedure in the named or implied schema, an error (SQLSTATE 42725) is returned. If a procedure by this name exists and the authorization ID of the statement does not have EXECUTE privilege on this procedure, an error (SQLSTATE 42501) is returned.

PROCEDURE procedure-name (data-type,...)

Provides the procedure signature, which uniquely identifies the procedure. The procedure resolution algorithm is not used.

procedure-name

Specifies the name of the procedure. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

(data-type,...)

Values must match the data types that were specified (in the corresponding position) on the CREATE PROCEDURE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific procedure instance.

If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path.

It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match.

FLOAT() cannot be used (SQLSTATE 42601) because the parameter value indicates different data types (REAL or DOUBLE).

If length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement. When length is specified for character and graphic string data types, the string unit of the length attribute must exactly match that specified in the CREATE PROCEDURE statement.

A type of FLOAT(n) does not need to match the defined value for n because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

If no procedure with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is returned.

If a procedure by this procedure signature exists and the authorization ID of the statement does not have EXECUTE privilege on this procedure, an error (SQLSTATE 42501) is returned.

SPECIFIC PROCEDURE specific-name

Identifies a particular procedure, by using the name that is specified or defaulted to at procedure creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is returned. If a procedure by this specific-name exists, and the authorization ID of the statement does not have EXECUTE privilege on this procedure, an error (SQLSTATE 42501) is returned.

FUNCTION function-name

Identifies a particular function, and is valid only if exactly one function instance with the name function-name exists in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is returned. If there is more than one instance of the function in the named or implied schema, an error (SQLSTATE 42725) is returned. If a function by this name exists and the authorization ID of the statement does not have EXECUTE privilege on this function, an error (SQLSTATE 42501) is returned.

function-designator

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

Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used.

function-name

Specifies the name of the function. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

(data-type,...)

Values must match the data types that were specified (in the corresponding position) on the CREATE FUNCTION statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific function instance.

If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path.

FLOAT() cannot be used (SQLSTATE 42601) because the parameter value indicates different data types (REAL or DOUBLE).

If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. When length is specified for character and graphic string data types, the string unit of the length attribute must exactly match that specified in the CREATE FUNCTION statement.

A type of FLOAT(n) does not need to match the defined value for n because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is returned.

If a function by this function signature exists and the authorization ID of the statement does not have EXECUTE privilege on this function, an error (SQLSTATE 42501) is returned.

SPECIFIC FUNCTION specific-name

Identifies a particular user-defined function, by using the name that is specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is returned. If a function by this specific-name exists, and the authorization ID of the statement does not have EXECUTE privilege on this function, an error (SQLSTATE 42501) is returned.

Notes

Privileges: The definer of a function always receives the EXECUTE privilege on the function. The definer of a function also receives the right to drop the function. The definer of the function is also given the WITH GRANT OPTION if the definer of the function has EXECUTE WITH GRANT OPTION on all of the component routines.

Examples

Define an aggregate function that returns the average of a set of numeric values, by using Java routines.

CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_initialize';
 
CREATE OR REPLACE PROCEDURE myavg_accumulate(IN input DOUBLE, INOUT sum DOUBLE, INOUT count INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_accumulate';

CREATE OR REPLACE PROCEDURE myavg_merge(IN sum DOUBLE, IN count INT, 
      INOUT mergesum DOUBLE, INOUT mergecount INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_merge';

CREATE OR REPLACE FUNCTION myavg_finalize(sum DOUBLE, count INT)
   RETURNS DECFLOAT(34)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_finalize';

CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DECFLOAT(34)
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING 
   INITIALIZE PROCEDURE myavg_initialize
   ACCUMULATE PROCEDURE myavg_accumulate
   MERGE PROCEDURE myavg_merge
   FINALIZE FUNCTION myavg_finalize;

Define an aggregate function with procedure and function names that are unqualified. Define the aggregate function under the schema FOO. Invoke the aggregate function under schema BAR.
```
SET SCHEMA FOO;

CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE PROCEDURE myavg_initialize
   ACCUMULATE PROCEDURE myavg_accumulate
   MERGE PROCEDURE myavg_merge
   FINALIZE FUNCTION myavg_finalize;

SET SCHEMA BAR;

SELECT FOO.myavg(c1) FROM t1;
```
The database manager looks for procedures with the names of FOO.myavg_initialize, FOO.myavg_accumulate, FOO.myavg_merge, and function with the name of FOO.myavg_finalize for the invocation of FOO.myavg.
Define an aggregate function with specific procedure and function names that are unqualified. Define the aggregate function under the schema FOO. Invoke the aggregate function under schema BAR.
```
SET SCHEMA FOO;

CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE SPECIFIC PROCEDURE myavg_initialize1
   ACCUMULATE SPECIFIC PROCEDURE myavg_accumulate1
   MERGE SPECIFIC PROCEDURE myavg_merge1
   FINALIZE SPECIFIC FUNCTION myavg_finalize1;

SET SCHEMA BAR;

SELECT FOO.myavg(c1) FROM t1;
```
The database manager looks for procedures with the specific names of FOO.myavg_initialize1, FOO.myavg_accumulate1, FOO.myavg_merge1, and function with the specific name of FOO.myavg_finalize1 for the invocation of FOO.myavg.

Define an aggregate function without some of the component routines. The aggregate function will be created as invalid and revalidation will be invoked in the next access.

UPDATE DB CFG USING AUTO_REVAL DEFERRED_FORCE;

CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_initialize';
 
CREATE OR REPLACE PROCEDURE myavg_accumulate(IN input DOUBLE, INOUT sum DOUBLE, INOUT count INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_accumulate';

-- component routine merge and finalize are missing, the creation is successful and myavg is invalid:
CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE  PROCEDURE myavg_initialize
   ACCUMULATE PROCEDURE myavg_accumulate
   MERGE PROCEDURE myavg_merge
   FINALIZE FUNCTION myavg_finalize;

CREATE OR REPLACE PROCEDURE myavg_merge(IN sum DOUBLE, IN count INT, 
      INOUT mergesum DOUBLE, INOUT mergecount INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_merge';

CREATE OR REPLACE FUNCTION myavg_finalize(sum DOUBLE, count INT)
   RETURNS DOUBLE
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_finalize';

-- revalidation of myavg will be invoked and it will be successful
SELECT myavg(c1) FROM t1;

Define an aggregate function by using a global variable as a default for its parameter. Dropping the global variable invalidates the function.

CREATE VARIABLE gv1 DOUBLE;

-- create all 4 component routines (myavg_initialize, myavg_accumulate, myavg_merge, myavg_finalize) like Example 1
...
...
...

CREATE OR REPLACE FUNCTION myavg(p1 DOUBLE DEFAULT gv1)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE SPECIFIC PROCEDURE myavg_initialize
   ACCUMULATE SPECIFIC PROCEDURE myavg_accumulate
   MERGE SPECIFIC PROCEDURE myavg_merge
   FINALIZE SPECIFIC FUNCTION myavg_finalize;

-- the following statement invalidates the function 'myavg'
DROP VARIABLE gv1;

Define an aggregate function whose component routines use a global variable as a default for its parameter. Dropping the global variable invalidates both the component routine and the aggregate function.

CREATE VARIABLE gv1 INT;

CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT DEFAULT gv1)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_initialize';

-- create the remaining 3 component routines (myavg_accumulate, myavg_merge, myavg_finalize) like Example 1
...
...
...

CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE SPECIFIC PROCEDURE myavg_initialize
   ACCUMULATE SPECIFIC PROCEDURE myavg_accumulate
   MERGE SPECIFIC PROCEDURE myavg_merge
   FINALIZE SPECIFIC FUNCTION myavg_finalize;

-- the following statement invalidates both the routine 'myavg_initialize' and the function 'myavg'
DROP VARIABLE gv1;

Define an aggregation function, then create a procedure that calls the aggregation function. Next, drop or replace one of the component routines. Either action invalidates both the aggregation function and the procedure that calls it.

CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL 
   EXTERNAL NAME 'myclass!myavg_initialize';

-- create the remaining 3 component routines (myavg_accumulate, myavg_merge, myavg_finalize) like Example 1
...
...
...

CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS DOUBLE
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE SPECIFIC PROCEDURE myavg_initialize
   ACCUMULATE SPECIFIC PROCEDURE myavg_accumulate
   MERGE SPECIFIC PROCEDURE myavg_merge
   FINALIZE SPECIFIC FUNCTION myavg_finalize;

CREATE OR REPLACE PROCEDURE myproc (OUT p1 DOUBLE)
BEGIN
   SET p1 = (SELECT myavg(c1) FROM t1);
END;

-- drop the component routine
-- this action invalidates both the 'myavg' aggregation function and the 'myproc' procedure that calls it:
DROP PROCEDURE myavg_initialize;
 
-- re-create the component routine
-- like the DROP statement, this action invalidates both the 'myavg' aggregation function and the 'myproc' procedure that calls it:
CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT)
   LANGUAGE C PARAMETER STYLE C
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_initialize_C_version';

-- revaliation is invoked the next time 'myproc' is accessed
-- both the 'myavg' aggregation function and the 'myproc' procedure are revalidated
CALL myproc(?);

Use the AUTO_REVAL database configuration parameter to control the invalidation and revalidation semantics.

UPDATE DB CFG USING AUTO_REVAL DEFERRED_FORCE;

-- global variable 'gv1' does not exist; the 'myavg_initialize' procedure can be created, but it is invalid
CREATE OR REPLACE PROCEDURE myavg_initialize(OUT sum DOUBLE, OUT count INT default gv1)
   LANGUAGE JAVA PARAMETER STYLE JAVA
   FENCED NO SQL
   EXTERNAL NAME 'myclass!myavg_initialize';

-- create the remaining 3 component routines (myavg_accumulate, myavg_merge, myavg_finalize) like Example 1
...
...
...

-- the 'myavg' function can be created, but it is invalid 
CREATE OR REPLACE FUNCTION myavg(DOUBLE)
   RETURNS mydouble
   AGGREGATE WITH (sum DOUBLE, count INT)
   USING
   INITIALIZE SPECIFIC PROCEDURE myavg_initialize
   ACCUMULATE SPECIFIC PROCEDURE myavg_accumulate
   MERGE SPECIFIC PROCEDURE myavg_merge
   FINALIZE SPECIFIC FUNCTION myavg_finalize;

-- create the global variable 'gv1'
CREATE VARIABLE gv1 DOUBLE;

-- revalidation of 'myavg' function and 'myavg_initialize' procedure is invoked; revalidation is successful
SELECT myavg(c1) FROM t1;

-- change the setting of the AUTO_REVAL database configuration parameter to IMMEDAITE
UPDATE DB CFG USING AUTO_REVAL IMMEDIATE;

-- the CREATE OR REPLACE VARIABLE statement invokes the revalidation for both 'myavg' function and 'myavg_initialize' procedure
CREATE OR REPLACE VARIABLE gv1 DOUBLE DEFAULT 1.0;