SQLMentor // learn db2

Functions

Difficulty: Intermediate · ~11 min read

Overview

DB2 ships with hundreds of built-in functions in three categories:

Category What it does Example
Scalar Returns one value per row UPPER(name)
Aggregate Returns one value per group SUM(amount)
Table Returns a virtual table XMLTABLE(...)

You can also write your own user-defined functions (UDFs) in SQL PL.

Syntax

SELECT function_name(arg1, arg2)
FROM   table;

That's it — functions are called inline anywhere an expression is valid.

Examples

Example 1: String functions

SELECT
  UPPER('hello')                    AS up,         -- 'HELLO'
  LOWER('WORLD')                    AS lo,         -- 'world'
  LENGTH('DB2')                     AS len,        -- 3
  SUBSTR('database', 1, 4)          AS sub,        -- 'data'
  POSITION('a' IN 'database')       AS pos,        -- 2
  TRIM('  hi  ')                    AS trimmed,    -- 'hi'
  REPLACE('foo-bar', '-', '_')      AS rep,        -- 'foo_bar'
  'Db' || '2'                       AS concat,     -- 'Db2'
  LPAD('7', 3, '0')                 AS padded      -- '007'
FROM   sysibm.sysdummy1;

Example 2: Numeric functions

SELECT
  ROUND(3.14159, 2)        AS rounded,    -- 3.14
  CEIL(3.2)                AS ceiling,    -- 4
  FLOOR(3.9)               AS floor,      -- 3
  ABS(-15)                 AS abs,        -- 15
  MOD(10, 3)               AS modulo,     -- 1
  POWER(2, 10)             AS power,      -- 1024
  SQRT(144)                AS root,       -- 12
  SIGN(-42)                AS sign        -- -1
FROM   sysibm.sysdummy1;

Example 3: Date and time functions

SELECT
  CURRENT_DATE                              AS today,
  CURRENT_TIMESTAMP                         AS now,
  DATE('2026-01-01')                        AS new_year,
  DAYS(CURRENT_DATE) - DAYS(DATE('2026-01-01')) AS days_since_new_year,
  DAYNAME(CURRENT_DATE)                     AS day_of_week,
  MONTHS_BETWEEN(CURRENT_DATE, DATE('2026-01-01')) AS months_since,
  ADD_MONTHS(CURRENT_DATE, 3)               AS three_months_out,
  LAST_DAY(CURRENT_DATE)                    AS month_end,
  YEAR(CURRENT_DATE), MONTH(CURRENT_DATE), DAY(CURRENT_DATE)
FROM   sysibm.sysdummy1;

Example 4: NULL-handling functions

SELECT
  COALESCE(NULL, NULL, 'fallback') AS coal,     -- 'fallback'
  NVL(NULL, 'default')             AS nvl,      -- 'default'
  NULLIF('A', 'A')                 AS nul,      -- NULL
  IFNULL(NULL, 0)                  AS ifn       -- 0
FROM   sysibm.sysdummy1;

COALESCE is the SQL standard — it returns the first non-NULL argument and accepts any number of args. Use it preferentially over NVL/IFNULL.

Example 5: Aggregate functions

CREATE TABLE sales (
  region   VARCHAR(20),
  amount   DECIMAL(10,2)
);
INSERT INTO sales VALUES
  ('EU', 100), ('EU', 250), ('EU', 50),
  ('US', 400), ('US', 150),
  ('APAC', 75);

SELECT  region,
        COUNT(*)            AS n_orders,
        SUM(amount)         AS total,
        AVG(amount)         AS average,
        MIN(amount)         AS smallest,
        MAX(amount)         AS largest,
        STDDEV(amount)      AS stddev
FROM    sales
GROUP BY region
ORDER BY total DESC;

Output:

REGION  N_ORDERS  TOTAL   AVERAGE  SMALLEST  LARGEST  STDDEV
------  --------  ------  -------  --------  -------  -------
US             2  550.00   275.00    150.00   400.00  176.78
EU             3  400.00   133.33     50.00   250.00  103.28
APAC           1   75.00    75.00     75.00    75.00     -

COUNT(*) counts rows. COUNT(col) counts non-null values in that column.

Example 6: Window functions (analytic)

SELECT  region,
        amount,
        SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS running_total,
        RANK()      OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
        LAG(amount) OVER (PARTITION BY region ORDER BY amount)  AS prev_amount
FROM    sales;

Window functions compute over a "window" of rows without collapsing them. Great for running totals, rankings, period-over-period comparisons.

Example 7: CASE expression

SELECT  amount,
        CASE
          WHEN amount < 100  THEN 'small'
          WHEN amount < 300  THEN 'medium'
          ELSE                   'large'
        END AS bucket
FROM    sales;

CASE isn't strictly a function but it's used everywhere as one.

Example 8: A simple UDF

CREATE FUNCTION discounted_price(price DECIMAL(9,2), pct INTEGER)
RETURNS DECIMAL(9,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN price * (100 - pct) / 100;

SELECT discounted_price(100, 15) FROM sysibm.sysdummy1;  -- 85.00

DETERMINISTIC tells DB2 the function returns the same output for the same input — enables caching and optimization.

Notes & Tips

  • DB2 has both || and CONCAT() for string concatenation — both work, both handle NULL the same (a NULL operand makes the result NULL; use COALESCE to default).
  • For date arithmetic, DAYS(d) returns an integer (days since 0001-01-01). Subtract two DAYS() values to get the number of days between dates.
  • DB2 stores CURRENT_TIMESTAMP to microsecond precision. Use CURRENT_TIMESTAMP(0) if you only want seconds.
  • Aggregate + GROUP BY skip NULLs by default. COUNT(*) includes them; COUNT(col) doesn't.
  • Wrap a column in a function inside a WHERE and DB2 usually can't use an index on that column. Prefer WHERE col = UPPER('abc') over WHERE UPPER(col) = 'ABC' when possible.

Practice Exercises

  1. Write a query that returns each customer's email, full_name (concatenate first + space + last), and the domain of their email (everything after the @). Use SUBSTR + POSITION.
  2. Find each region's MAX(amount) - MIN(amount) (the spread) in a single query.
  3. Use window functions to add a pct_of_total column to the sales table: each row's amount as a percentage of the region's total.
  4. Write a CASE expression that classifies dates into "weekday" or "weekend" using DAYNAME(d).
  5. Define a UDF age_in_years(birth_date) that returns the integer age based on CURRENT_DATE.

Quick Quiz

Q1. What does COALESCE(a, b, c) return?

Show answer

The first non-NULL argument from left to right. If all arguments are NULL, the result is NULL. Unlike NVL (which takes only 2 args), COALESCE accepts any number of arguments — and it's the SQL standard, so it's portable across databases.

Q2. Difference between COUNT(*) and COUNT(col)?

Show answer

COUNT(*) counts every row in the group, regardless of NULLs. COUNT(col) counts only the rows where col IS NOT NULL. So COUNT(col) ≤ COUNT(*) always — and they're equal only when col has no nulls.

Q3. Why might WHERE UPPER(name) = 'ALICE' be slow?

Show answer

Wrapping a column in a function inside a WHERE usually prevents DB2 from using an index on that column. Use case-insensitive collation, store names in a normalized case, or create an index on the expression (CREATE INDEX … ON t (UPPER(name))) if the lookup pattern is fixed.

Next Up

Functions transform values per row. Views save entire queries so users can read them like tables.