SQLCODE -802: Arithmetic overflow or divide by zero

What it means

Db2 does the arithmetic in SELECT lists, WHERE clauses, SET clauses and aggregate functions itself. When a calculation can't produce a valid result, the statement fails with -802. The message has four tokens, though some may be missing depending on where the error happened.

• The exception type is what went wrong, such as FIXED POINT OVERFLOW, DECIMAL OVERFLOW, ZERO DIVIDE, DIVIDE EXCEPTION or EXPONENT OVERFLOW.
• The operation is ADDITION, SUBTRACTION, MULTIPLICATION, DIVISION, NEGATION or a function.
• The data type is BIGINT, INTEGER, SMALLINT, DECIMAL or FLOAT. It can be the type of an internal working value, so it may not match your column.
• The position is only set when the error is in the SELECT list of the outer SELECT. It counts expressions from the left.

For INSERT and UPDATE, nothing is changed. If it happens during a FETCH, the cursor stays open, unless the error was in an aggregate function. Then Db2 closes the cursor, and a later FETCH gets -501.

If the failing expression is in the outer SELECT list and you supply an indicator variable for it, Db2 doesn't fail the row. It returns warning +802, sets the indicator to -2 and gives you the values that did work.

Common causes

• Division by zero, typically a rate or average where the divisor column is zero for some rows.
• SUM or a multiplication on INTEGER columns that goes past about 2.1 billion.
• Decimal division where the quotient is too big for the result's precision, reported as DIVIDE EXCEPTION.
• A decimal result that loses non-zero digits because the result type is too short, reported as DECIMAL OVERFLOW.
• A built-in function given an argument outside its range.
• A Java stored procedure or function returns a decimal output parameter whose precision or scale is too small. The operation is then Java conversion and the position is the parameter number.

How to find the problem

1. Read the exception type and the operation from the message. ZERO DIVIDE and FIXED POINT OVERFLOW point you straight at the expression to check.
2. Use the position number to pick the expression in the SELECT list.
3. Find the rows that cause it. For a divide, select the rows where the divisor is zero. For an overflow, look at the largest values in the columns involved.
4. Work out the result type of the expression. Db2's rules for DECIMAL precision and scale in multiplication and division can produce a smaller result than you expect.

How to fix it

• Guard divisions with NULLIF(divisor, 0), which returns null instead of failing, or a CASE expression that supplies a default.
• Cast to a bigger type before the arithmetic, such as SUM(BIGINT(QTY)) or CAST(AMT AS DECIMAL(31,2)).
• Add indicator variables for SELECT list expressions, so a bad row returns +802 and a null instead of stopping the program.
• For a Java routine, define the decimal output parameter with enough precision and scale.
• If the data itself is wrong, such as a zero that should never be there, fix it at the source.

What it looks like

DSNT408I SQLCODE = -802, ERROR:  EXCEPTION ERROR ZERO DIVIDE HAS OCCURRED DURING DIVISION OPERATION ON DECIMAL DATA, POSITION 3
DSNT418I SQLSTATE   = 22012 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.