There are three basic types of built-in functions available to DB2 users:
- aggregate functions,
- scalar functions, and
- table functions.
It is important
that you understand what functions are available – as well as what they do – in
order to simplify program development and data access. Functions are invoked simply
by specifying the function name and any required operands. A built-in function
can be used any place an expression can be used (with some exceptions).
The first type of
function is called an aggregate function, or
sometimes a column function.
Aggregate functions can be used to
compute, from a group of rows, a single value for a designated column or
expression. This provides the capability to aggregate data, thereby enabling
you to perform statistical calculations across many rows with one SQL
statement.
An aggregate function can be
specified in a WHERE clause only if that clause is part of a subquery of a HAVING clause. In addition, every
column name specified in the expression of the aggregate function must be a
correlated reference to the same group.
The aggregate functions are AVG, CORRELATION, COUNT, COUNT_BIG, COVARIANCE, COVARIANCE_SAMP, MAX, MIN, STDDEV, SUM, VARIANCE, and XMLAGG.
I won’t be explaining what
each of these functions does in today’s blog entry; instead, I suggest that you
review their functionality in the SQL Reference Guide (SC19-4066 for DB2 11 for
z/OS).
The second type of
function is the scalar function, which is probably the most commonly used type
of function. However, there are so many scalar functions that it can be
difficult to keep track of them all. A scalar function is applied to a column or expression and operates on a single value.
Contrast this with the aggregate functions, which are applied to a set of data
and return only a single result.
The result of a scalar
function is a transformed version of the column or expression being operated
on. The transformation of the value is based on the scalar function being
applied and the value itself. For example, you might use the CHAR function to
convert a DB2 date, time, timestamp, ROWID, floating point, integer, or decimal
value to a character value… or you might use the DIGITS function to convert a
number to a character string of digits.
Of course, these are just a
few simple examples of the many functions available in DB2 for z/OS. There are well
over 100 scalar functions, each of which may be useful to minimize the amount
of code you write by making DB2 do it, instead!
With so many functions
available it would not make sense to cover them all here. Suffice it to say, by
acquiring a sound understanding of DB2’s functions many requirements that seem to
be difficult at first glance, are achievable using nothing more than SQL (check
out this example to clarify what I mean: Sorting Out a Solution in DB2).
The third type of function
supported by DB2 is the table function, which can be specified only in the FROM clause of a query. This is
because table functions return columns and resemble a table. There are three basic table functions:
- ADMIN_TASK_LIST: Returns a table with one row for each defined task in the administrative scheduler task list. The administrative scheduler is covered in Part VI, “DB2 Utilities and and Commands.”
- ADMIN_TASK_OUTPUT: For an execution of a stored procedure, returns the output parameter values and result sets, if available.
- ADMIN_TASK_STATUS: Returns a table with one row for each defined task in the administrative scheduler task list, showing the status of the task the last time it was run.
Additional Useful Function Information
DB2 also offers some other types of built-in functions including:
- MQSeries Built-In Functions - scalar and table functions for use with IBM’s message queuing software, MQSeries. These functions enable MQSeries messages to be received and sent.
- XML Built-In Functions - scalar functions to allow applications to efficiently generate and manipulate XML data with DB2.
The final built-in function to be aware of is the
RAISE_ERROR
Function. It is used to raise an error
condition in the SQLCA. The user supplies the SQLSTATE and error description for the error to be raised. The error will
be raised with the specified SQLSTATE and a SQLCODE of –438. The RAISE_ERROR function can be used to signal application program and data
problems.
What About User-Defined Functions?
DB2 also supports the creation of additional functions (and data
types) to supplement the built-in function (and data types) supplied
out-of-the-box. User-defined functions (and types) give users the ability to
effectively customize DB2 to their shop requirements. The ability to customize
is potentially very powerful. It also can be quite complex and requires
detailed knowledge, additional application development skills, and
administrative dexterity.
We will take a look at UDFs in
the next blog post, so check in again next week for that!
