Updated March 6, 2023
Introduction to DB2 Functions
DB2 functions are used in DB2 to perform an operation and it accepts zero or more inputs while calling it which helps in passing the data to the function while execution. These passed values are called the arguments which are closed inside the parentheses. The function is used for representing the relation and manipulation of the arguments which are input values and the output which are the resultant values.
There are many types of functions in DB2 such as a table, scalar, and aggregate which are broadly classified as built-in functions and user-defined functions. Built-in functions of DB2 can be either aggregate or scalar functions while user-defined can be a table, scalar, or column functions. In this article, we will study some of the functions of DB2 and also understand their usage and get the details of them.
Types of Functions in DB2
Let us discuss the types of functions in DB2.
OLAP functions
The specifications like ROW_NUMBER, RANK, and DENSE_RANK in online analytical processing are also called in-built functions of DB2.
MQ functions of DB2
The IBM MQ operations for messaging are also referred to as MQ functions of DB2 when used within the SQL statements. These functions are responsible for making it possible that we can integrate the database applications without IBM MQ messaging. We can combine the database access with IBM MQ by using functions that can access IBM MQ messaging from SQL statements. These functions can be either table or scalar functions.
Administrative task schedular functions:
This type of function is responsible for providing information about the status of tasks that are scheduled for running the task schedular for administration. It is capable to run the JCL jobs, stored procedures, and different tasks of administration based on the event and time-based schedule.
Aggregate functions
As the name suggests this function takes the list of the values which can be even a column name of a table containing multiple rows and return a single value out of them by aggregating it depending on which aggregation operation is being performed. The following table shows some of the aggregate functions.
| Function | Description |
| AVG | To calculate the average value from the provided set of numbers. |
| COUNT | To retrieve the number of rows in the resultset. |
| MAX | It is used to retrieve the maximum value from the given set of values. |
| MEDIAN | This function can only be used in accelerator servers and is used to retrieve the median from the given set of values. |
| SUM | The function is used to find out the total value which is formed after adding up all the specified set of values. |
| COVARIANCE | It is used to retrieve the population covariance value from the supplied set of pairs of numbers. |
| MIN | This function is used to find out the minimum value from the passed set of values. |
Row functions
The PACK and UNPACK row functions can only be used when the specially described context is used for that function. The UNPACK and PACK function is used for decoding and encoding the binary string values respectively.
Table functions
These functions are used in the from clause of the query statement. These functions give the output containing the columns of the table which are somewhat similar to the table created using the create table query statement. These functions can also be qualified with the help of the name of the schema. The following are the table functions shown in the table.
| Function | Description |
| MQREADALL | It returns the table which has metadata of the messages and the messages itself which are specified in the MQ location of IBM. This function further does not removes the messages inside the queue. |
| MQRECEIVEALL | It returns the table which has metadata of the messages and the messages itself which are specified in the MQ location of IBM. This function further removes all the messages inside the queue. |
| XMLTABLE | It returns the table which is resulted as per the evaluation of the expressions of XQuery which considers the argument values provided in the input as the variables of XQuery. |
| BLOCKING_THREADS | It returns a table that has one row that represents the claim or lock which is held by the thread in the database specified in the parameter values provided in the input. |
| ADMIN_TASK_STATUS | This function returns a table containing one row for each given task that is actually present in the list of the schedule of administration. The status shown by each row gives the details about the last time the task was run. |
| ADMIN_TASK_LIST | This function is used for retrieving the list of all the tasks which are present inside the list of the tasks of the administrative schedular. |
Scalar functions:
These functions are applicable to a single set of values and not to a set of values as that of aggregate functions. Hence, the restrictions which are there for the usage of aggregate functions do not exist when we are using the scalar functions. But when the aggregate function is used inside the scalar function then the restriction is applied over it. Consider for example that if the use of the aggregate function is allowed inside the context of the scalar function being used then this aggregate function can also be used within the scalar function. These functions can be used anywhere wherever there is an expression in the query statement. For the complete list of the scalar functions arranged in alphabetical order refer to the official link of IBM. The below table shows some of the important scalar functions in DB2.
| Function | Description |
| DAYS | It is used to get the integer representation of the specified date value. |
| FLOOR | It is used to retrieve the biggest value of the integer which is less than or equal to the specified argument value. |
| LENGTH | It returns the length of the specified value. |
| TIMESTAMP | It is helpful for retrieving the timestamp value without any of the information of the time zone for the specified value of the argument. |
For the complete list of all the built-in functions supported by IBM in DB2, you can refer to this link.
Conclusion
There are two types of functions available in DB2 which are broadly classified as user-defined functions and in-built functions. The articles introduced different types of functions available in DB2 like scalar, row, table, aggregate, and administrative schedular related functions.
Recommended Articles
This is a guide to DB2 Functions. Here we discuss Introduction, Types of Functions in DB2, and their Usage. You may also have a look at the following articles to learn more –
