A function is
an operation denoted by a function name followed by one or more operands
that are enclosed in parentheses. A function represents a relationship
between a set of input values and a set of result values. The input
values to a function are called arguments. For example, the
TIMESTAMP function can be passed arguments of type DATE and TIME,
and the result is a TIMESTAMP.
There are several ways to classify
functions.
One way is to classify
functions as ether built-in or user-defined.- Built-in functions are provided with the database
manager. They return a single result value and are identified as part
of the SYSIBM schema. Such functions include aggregate functions (for
example, AVG), operator functions (for example, +), casting functions
(for example, DECIMAL), and scalar functions (for
example, CEILING).
- User-defined
functions are functions that are created using an SQL data
definition statement and registered to the database manager in the
catalog. User-defined schema functions are created using
the CREATE FUNCTION statement. For more information, see "CREATE
FUNCTION". A set of user-defined schema functions is
provided with the database manager in a schema called SYSFUN. User-defined
module functions are created using the ALTER MODULE ADD FUNCTION
or ALTER MODULE PUBLISH FUNCTION statements. For
more information, see "ALTER MODULE". A set of user-defined
module functions is provided with the database manager in a
set of modules in a schema called SYSIBMADM. A user-defined
function resides in the schema in which it was created or in
the module where it was added or published.
User-defined functions
extend the capabilities of the database system by adding function
definitions (provided by users or third party vendors) that can be
applied in the database engine itself. Extending database functions
lets the database exploit the same functions in the engine that an
application uses, providing more synergy between application and database.
Another way to classify a user-defined
function is as an external function, an SQL function, or a sourced
function.
- An external function is defined to the database with
a reference to an object code library, and a function within that
library that will be executed when the function is invoked. External
functions cannot be aggregate functions.
- An SQL function is defined to the database
using only SQL statements, including at least one RETURN statement. It
can return a scalar value, a row, or a table.
- SQL functions cannot be aggregate functions. A sourced function is
defined to the database with a reference to another built-in or user-defined
function that is already known to the database. Sourced functions
can be scalar functions or aggregate functions. They are useful for
supporting existing functions with user-defined types.
Another way to classify functions is
as a scalar,
aggregate, row, or table functions,
depending on the input data values and result values.
- A scalar function is a function that returns a single-valued
answer each time it is called. For example, the built-in function
SUBSTR() is a scalar function. Scalar UDFs can be either external
or sourced.
- An aggregate function is one which conceptually is
passed a set of like values (a column) and returns a single-valued
answer. An example of an aggregate function is the built-in function
AVG(). An external column UDF cannot be defined to DB2®, but a column UDF, which is sourced upon
one of the built-in aggregate functions, can be defined. This is useful
for distinct types. For example, if there is a distinct type SHOESIZE
defined with base type INTEGER, a UDF AVG(SHOESIZE), which is sourced
on the built-in function AVG(INTEGER), could be defined, and it would
be an aggregate function.
- A row function is a function that returns one row
of values. It can be used in a context where a
row expression is supported. It can also be used as a transform
function, mapping attribute values of a structured type into values
in a row. A row function must be defined as an SQL function.
- A table function is a function that returns a table
to the SQL statement which references it. It may only be referenced
in the FROM clause of a SELECT statement. Such a function can be used
to apply SQL language processing power to data that is not DB2 data, or to convert such data
into a DB2 table. A table function can read a file, get data from the
Web, or access a Lotus Notes® database
and return a result table. This information can be joined with
other tables in the database. A table function can be defined as an
external function or as an SQL function. (A table function cannot
be a sourced function.)
Function signatures
A
schema function is identified by its schema name, a function name,
the number of parameters, and the data types of its parameters. A module function is identified by its schema name,
module name, a function name, the number of parameters, and the data
types of its parameters. This identification
of a schema function or a module function is called a function
signature, which must be unique within the database; for example,
TEST.RISK(INTEGER). There can be more than one function with the same
name in a schema or a module, provided that the number of parameters
or the data types of the parameters are different. A function name
for which there are multiple function instances with
the same number of parameters is called an overloaded function.
A function name can be overloaded within a schema, in which case there
is more than one function by that name with the
same number of parameters in the schema. Similarly,
a function name can be overloaded within a module, in which case there
is more than one function by that name with the same number of parameters
in the module. These functions must have different parameter
data types. Functions can also be overloaded across
the schemas of an SQL path, in which case there is more than
one function by that name with the same number
of parameters in different schemas of the SQL path. These functions
do not necessarily have different parameter data types.
Function invocation
Each reference to a function conforms to the following
syntax:
(1)
>>-function-name--(------+----------+--+----------------------+--)-><
+-ALL------+ | .-,----------------. |
'-DISTINCT-' | V | |
'-----| argument |---+-'
argument
|--+--------------------+--+-expression-----+-------------------|
'-parameter-name--=>-' +-row-expression-+
'-DEFAULT--------'
Notes:
- The ALL or DISTINCT keyword
can be specified only for an aggregate function or a user-defined
function that is sourced on an aggregate function.
In the above syntax, expression and row-expression cannot
include an aggregate function. See "Expressions" for other rules
for expression.
A function
is invoked by referring (in an allowable context) to its qualified
or unqualified function name followed by the list of arguments enclosed
in parentheses. The possible qualifiers for a function name are:
- A schema name
- An unqualified module name
- A schema-qualified module name
The qualifier used when invoking a function determines the scope
used to search for a matching function.
- If a schema-qualified module name is used as the qualifier, the
scope is the specified module.
- If a single identifier is used as the qualifier, the scope includes:
- The schema that matches the qualifier
- One of the following modules:
- The invoking module, if the invoking module name matches the qualifier
- The first module in a schema in the SQL path that matches the
qualifier
- If no qualifier is used, the scope includes the schemas in the
SQL path and, if the function is invoked from within a module object,
the same module from which the function is invoked.
For static SQL statements, the SQL path is specified using the
FUNCPATH bind
option. For dynamic SQL statements, the SQL path is the value of the
CURRENT PATH special register.
When any function is invoked,
the database manager must determine which function to execute. This
process is called function resolution and applies to
both built-in and user-defined functions. It is recommended that function
invocations intending to invoke a user-defined function be fully qualified.
This improves performance of function resolution and prevents unexpected
function resolution results as new functions are added or privileges
granted.
An argument is
a value passed to a function upon invocation or the specification
of DEFAULT. When a function is invoked in SQL, it is passed a list
of zero or more arguments. They are positional in that the semantics
of an argument are determined by its position in the argument list.
A parameter is a formal definition of an input to a function
or an output from a function. When a function is defined to the database,
either internally (a built-in function) or by a user (a user-defined
function), its parameters (zero or more) are specified, and the order
of their definitions defines their positions and their semantics.
Therefore, every parameter is a particular positional input to a function
or an output from a function. On invocation, an argument is assigned
to a parameter using either the positional syntax or the named syntax.
If using the positional syntax, an argument corresponds to a particular
parameter according to its position in the list of arguments. If using
the named syntax, an argument corresponds to a particular parameter
by the name of the parameter. When an argument is assigned to a parameter
using the named syntax, then all the arguments that follow it must
also be assigned using the named syntax (SQLSTATE 4274K). The name
of a named argument can appear only once in a function invocation
(SQLSTATE 4274K). In cases where the data types of the arguments of
the function invocation are not a match to the data types of the parameters
of the selected function, the arguments are converted to the data
type of the parameter at execution time using the same rules as assignment
to columns. This includes the case where precision, scale, or length
differs between the argument and the parameter. In cases where the
arguments of the function invocation are the specification of DEFAULT,
the actual value used for the argument is the value specified as the
default for the corresponding parameter in the function definition.
If no default value was defined for the parameter, the null value
is used. If an untyped expression (a parameter marker, a NULL keyword,
or a DEFAULT keyword) is used as the argument, the data type associated
with the argument is determined by the parameter data type of the
parameter of the selected function.
Access to schema functions
is controlled through the EXECUTE privilege on the schema functions.
If the authorization ID of the statement invoking the function does
not have EXECUTE privilege, the schema function will not be considered
by the function resolution algorithm, even if it is a better match.
Built-in functions (SYSIBM functions) and functions in the SYSFUN
schema have the EXECUTE privilege implicitly granted to PUBLIC.
Access
to module functions is controlled through EXECUTE privilege on the
module for all functions within the module. The authorization ID of
the statement invoking the function might not have EXECUTE privilege
on a module. In such cases, module functions within that module, unlike
schema functions, are still considered by the function resolution
algorithm even though they cannot be executed.
When the user-defined
function is invoked, the value of each of its arguments is assigned,
using storage assignment, to the corresponding parameter of the function.
Control is passed to external functions according to the calling conventions
of the host language. When execution of a user-defined scalar function
or a user-defined aggregate function is complete, the result of the
function is assigned, using storage assignment, to the result data
type. For details on the assignment rules, see "Assignments and
comparisons".
Table functions can be referenced only in the
FROM clause of a subselect. For more details on referencing a table
function, see "table-reference".
Function resolution
After a function is invoked, the database manager
must determine which function to execute. This process is called function
resolution and applies for both built-in and user-defined functions.
The
database manager first determines the set of candidate functions based
on the following information:
- The qualification of the name of the invoked function
- The context that invokes the function
- The unqualified name of the invoked function
- The number of arguments specified
- Any argument names that are specified
- The authorization of schema functions.
See
Determining the set of candidate functions for details.
The
database manager then determines the best fit from the set of candidate
functions based on the data types of the arguments of the invoked
function as compared with the data types of the parameters of the
functions in the set of candidate functions. The SQL path and number
of parameters is also considered. See Determining the best fit for
details.
Once
a function is selected, it is still possible for an error to be returned
for one of the following reasons:
- If a module function is selected and either the function is invoked
from outside a module or the function is invoked from within a module
object and the qualifier does not match the context module name, the
authorization ID of the statement that invoked the function must have
EXECUTE privilege on the module that contains the selected function
(SQLSTATE 42501).
- If a function is selected, its successful use depends on it being
invoked in a context in which the returned result is allowed. For
example, if the function returns a table where a table is not allowed,
an error is returned (SQLSTATE 42887).
- If a cast function is selected, either built-in or user-defined,
and any argument would need to be implicitly cast (not promoted) to
the data type of the parameter, an error is returned (SQLSTATE 42884).
- If a function invocation involves an argument with an unnamed
row type, an error is returned (SQLSTATE 42884) if either of the following
conditions occur:
- The number of fields of the argument does not match the number
of fields of the parameter.
- The data types of the fields of the argument are not assignable
to the corresponding data type of the fields of the parameter.
Determining the set
of candidate functions
- Let A be the number of arguments in a function
invocation.
- Let P be the number of parameters in a function
signature.
- Let N be the number of parameters in a function
signature without a defined default.
Candidate functions for resolution of a function invocation are
selected based on the following criteria:
- Each candidate schema function has a matching name and applicable
number of parameters. An applicable number of parameters satisfies
the condition N ≤ A ≤ P.
- Each candidate module function has parameters such that for each
named argument in the function invocation there exists a parameter
with a matching name that does not already correspond to a positional
(unnamed) argument.
- Each parameter of a candidate function that does not have a corresponding
argument in the function invocation, specified by either position
or name, is defined with a default.
- Each candidate function from a set of one or more schemas has
the EXECUTE privilege associated with the authorization ID of the
statement invoking the function.
- Each candidate function from a module other than the context module
is a published module function.
The functions selected for the set of candidate
functions are from one or more of the following search spaces.
- The context module, that is, the module which contains
the module object that invoked the function
- A set of one or more schemas
- A module other than the context module
The specific search spaces considered are affected by the qualification
of the name of the invoked function.
- Qualified function invocation:
When a function is invoked with a function name and a qualifier, the
database manager uses the qualifier and, in some cases, the context
of the invoked function to determine the set of candidate functions.
- If a function is invoked from within a module object using a function
name with a qualifier, the database manager considers if the qualifier
matches the context module name. If the qualifier is a single identifier,
then the schema name of the module is ignored when determining a match.
If the qualifier is a two-part identifier, then it is compared to
the schema-qualified module name when determining a match. If the
qualifier matches the context module name, the database manager searches
the context module for candidate functions.
If
one or more candidate functions are found in the context module, then
this set of candidate functions is processed for best fit without
consideration of possible candidate functions in any other search
space (see "Determining the best fit"). Otherwise, continue to
the next search space.
- If the qualifier is a single identifier, the database manager
considers the qualifier as a schema name and searches that schema
for candidate functions.
If
one or more candidate functions are found in the schema, then this
set of candidate functions is processed for best fit without consideration
of possible candidate functions in any other search space (see "Determining
the best fit"). Otherwise, continue to the next search space, if
applicable.
- If the function is invoked from outside a module or the qualifier
does not match the context module name when it is invoked from within
a module object, the database manager considers the qualifier as a
module name. Without considering EXECUTE privilege on modules, the
database manager then selects the first module that matches based
on the following criteria:
- If the module name is qualified with a schema name, select the
module with that schema name and module name.
- If the module name is not qualified with a schema name, select
the module with that module name that is found in the schema earliest
in the SQL path.
- If the module is not found using the SQL path, select the module
public alias with that module name.
If a matching module is not found, then there are no candidate
functions. If a matching module is found, the database manager searches
the selected module for candidate functions. If
one or more candidate functions are found in the selected modules,
then this set of candidate functions is processed for best fit (see "Determining
the best fit").
- Unqualified function
invocation: When a function is invoked without a qualifier,
the database manager considers the context of the invoked function
to determine the sets of candidate functions.
- If a function is invoked with an unqualified function name from
within a module object, the database manager searches the context
module for candidate functions.
If one or more candidate functions
are found in the context module, then these candidate functions are
included with any candidate functions from the schemas in the SQL
path (see next item).
- If a function is invoked with an unqualified function name, either
from within a module object or from outside a module, the database
manager searches the list of schemas in the SQL path to resolve the
function instance to execute. For each schema in the SQL path (see "SQL
path"), the database manager searches the schema for candidate
functions.
If
one or more candidate functions are found in the schemas in the SQL
path, then these candidate functions are included with any candidate
functions from the context module (see previous item). This set of
candidate functions is processed for best fit (see "Determining
the best fit").
If the database manager does not find any candidate
functions, an error is returned (SQLSTATE 42884).
Determining the best fit
The
set of candidate functions may contain one function or more than one
function with the same name. In either case, the data types of the
parameters, the position of the schema in the SQL path, and the total
number of parameters of each function in the set of candidate functions
are used to determine if the function meets the best fit requirements.
If the set of candidate functions contains
more than one function and named arguments are used in the function
invocation, the ordinal position of the parameter corresponding to
a named argument must be the same for all candidate functions (SQLSTATE
4274K).
The term set of parameters is
used to refer to all of the parameters at the same position in the
parameter lists (where such a parameter exists) for the set of candidate
functions. The corresponding argument of a parameter is determined
based on how the arguments are specified in the function invocation.
For positional arguments, the corresponding argument to a parameter
is the argument in the same position in the function invocation as
the position of the parameter in the parameter list of the candidate
function. For named arguments, the corresponding argument to a parameter
is the argument with the same name as the parameter. In this case,
the order of the arguments in the function invocation is not considered
while determining the best fit. If the number of parameters in a candidate
function is greater than the number of arguments in the function invocation,
each parameter that does not have a corresponding argument is processed
as if it does have a corresponding argument that has the DEFAULT keyword
as the value.
The following steps are
used to determine the function that is the best fit:
- Step 1: Considering arguments that are typed expressions
- The database manager determines the function, or set of functions,
that meet the best fit requirements for the invocation by comparing
the data type of each parameter with the data type of the corresponding
argument.
When determining whether the data type
of a parameter is the same as the data type of its corresponding argument:
- Synonyms of data types match. For example, FLOAT and DOUBLE are
considered to be the same.
- Attributes of a data type such as length, precision, scale, and
code page are ignored. Therefore, CHAR(8) and CHAR(35) are considered
to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).
A subset of the candidate functions
is obtained by considering only those functions for which the data
type of each argument of the function invocation that is not an untyped
expression matches or is promotable to the data type of the corresponding
parameter of the function instance. If the argument of the function
invocation is an untyped expression, the data type of the corresponding
parameter can be any data type. The precedence list for the promotion
of data types in
Promotion of data types shows
the data types that fit (considering promotion) for each data type
in best-to-worst order. If this subset is not empty, then the best
fit is determined using the
Promotable
process on this subset of candidate functions. If this subset
is empty, then the best fit is determined using the
Castable process on the original
set of candidate functions.
- Promotable process
- This process determines the best fit only
considering whether arguments in the function invocation match or
can be promoted to the data type of the corresponding parameter of
the function definition. For the subset of candidate functions, the
parameter lists are processed from left to right, processing the set
of parameters in the first position from the subset of candidate functions
before moving on to the set of parameters in the second position,
and so on. The following steps are used to eliminate candidate functions
from the subset of candidate functions (only considering promotion):
- If one candidate function has a parameter where the data type
of the corresponding argument fits (only considering promotion) the
data type of the parameter better than other candidate functions,
those candidate functions that do not fit the function invocation
equally well are eliminated. The precedence list for the promotion
of data types in Promotion of data types shows
the data types that fit (considering promotion) for each data type
in best-to-worst order.
- If the data type of the corresponding argument is an untyped expression,
no candidate functions are eliminated.
- These steps are repeated for the next set of parameters from the
remaining candidate functions until there are no more sets of parameters.
- Castable process
- This process determines the best fit first
considering, for each parameter, if the data type of the corresponding
argument in the function invocation matches or can be promoted to
the data type of the parameter of the function definition. Then, for
each set of parameters where no corresponding argument has a data
type that was promotable, the database manager considers, for each
parameter, if the data type of the corresponding argument can be implicitly
cast for function resolution to the data type of the parameter.
For
the set of candidate functions, the parameters in the parameter lists
are processed from left to right, processing the set of parameters
in the first position from all the candidate functions before moving
on to the set of parameters in the second position, and so on. The
following steps are used to eliminate candidate functions from the
set of candidate functions (only considering promotion):
- If one candidate function has a parameter where the data type
of the corresponding argument fits (only considering promotion) the
data type of the parameter better than other candidate functions,
those candidate functions that do not fit the function invocation
equally well are eliminated. The precedence list for the promotion
of data types in Promotion of data types shows
the data types that fit (considering promotion) for each data type
in best-to-worst order.
- If the data type for the corresponding argument is not promotable
(which includes the case when the corresponding argument is an untyped
expression) to the data type of the parameter of any candidate function,
no candidate functions are eliminated.
- These steps are repeated for the next set of parameters from the
remaining candidate functions until there are no more sets of parameters.
If at least one set of parameters has no corresponding
argument that fit (only considering promotion) and the corresponding
argument for the set of parameters has a data type, the database manager
compares each such set of parameters from left to right. The following
steps are used to eliminate candidate functions from the set of candidate
functions (considering implicit casting).
- If all the data types of the set of parameters for all remaining
candidate functions do not belong to the same data type precedence
list, as specified in Promotion of data types,
an error is returned (SQLSTATE 428F5).
- If the data type of the corresponding arguments cannot be implicitly
cast to the data type of the parameters, as specified in Implicit
casting for function resolution, an error is returned (SQLSTATE 42884).
- If one candidate function has a parameter where the data type
of the corresponding argument fits (considering implicit casting)
the data type of the parameter better than other candidate functions,
those candidate functions that do not fit the function invocation
equally well are eliminated. The data type list in Implicit casting for function
resolution shows the data type that fits (considering implicit
casting) better.
- These steps are repeated for the next set of parameters which
has no corresponding argument that fit (only considering promotion)
and the corresponding argument for the set of parameters has a data
type until there are no more such sets of parameters or an error occurs.
- Step 2: Considering SQL path
- If more than one candidate function remains and a context module
exists that still includes candidate functions, the database manager
selects those functions. If there is no context module or no candidate
functions remain in the context module, the database manager selects
those candidate functions whose schema is earliest in the SQL path.
- Step 3: Considering number of arguments in the function invocation
- If more than one candidate function remains and if one candidate
function has a number of parameters that is less than or equal to
the number of parameters of the other candidate functions, those candidate
functions that have a greater number of parameters are eliminated.
- Step 4: Considering arguments that are untyped expressions
- If more than one candidate function remains and at least one set
of parameters has a corresponding argument that is an untyped expression,
the database manager compares each such set of parameters from left
to right. The following steps are used to eliminate candidate functions
from the set of candidate functions:
- If all the data types of the set of parameters for all remaining
candidate functions do not belong to the same data type precedence
list, as specified in Promotion of data types,
an error is returned (SQLSTATE 428F5).
- If the data type of the parameter of one candidate function is
further left in the data type ordering for implicit casting than other
candidate functions, those candidate functions where the data type
of the parameter is further right in the data type ordering are eliminated.
The data type list in "Implicit casting for function resolution" shows
the data type ordering for implicit casting.
If there are still multiple candidate functions, an error is
returned (SQLSTATE 428F5).
- Implicit casting for function resolution
- Implicit casting for function resolution is not supported for
arguments with a user-defined type, reference type, or XML data type. It is also not supported for built-in or user-defined
cast functions. It is supported for the following cases:
- A value of one data type can be cast to any other data type that
is in the same data type precedence list, as specified in Promotion of data types.
- A numeric or datetime data type can be cast to a character or
graphic string data type, except for LOBs
- A character or graphic string type, except LOBs, can be cast to
a numeric or datetime data type
- A character FOR BIT DATA can be cast to a BLOB
and a BLOB can be cast to a character FOR BIT DATA
- A TIMESTAMP data type can be cast to a TIME data type
- An
untyped argument can be cast to any data type.
Similar to the data type precedence list for promotion, for
implicit casting there is an order to the data types that are in the
group of related data types. This order is used when performing function
resolution that considers implicit casting. Table 1 shows the data type ordering
for implicit casting for function resolution. The data types are listed
in best-to-worst order (note that this is different than the ordering
in the data type precedence list for promotion). Note, when function
resolution selects a built-in function and implicit casting is necessary
for some argument, if the built-in function supports both character
input and graphic input for the parameter, the argument is implicitly
cast to character.
Table 1. Data type
ordering for implicit casting for function resolutionData type group |
Data type list for implicit casting for function
resolution (in best-to-worst order) |
Numeric data types |
DECFLOAT, double, real, decimal, BIGINT, INTEGER,
SMALLINT |
Character and graphic string data types |
VARCHAR or VARGRAPHIC, CHAR or GRAPHIC, CLOB
or DBCLOB |
Datetime data types |
TIMESTAMP, DATE |
Notes:- The lower case types above are defined as follows:
- decimal = DECIMAL (p,s)
or NUMERIC(p,s)
- real = REAL or FLOAT(n) where n is
not greater than 24
- double = DOUBLE, DOUBLE-PRECISION, FLOAT or FLOAT(n),
where n is greater than 24
Shorter and longer form synonyms of the listed data types are
considered to be the same as the listed form.
- For a Unicode database only, the following are considered to be
equivalent data types:
- CHAR or GRAPHIC
- VARCHAR and VARGRAPHIC
- CLOB and DBCLOB
Table 2. Derived length of an argument when invoking a built-in scalar
function in cases where implicit casting is needed.Source Data Type |
Target Type
and Length |
CHAR
|
GRAPHIC
|
VARCHAR
|
VARGRAPHIC
|
CLOB
|
DBCLOB
|
BLOB
|
TIMESTAMP
|
DECFLOAT
|
UNTYPED |
127 |
127 |
254 |
254 |
32767 |
32767 |
32767 |
12 |
34 |
SMALLINT |
6 |
6 |
6 |
6 |
- |
- |
- |
- |
- |
INTEGER |
11 |
11 |
11 |
11 |
- |
- |
- |
- |
- |
BIGINT |
20 |
20 |
20 |
20 |
- |
- |
- |
- |
- |
DECIMAL(p,s) |
2+p |
2+p |
2+p |
2+p |
- |
- |
- |
- |
- |
REAL |
24 |
24 |
24 |
24 |
- |
- |
- |
- |
- |
DOUBLE |
24 |
24 |
24 |
24 |
- |
- |
- |
- |
- |
DECFLOAT |
42 |
42 |
42 |
42 |
- |
- |
- |
- |
- |
CHAR(n) |
- |
- |
- |
- |
- |
- |
min(n,254) |
12 |
34 |
VARCHAR(n) |
min(n,254) |
min(n,127) |
- |
- |
- |
- |
min(n,32672) |
12 |
34 |
CLOB(n) |
min(n,254) |
min(n,127) |
min(n,32672) |
min(n,16336) |
- |
- |
- |
- |
- |
GRAPHIC(n) |
- |
- |
- |
- |
- |
- |
- |
12 |
34 |
VARGRAPHIC(n) |
min(n,254) |
min(n,127) |
- |
- |
- |
- |
- |
12 |
34 |
DBCLOB(n) |
min(n,254) |
min(n,127) |
min(n,32672) |
min(n,16336) |
- |
- |
- |
- |
- |
BLOB(n) |
min(n,254) |
- |
min(n,32672) |
- |
- |
- |
- |
- |
- |
TIME |
8 |
8 |
8 |
8 |
- |
- |
- |
- |
- |
DATE |
10 |
10 |
10 |
10 |
- |
- |
- |
- |
- |
TIMESTAMP(p) |
if p=0 then
19 else p+20 |
if p=0 then
19 else p+20 |
if p=0 then
19 else p+20 |
if p=0 then
19 else p+20 |
- |
- |
- |
- |
- |
SQL path considerations
for built-in functions
Built-in functions reside in a special
schema called SYSIBM. Additional functions are available in the SYSFUN,
SYSPROC, and SYSIBMADM schemas as well as in modules
within the SYSIBMADM schema, but are not considered built-in
functions because they are developed as user-defined functions and
have no special processing considerations. Users cannot define additional
functions in the SYSIBM, SYSFUN, SYSPROC, or SYSIBMADM schemas (or
in any other schema whose name begins with the letters 'SYS', except
SYSTOOLS).
As already stated, the built-in functions participate
in the function resolution process exactly as do the user-defined
functions. One difference between built-in and user-defined functions,
from a function resolution perspective, is that the built-in functions
must always be considered during function resolution. Therefore, omission
of SYSIBM from the path results in the assumption (for function and
data type resolution) that SYSIBM is the first schema on the path.
For
example, if a user's SQL path is defined as:
"SHAREFUN","SYSIBM","SYSFUN"
and
there is a LENGTH function defined in schema SHAREFUN with the same
number and types of arguments as SYSIBM.LENGTH, then an unqualified
reference to LENGTH in this user's SQL statement will result in selecting
SHAREFUN.LENGTH. However, if the user's SQL path is defined as:
"SHAREFUN","SYSFUN"
and
the same SHAREFUN.LENGTH function exists, then an unqualified reference
to LENGTH in this user's SQL statement will result in selecting SYSIBM.LENGTH,
because SYSIBM implicitly appears first in the path.
To minimize
potential problems in this area:
- Never use the names of built-in functions for user-defined functions.
- If, for some reason, it is necessary to create a user-defined
function with the same name as a built-in function, be sure to qualify
any references to it.
Note: Some invocations of built-in functions do
not support SYSIBM as an explicit qualifier and resolve directly to
the built-in function without considering the SQL path. Specific cases
are covered in the description of the built-in function.
Examples of function resolution
The
following are examples of function resolution. (Note that not all
required keywords are shown.)
- This is an example illustrating the SQL path considerations in
function resolution. For this example, there are eight ACT functions,
in three different schemas, registered as:
CREATE FUNCTION AUGUSTUS.ACT (CHAR(5), INT, DOUBLE) SPECIFIC ACT_1 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_2 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE, INT) SPECIFIC ACT_3 ...
CREATE FUNCTION JULIUS.ACT (INT, DOUBLE, DOUBLE) SPECIFIC ACT_4 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_5 ...
CREATE FUNCTION JULIUS.ACT (SMALLINT, INT, DOUBLE) SPECIFIC ACT_6 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_7 ...
CREATE FUNCTION NERO.ACT (INT, INT, DEC(7,2)) SPECIFIC ACT_8 ...
The
function reference is as follows (where I1 and I2 are INTEGER columns,
and D is a DECIMAL column):
SELECT ... ACT(I1, I2, D) ...
Assume
that the application making this reference has an SQL path established
as:
"JULIUS","AUGUSTUS","CAESAR"
Following
through the algorithm...
- The function with specific name ACT_8 is eliminated as a candidate,
because the schema NERO is not included in the SQL path.
- The function with specific name ACT_3 is eliminated as a candidate,
because it has the wrong number of parameters. ACT_1 and ACT_6 are
eliminated because, in both cases, the first argument cannot be promoted
to the data type of the first parameter.
- Because there is more than one candidate remaining, the arguments
are considered in order.
- For the first argument, the remaining functions, ACT_2, ACT_4,
ACT_5, and ACT_7 are an exact match with the argument type. No functions
can be eliminated from consideration; therefore the next argument
must be examined.
- For this second argument, ACT_2, ACT_5, and ACT_7 are exact matches,
but ACT_4 is not, so it is eliminated from consideration. The next
argument is examined to determine some differentiation among ACT_2,
ACT_5, and ACT_7.
- For the third and last argument, neither ACT_2, ACT_5, nor ACT_7
match the argument type exactly. Although ACT_2 and ACT_5 are equally
good, ACT_7 is not as good as the other two because the type DOUBLE
is closer to DECIMAL than is DECFLOAT. ACT_7 is eliminated..
- There are two functions remaining, ACT_2 and ACT_5, with identical
parameter signatures. The final tie-breaker is to see which function's
schema comes first in the SQL path, and on this basis, ACT_5 is the
function chosen.
- This is an example of a situation where function resolution will
result in an error (SQLSTATE 428F5) since more than one candidate
function fits the invocation equally well, but the corresponding parameters
for one of the arguments do not belong to the same type precedence
list.
For this example, there are only three function in a single
schema defined as follows:
CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DATE) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...
The function reference is as follows (where I1
and I2 are INTEGER columns, and VC is a VARCHAR column):
SELECT ... ACT(I1, I2, VC) ...
Assume
that the application making this reference has an SQL path established
as:
"CAESAR"
Following through the algorithm
...
- Each of the candidate functions is evaluated to determine if the
data type of each input argument of the function invocation matches
or is promotable to the data type of the corresponding parameter of
the function instance:
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, ACT_1 is eliminated because INTEGER is
not promotable to VARCHAR.
- For the third argument, both ACT_2 and ACT_3 are eliminated since
VARCHAR is not promotable to DATE or DOUBLE, so no candidate functions
remain.
- Since the subset of candidate functions from above is empty, the
candidate functions are considered using the castable process:
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, ACT_1 is eliminated since INTEGER is
not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
- For the third argument, the data type of the corresponding parameters
of ACT_2 and ACT_3 do not belong to the same data type precedence
list, so an error is returned (SQLSTATE 428F5).
- This example illustrates a situation where function resolution
will succeed using the castable process. For this example, there are
only three function in a single schema defined as follows:
CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...
The function reference is as follows (where I1 and
I2 are INTEGER columns, and VC is a VARCHAR column):
SELECT ... ACT(I1, I2, VC) ...
Assume
that the application making this reference has an SQL path established
as:
"CAESAR"
Following through the algorithm
...
- Each of the candidate functions is evaluated to determine if the
data type of each input argument of the function invocation matches
or is promotable to the data type of the corresponding parameter of
the function instance:
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, ACT_1 is eliminated because INTEGER is
not promotable to VARCHAR.
- For the third argument, both ACT_2 and ACT_3 are eliminated since
VARCHAR is not promotable to DECFLOAT or DOUBLE, so no candidate functions
remain.
- Since the subset of candidate functions from above is empty,
the candidate functions are considered using the castable process:
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, ACT_1 is eliminated since INTEGER is
not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
- For the third argument, both DECFLOAT and DOUBLE are in the same
data type precedence list and VARCHAR can be implicitly cast to both
DECFLOAT and DOUBLE. Since DECFLOAT is a better fit for the purpose
of implicit casting, ACT_2 is the best fit
- This example illustrates that during function resolution using
the castable process that promotion of later arguments takes precedence
over implicit casting. For this example, there are only three function
in a single schema defined as follows:
CREATE FUNCTION CAESAR.ACT (INT, INT, VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...
The function reference is as follows (where I1 is an
INTEGER column, and VC1 is a VARCHAR column and C1 is a CHAR column):
SELECT ... ACT(I1, VC1, C1) ...
Assume
that the application making this reference has an SQL path established
as:
"CAESAR"
Following through the algorithm:
- Each of the candidate functions is evaluated to determine if the
data type of each input argument of the function invocation matches
or is promotable to the data type of the corresponding parameter of
the function instance:
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, all candidate functions are eliminated
since VARCHAR is not promotable to INTEGER, so no candidate functions
remain.
- Since the subset of candidate functions from above is empty,
the candidate functions are considered using the castable process
- For the first argument, all the candidate functions have an exact
match with the parameter type.
- For the second argument, none of the candidate functions have
a parameter to which the corresponding argument can be promoted, so
no candidate functions are eliminated.
- Since the third argument can be promoted to the parameter of ACT_1,
but not to the parameters of ACT_2 or ACT_3, ACT_1 is the best fit.