Arithmetic operators in DB2 SQL

Arithmetic in expressions

If an expression uses arithmetic operators, the result is the value you get by applying those operators to the operands. Operators can be written in infix form (a + b) or, for some operators, as functions ("+"(a, b)). Arithmetic applies to signed numeric types and to datetime values in the addition and subtraction cases IBM documents. USER + 2 is invalid: you cannot add an integer to a string. Distinct numeric types need sourced functions before + and * work.

If any operand is NULL, the result of the arithmetic expression is NULL. That is why salary + commission without COALESCE drops rows (or yields null results) when commission is unknown. Division by zero is an error for ordinary numeric types (decimal floating-point has its own special-value rules).

Addition (+)

Infix + adds two numeric operands, or adds a labeled duration to a datetime value (for example DATE + 1 MONTH). The result type follows numeric type combination rules: two integers yield an integer (BIGINT if either operand is BIGINT); mixing DECIMAL and INTEGER yields a decimal with computed precision and scale.

1
2
3
SELECT SALARY + COALESCE(COMM, 0) AS PAY,
       HIREDATE + 6 MONTHS AS SIX_MONTHS
  FROM HR.EMPLOYEE;

Datetime addition is not the same as adding integers to a CHAR date. Use DATE/TIMESTAMP types (or CAST) and labeled durations (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND) rather than string tricks.

Subtraction (-)

Infix - subtracts numbers, subtracts a duration from a datetime, or subtracts two datetime values to produce a duration (the exact result type depends on the operands—date minus date is a number of days in common teaching examples; check the SQL Reference for timestamp differences). Multiplication and division must not be applied to datetime values.

1
2
3
SELECT SALARY - 500,
       CURRENT DATE - HIREDATE AS DAYS_EMPLOYED
  FROM HR.EMPLOYEE;

Multiplication (*)

* multiplies numeric operands. Overflow follows the result type: two large INTEGERs can overflow INTEGER; DECIMAL overflow is a different SQLCODE. Scale of a decimal product is the sum of scales (subject to product precision limits). Use CAST to DECIMAL with an explicit scale when you care about pennies.

1
2
3
SELECT SALARY * 1.03 AS NEXT_YEAR,
       QTY * UNIT_PRICE AS LINE_AMT
  FROM ORD.LINE;

Division (/)

/ divides numeric operands. The divisor must not be zero. If both operands are integers, Db2 performs binary integer division and discards the remainder: 5 / 2 is 2, not 2.5. If you need a fractional result, CAST an operand to DECIMAL or DECFLOAT first.

1
2
VALUES 5 / 2;              -- 2 (integer division)
VALUES DECIMAL(5,5,1) / 2; -- 2.5 style decimal result

Decimal division scale is influenced by the precompiler DEC option, DECARTH, MINDVSCL, and DECDIV3 subsystem parameters, and by CURRENT PRECISION for dynamic SQL. If shop reports show unexpected rounding, those settings—not the / token—are the first place to look.

Unary plus and unary minus

Unary + (prefix plus) does not change its operand. It is rarely useful except to make a sign explicit.

Unary - reverses the sign of a nonzero, non-DECFLOAT operand. For DECFLOAT it reverses the sign of all values, including zero and special values (NaNs and infinities). If the type of A is SMALLINT, the type of -A is INTEGER (large integer). The token after a prefix operator must not begin with another plus or minus—write - (A) or -A, not -- which starts a comment in many SQL dialects or is simply invalid as a double prefix.

1
2
3
VALUES + SALARY;
VALUES - COMM;
VALUES - SMALLINT_COL;  -- result type INTEGER

Arithmetic precedence

When parentheses do not say otherwise:

• Parentheses (and dereference operations) first, left to right
• Prefix unary + and unary -
• Multiplication, division, and concatenation (* / CONCAT ||)
• Addition and subtraction

Operators at the same level run left to right. People remember school math for * vs + and then forget that CONCAT sits with * and /, so a mix of concatenation and addition should always be parenthesised for readers even when the engine is sure.

1
2
3
VALUES 2 + 3 * 4;      -- 14
VALUES (2 + 3) * 4;    -- 20
VALUES 10 - 2 - 3;     -- (10 - 2) - 3 = 5

Concatenation (CONCAT and ||)

Concatenation is not numeric arithmetic, but it is an expression operator with the same precedence band as * and /. CONCAT and || both join two compatible strings into one. Operands must be compatible strings: binary cannot concatenate with character, including CHAR FOR BIT DATA. Distinct types based on strings need a sourced CONCAT (or an overloaded "||" function).

1
2
3
4
5
SELECT FIRSTNME CONCAT ' ' CONCAT LASTNAME AS FULL_NAME
  FROM HR.EMPLOYEE;
 
-- Equivalent, but CONCAT is the portable spelling:
-- FIRSTNME || ' ' || LASTNAME

Vertical bars (or the substitute characters some national code pages use) can parse incorrectly after CCSID conversion when a statement travels between systems. IBM recommends CONCAT for that reason. If either operand is null, the concatenation result is null—not an empty string. Length of the result follows documented MIN/MAX rules (including mixed CCSID conversion expanding bytes).

Bit operations / binary operations

Db2 for z/OS does not give you C’s &, |, and ^ as SQL infix operators. Bit work is done with scalar functions that operate on the two’s complement representation of SMALLINT, INTEGER, BIGINT, or DECFLOAT (DECIMAL, REAL, and DOUBLE arguments are cast to DECFLOAT and truncated to whole numbers):

• BITAND(x, y) — bit is 1 only if both corresponding bits are 1
• BITOR(x, y) — bit is 1 unless both bits are 0 (set flags)
• BITXOR(x, y) — bit is 1 if the bits differ (toggle flags)
• BITANDNOT(x, y) — clear bits of x that are set in y (preferred over BITAND(x, BITNOT(y)))
• BITNOT(x) — invert all bits

1
2
3
4
5
6
7
8
9
-- Turn on property bit 16
UPDATE ITEM
   SET PROPERTIES = BITOR(PROPERTIES, 16)
 WHERE ITEMID = 3412;
 
-- Toggle bit 1024
UPDATE ITEM
   SET PROPERTIES = BITXOR(PROPERTIES, 1024)
 WHERE ITEMID = 3412;

Results come back as base-10 integers of a type derived from the arguments—not as binary strings. For raw byte strings use BINARY/BLOB functions and SUBSTR, not BITAND.

Putting operators to work

1
2
3
4
5
6
SELECT EMPNO,
       LASTNME CONCAT ', ' CONCAT FIRSTNME AS NAME,
       SALARY * 12 AS YEARLY,
       BITOR(FLAGS, 1) AS FLAGS_WITH_ACTIVE
  FROM HR.EMPLOYEE
 WHERE SALARY + COALESCE(COMM, 0) > 50000;

Arithmetic in the WHERE clause is an expression; the comparison > is a different operator family (next page). Keep computations in the SELECT list when you need to display them, and be aware that wrapping a column in SALARY * 12 can block index use on SALARY.

Explain It Like I'm Five

Plus, minus, times, and divide are the same buttons as on a school calculator. Times and divide happen before plus and minus unless you put up fence posts (parentheses). If a box is empty (NULL), the calculator gives up and the answer is empty too. CONCAT is taping two word-stickers into one longer sticker. Bit functions are flipping light switches on a panel of on/off flags inside a number—not taping words and not adding pocket money.

Exercises

1. Evaluate 7 + 1 * 2 and (7 + 1) * 2.
2. Rewrite FIRSTNME || LASTNAME using CONCAT and a space.
3. Explain why 1 / 2 as INTEGER columns is 0, and how to get 0.5.
4. Write BITOR to turn on hex bit 0x04 in a FLAGS INTEGER column.
5. Predict SALARY + COMM when COMM is NULL, then fix it with COALESCE.

Frequently asked questions