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 functions provided with the database
manager. Built-in functions include aggregate functions (for example,
AVG), operator functions (for example, +), casting functions (for
example, DECIMAL), scalar functions (for example, CEILING), and table
functions (for example, BASE_TABLE). Built-in functions are generally
defined in schemas that begin with 'SYS' (for example, SYSIBM, SYSFUN,
and SYSIBMADM) although some are also defined in schemas that begin
with 'DB2' (for example DB2MQ).
- 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". 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 scalar, aggregate, row, or table function, 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 syntax shown previously, 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 in the SYSIBM and SYSFUN
schemas 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 function has a matching name and applicable number of parameters.
An applicable number of parameters satisfies the condition N ≤ A ≤ P.
- Each
candidate 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.
- In a Unicode database, a numeric or datetime data
type can be cast to a character or graphic string data type, except
for LOBs
- In a Unicode database, a character or graphic string
type, except LOBs, can be cast to a numeric or datetime data type
- In a non-Unicode database, a numeric or datetime
data type can be cast to a character string data type, except for
LOBs
- In a non-Unicode database, a character 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). In a Unicode database, when function resolution
selects a built-in function from the SYSIBM schema 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 resolution| Data 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 lowercase types in the previous table 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 from the SYSIBM schema 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
Function
resolution applies to all functions, including schema functions and
modules functions that are built-in or user-defined. If a function
is invoked without its schema name, the SQL path is used to resolve
the function invocation to a specific function.
The
built-in functions in the SYSIBM schema are always considered during
function resolution, even when SYSIBM is not explicitly included in
the SQL path. 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 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 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 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.
DB2 Version 10.1 for Linux, UNIX, and Windows