Built-in functions and functions that are supplied with Db2

A function is an operation denoted by a function name followed by zero or more input values that are enclosed in parentheses. It represents a relationship between a set of input values and a set of result values. The input values to a function are called arguments. Built-in functions include operator functions such as "+", aggregate functions such as AVG, and scalar functions such as SUBSTR, whereas user-defined functions are created using the CREATE FUNCTION statement and registered to the Db2 in the catalog. Some user-defined functions are supplied with Db2.

The types of functions are aggregate, scalar, and table. A built-in function is classified as an aggregate function, scalar function, or table function. A user-defined function can be a scalar function or table function.

If a column mask is used to mask the column values in the final result table and a column mask is applied to a column that is an argument for a function, the result of the function might be different because the column mask is applied to the column before the function operation can take place. For example, applying a column mask to column SSN can change the result of the aggregate function, COUNT(DISTINCT SSN).

The DISTINCT operation is performed on the unmasked column values. The keyword DISTINCT is not an argument of the function but rather a specification of an operation that is performed before the function is applied. If DISTINCT is specified, redundant duplicate values are eliminated before column mask is applied. If the column in aggregate function with DISTINCT keyword has column mask definition references columns that are non-grouping column and not the target column of the DISTINCT operation, the result of aggregate function can vary. Db2 does not guarantee the same row is returned from a group of duplicates in each execution, thus, the values in those non-DISTINCT/non-grouping columns in the returned row could be different each time.

The OLAP specification and built-in functions

The RANK, DENSE_RANK, and ROW_NUMBER specifications are sometimes referred to as built-in functions. For more information on these specifications, see OLAP specifications.

User defined functions that are supplied with Db2

User-defined functions are functions that are created using the CREATE FUNCTION statement and registered to the Db2 in the catalog. Some user-defined functions are supplied with Db2. Some examples include the following types of supplied user-defined functions:

  • Administrative task scheduler functions, which provide information and status about the tasks that are scheduled to run using the administrative task scheduler. The administrative task scheduler provides the ability to run stored procedures, JCL jobs, and other administrative tasks according to a time or an event-based schedule. For more information, see Scheduling administrative tasks.
  • Start of changeThe BLOCKING_THREADS table function, which can help you to identify applications, activities, and Db2 resources that might be incompatible with catalog migrations and updates, so that you can take appropriate actions beforehand to minimize the possibility of a failed migration. For more information, see Identify applications that are incompatible with online migration and BLOCKING_THREADS table function.End of change
  • Db2 MQ functions, which help you integrate IBM® MQ messaging with database applications. You can use the functions to access IBM MQ messaging from within SQL statements and to combine IBM MQ messaging with Db2 database access. For more information on using IBM MQ functions, see the information on enabling IBM MQ functions in Additional steps for enabling IBM MQ user-defined functions and on programming techniques in IBM MQ with Db2.

A user-defined function resides in the schema in which it was registered. Generally supplied user-defined functions have a schema other than SYSIBM.