Types of functions
There are several ways to classify functions. One way to classify functions
is as built-in, user-defined, or generated user-defined functions for distinct
types.
- Built-in functions are functions that
come with the database manager. These functions provide a single-value result. Built-in
functions include operator functions such as "+", aggregate functions such
as AVG, and scalar functions such as SUBSTR. For a list of the built-in aggregate
and scalar functions and information on these functions, see Built-in functions.
The built-in functions are part of schema
QSYS2. 33
- User-defined functions are functions
that are created using the CREATE FUNCTION statement and registered to the database manager in
catalog table QSYS2.SYSROUTINES and catalog view QSYS2.SYSFUNCS. For more
information, see CREATE FUNCTION. These functions allow users to extend
the function of the database manager by adding their own or third party vendor
function definitions.
A user-defined function is either an SQL, external, or sourced function. An SQL function is defined to the database using only SQL
statements. An external function is defined to the database with a reference
to an external program or service program that is executed when the function
is invoked. A sourced function is defined to the database with a reference
to a built-in function or another user-defined function. Sourced functions
can be used to extend built-in aggregate and scalar functions for use on distinct
types.
A user-defined function resides in the schema in which it was
created. The schema cannot be QSYS, QSYS2, or QTEMP.
- Generated user-defined functions for distinct types are functions that the database manager automatically generates when
a distinct type is created using the CREATE DISTINCT TYPE statement. These
functions support casting from the distinct type to the source type and from
the source type to the distinct type. The ability to cast between the data
types is important because a distinct type is compatible only with itself.
The generated cast functions reside in the same schema as the distinct type
for which they were created. The schema cannot be QSYS, QSYS2, or QTEMP. For
more information about the functions that are generated for a distinct type,
see CREATE DISTINCT TYPE.
Another way to classify functions is as aggregate, scalar, or
table functions, depending on the input data values and result values.
- An aggregate function receives a set of values
for each argument (such as the values of a column) and returns a single-value
result for the set of input values. Aggregate functions are sometimes called column functions. Built-in functions and user-defined
sourced functions can be aggregate functions.
- A scalar function receives a single value for
each argument and returns a single-value result. Built-in functions and user-defined
functions can be scalar functions. Generated user-defined functions for distinct
types are also scalar functions.
- A table function returns a table for the set of
arguments it receives. Each argument is a single value. A table function can
only be referenced in the FROM clause of a subselect. A table function can
be defined as an external function or as an SQL function, but a table function
cannot be a sourced function.
Table functions can be used to apply SQL language
processing power to data that is not DB2(R) data or to convert such data into a DB2 table.
For example, a table function can take a file and convert it to a table, get
data from the Web and tabularize it, or access a Lotus(R) Notes(R) database and return information about
email messages.
Built-in functions are implemented internally
by the database manager, so an associated program or service program object does not exist
for a built-in function. Furthermore, the catalog
does not contain information about built-in functions.
However, built-in functions can be treated as if they
exist in QSYS2 and a built-in function name can be
qualified with QSYS2.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.