For SQL naming, the function will be created in the schema specified by the implicit or explicit qualifier.
For system naming, the function will be created in schema that is specified by the qualifier. If no qualifier is specified:
If the function is sourced on an existing function to enable the use of the existing function with a distinct type, the name can be the same name as the existing function. In general, more than one function can have the same name if the function signature of each function is unique.
Certain function names are reserved for system use. For more information see Choosing the Schema and Function Name.
Any valid SQL data type may be used provided it is castable to the type of the corresponding parameter of the function identifed in the SOURCE clause (for information see Casting between data types). However, this checking does not guarantee that an error will not occur when the function is invoked. For more information, see Considerations for invoking a sourced user-defined function.
If the name of the distinct type is specified without a schema name, the database manager resolves the schema name by searching the schemas in the SQL path.
DataLinks are not allowed for functions sourced on external functions.
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.
Any valid SQL data type can be used provided it is castable from the result type of the source function. (For information on casting data types, see Casting between data types) However, this checking does not guarantee that an error will not occur when this new function is invoked. For more information, see Considerations for invoking a sourced user-defined function.
If specific name is not specified, it is set to the function name. If a function or procedure with that specific name already exists, a unique name is generated similar to the rules used to generate unique table names.
The source function can be any aggregate function or any built-in scalar function except COALESCE, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM, EXTRACT, HASH, HASHED_VALUE, LAND, LOR, MAX, MIN, NODENAME, NODENUMBER, PARTITION, POSITION, RAISE_ERROR, RRN, STRIP, SUBSTRING, TRIM, VALUE, and XOR, or any previously created user-defined function. It can be a system-generated user-defined function (generated when a distinct type was created).
The source function can be one of the following built-in scalar functions only if one argument is specified: BINARY, BLOB, CHAR, CLOB, DBCLOB, DECIMAL, DECRYPT_BIN, DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, DECRYPT_DB, GRAPHIC, TRANSLATE, VARBINARY, VARCHAR, VARGRAPHIC, and ZONED.
If you base the sourced function directly or indirectly on a scalar function, the sourced function inherits the attributes of the scalar function. This can involve several layers of sourced functions. For example, assume that function A is sourced on function B, which in turn is sourced on function C. Function C is a scalar function. Functions A and B inherit all of the attributes that are specified on CREATE FUNCTION statement for function C.
If function-name() is specified, the function identified must have zero parameters.
To use a built-in function as the source function, this syntax variation must be used.
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, you can specify a value or use a set of empty parentheses.
For data types with a subtype or CCSID attribute, 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 you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.
The number of input parameters in the function that is being created must be the same as the number of parameters in the source function. If the data type of each input parameter is not the same as or castable to the corresponding parameter of the source function, an error occurs. The data type of the final result of the source function must match or be castable to the result of the sourced function.
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).
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.