DB2 numeric functions: ABS, CEIL, FLOOR, ROUND and friends

Shared rules

These functions are scalar: one input row’s arguments produce one result. If any argument is null, the result is null. A character or graphic string argument is typically implicitly cast to DECFLOAT(34) before the math runs, so '12.5' can be FLOOR'd, but 'N/A' fails conversion.

Result types usually match the input numeric type, with documented exceptions (FLOOR of DECIMAL uses scale 0; some mixed expressions promote to DECFLOAT or DOUBLE). Overflow and invalid operations raise SQL errors for BINARY INTEGER / DECIMAL / FLOAT; DECFLOAT can yield special values (NaN, infinity) instead of always failing.

Do not confuse these with conversion functions named INTEGER, DECIMAL, or DOUBLE — those change type. ROUND(salary, 0) is still the original type with a rounded value. INTEGER(salary) is a 4-byte integer, truncating toward zero.

ABS (ABSVAL) and SIGN

ABS(numeric-expression) returns the absolute value: negative numbers lose their sign, zero and positives stay as they are. ABSVAL is a synonym. The result has the same type (and for DECIMAL, the same precision and scale) as the argument.

1
2
3
4
5
6
SELECT ABS(-18.7) AS A1,
       ABS(0) AS A0,
       SIGN(-18.7) AS SNEG,
       SIGN(0) AS SZER,
       SIGN(18.7) AS SPOS
  FROM SYSIBM.SYSDUMMY1;

SIGN returns −1, 0, or +1 according to the sign of the argument. It is the cheap way to implement “direction” in report SQL (for example SIGN(actual - budget) as over/under) without a three-way CASE. For DECFLOAT, signed zeros and NaN have extra rules in the SQL Reference; if you live in DECFLOAT, read SIGN’s DECFLOAT notes before assuming ordinary −1/0/+1.

CEILING, FLOOR, ROUND and TRUNCATE

These four all “remove fractional noise,” but they aim at different integers.

CEILING or CEIL

Smallest integer value greater than or equal to the argument. CEILING(3.1) is 4. CEILING(−3.1) is −3. Positive numbers go up; negative numbers go toward zero when the fraction is nonzero? No: −3.1 toward +infinity is −3. Remember “toward +infinity” and the table above.

FLOOR

Largest integer value less than or equal to the argument. FLOOR(3.1) is 3. FLOOR(−3.1) is −4. IBM’s sample on DSN8C10.EMP: FLOOR(MAX(SALARY)/12) walks Christine Haas’s 52750 annual salary down to 4395 from 4395.83.

1
2
3
SELECT FLOOR(3.5), FLOOR(3.1), FLOOR(-3.1), FLOOR(-3.5)
  FROM SYSIBM.SYSDUMMY1;
-- 3, 3, -4, -4

TRUNCATE or TRUNC

TRUNCATE(numeric-expression-1, numeric-expression-2) chops digits without rounding. The second argument defaults to 0. Positive 2 means keep two digits to the right of the decimal. Negative 2 means chop to hundreds (two places to the left). Truncation is toward zero: TRUNCATE(−1.9, 0) is −1, unlike FLOOR(−1.9) which is −2.

ROUND

ROUND(numeric-expression-1, numeric-expression-2) rounds to a number of places. The second argument defaults to 0. Behaviour is ROUND_HALF_UP: a 5 rounds away from zero. ROUND does not use the DECFLOAT ROUNDING MODE special register. If you need that register’s mode (half-even, down, ceiling, and the rest), use QUANTIZE on DECFLOAT values.

1
2
3
4
5
SELECT ROUND(748.58, 0)  AS R0,    -- 749
       ROUND(748.58, 1)  AS R1,    -- 748.6
       ROUND(748.58, -2) AS RNEG,  -- 700
       TRUNCATE(748.58, 0) AS T0   -- 748
  FROM SYSIBM.SYSDUMMY1;

Negative places are the usual way to round money to thousands for executive summaries. Document the second argument in comments; a missing second argument silently means 0 and will change a DECIMAL(9,2) salary into a whole-dollar figure in the result value (type may still show scale, depending on type rules — check the result in SPUFI).

MOD — remainder

MOD(numeric-expression-1, numeric-expression-2) returns the remainder of the first argument divided by the second. If the second argument is zero, the operation is invalid. For integers, the result is integer. For DECIMAL, scale rules follow the reference (the result scale relates to the first argument).

1
2
3
4
SELECT MOD(9, 4) AS M1,     -- 1
       MOD(9, -4) AS M2,
       MOD(YEAR(CURRENT DATE), 2) AS ODD_EVEN_YEAR
  FROM SYSIBM.SYSDUMMY1;

MOD is the usual “every nth row” helper when combined with a dense key, and the usual “is this year even?” helper. It is not a substitute for bitwise AND; use BITAND for flags. Sign of the remainder follows IBM’s rules for the operand types — verify with your actual types if you port formulas from another DBMS that uses a different remainder sign convention.

POWER, SQRT, EXP, LN, LOG10

These are the algebraic and transcendental functions. Arguments that cannot convert to a numeric type fail. Domain errors (log of zero or negative, square root of negative on non-DECFLOAT) fail.

• POWER(x, y) or POW — x raised to the power y. POWER(2, 3) is 8. Fractional exponents on negative bases are not ordinary real results.
• SQRT(x) — square root. x should be ≥ 0 for integer/decimal/float.
• EXP(x) — e raised to x. Grows fast; overflow is easy with DECIMAL and FLOAT.
• LN(x) — natural logarithm (base e). x must be > 0.
• LOG10(x) — base-10 logarithm. x must be > 0. Useful for order-of- magnitude checks.

1
2
3
4
5
6
SELECT POWER(2, 10) AS KIB,
       SQRT(2) AS ROOT2,
       EXP(1) AS E_APPROX,
       LN(10) AS LN10,
       LOG10(1000) AS ORDERS
  FROM SYSIBM.SYSDUMMY1;

Trigonometric cousins (SIN, COS, TAN, and the hyperbolic set) exist too but are not in this page’s list. They take radians, not degrees; DEGREES and RADIANS convert.

RAND and RANDOM

RAND() or RANDOM() returns a DOUBLE in the interval 0 ≤ value < 1. RAND(integer) seeds the generator. The same seed in the same environment produces a repeatable sequence, which is what you want in a test case and not what you want if you thought “seed 1” meant “more random.”

1
2
3
4
SELECT RAND() AS R1,
       RAND() AS R2,
       RAND(5) AS SEEDED
  FROM SYSIBM.SYSDUMMY1;

RAND is non-deterministic. It restricts optimization and is banned in some contexts (for example certain generated columns and materialized query tables that require determinism). For a shuffle of employees, ORDER BY RAND() is a known pattern; it sorts the full result and does not scale. Prefer a keyed sample for large tables.

XMLNAMESPACES and EXPLAIN are not numeric

Topic lists sometimes park XMLNAMESPACES and “explain functions” beside ABS. They do not round numbers.

• XMLNAMESPACES — a namespace declaration used inside XML constructors such as XMLELEMENT (for example XMLNAMESPACES(DEFAULT 'http://example') or XMLNAMESPACES('http://example' AS p)). It belongs with XML functions.
• EXPLAIN — the EXPLAIN statement writes access-path rows to PLAN_TABLE (and related explain tables). Administrative table functions can expose cache and explain data. They belong with EXPLAIN and monitoring, not with SQRT.

When you need both math and XML in one query, keep the numeric functions on numeric columns and the XML constructors on XML columns. Mixing them in one expression is usually a type error.

Putting numeric functions in real SQL

1
2
3
4
5
6
7
SELECT EMPNO,
       SALARY,
       ROUND(SALARY, -3) AS SAL_THOUSANDS,
       FLOOR(SALARY / 12) AS MONTHLY_FLOOR,
       MOD(INTEGER(EMPNO), 2) AS ODD_EMPNO
  FROM DSN8C10.EMP
 WHERE ABS(SALARY - 50000) < 5000;

Wrapping SALARY in ROUND in the WHERE clause can prevent index matching on SALARY. Compute a persisted rounded column if you filter on it often. In the SELECT list, rounding is display logic and is cheap compared with a tablespace scan.

Explain It Like I'm Five

ABS takes the minus sign off a number, like turning around a backward toy car so it faces forward. FLOOR is “go down the stairs to the next whole step.” CEILING is “go up to the next whole step.” TRUNCATE snaps off the extra Lego studs without moving to a different step. ROUND looks at the leftover studs and decides whether to climb one more step; a leftover of 5 or more climbs. MOD is the leftover after you share bricks into equal piles. RAND is shaking a bag of numbered tickets and pulling one fraction between zero and one. SQRT asks “which pile size, times itself, makes this pile?”

Exercises

1. Compute CEILING, FLOOR, ROUND(...,0), and TRUNCATE(...,0) for 2.5 and −2.5. Write one sentence on each pair that disagrees.
2. Using DSN8C10.EMP, list employees whose monthly salary FLOOR(SALARY/12) is at least 4000. Compare to ROUND(SALARY/12, 0) ≥ 4000.
3. Explain what MOD(DAY(CURRENT DATE), 7) might be used for, and why DAY versus DAYOFWEEK would change the meaning.
4. Seed RAND(1) in two separate VALUES statements in one session and compare. Then call RAND() twice in one SELECT list and compare those two columns.
5. Write a CASE that uses SIGN(COMM) to label commission as none, negative (data error), or positive without using < and > comparisons on COMM (still handle NULL).