SQLCODE -406: Derived numeric value out of range

What it means

Every numeric column has a range. SMALLINT holds up to 32,767, INTEGER up to 2,147,483,647, and DECIMAL(7,2) up to 99,999.99. When a statement stores a value it worked out itself, from arithmetic, a function, a column of another table or a subquery, Db2 checks the value against the target column. If the whole-number part doesn't fit, Db2 fails the statement with -406.

Extra digits after the decimal point are a different matter. When a value has more decimal places than the column's scale, Db2 drops the extra digits without an error. Only the part to the left of the point can cause -406.

Three nearby codes cover similar ground. A value that comes straight from a host variable and is too big for the column gives -302. An overflow during the arithmetic itself, before any assignment, gives -802. An overflow in a CAST or a conversion function gives -413.

Common causes

• An UPDATE scales a value, such as SET PRICE = PRICE * 1.25, and some rows end up larger than the column's precision allows.
• An INSERT ... SELECT stores a SUM or COUNT in a column sized for single values. Totals grow over time until one day they no longer fit.
• Data is copied from a DECIMAL(15,2) column into a DECIMAL(9,2) column, and a few rows hold large values.
• A counter kept in a control table is defined as SMALLINT or a small DECIMAL and has reached its limit.
• A join or a missing predicate produced duplicate rows, so a total is many times bigger than it should be.
• A DECIMAL(19,0) column was altered to BIGINT and holds a value outside the BIGINT range. IBM's advice is to change it back to DECIMAL(19,0) and follow the documented way to convert it.

How to find the problem

1. The z/OS message doesn't name the column. List the numeric target columns of the statement and look each one up in SYSIBM.SYSCOLUMNS. COLTYPE gives the type, LENGTH gives the precision for DECIMAL and SCALE the decimal places.
2. Run the expression as a query to see the values, for example SELECT ACCT_NO, PRICE * 1.25 FROM ... ORDER BY 2 DESC FETCH FIRST 10 ROWS ONLY.
3. Compare the largest value with the column's maximum. For DECIMAL(p,s) the largest whole number has p minus s digits.
4. If the numbers look far too big, check the join conditions and look for duplicate rows feeding the total.

How to fix it

• If the values are valid, widen the column, for example ALTER TABLE ... ALTER COLUMN TOTAL_AMT SET DATA TYPE DECIMAL(15,2). Check the effect on dependent programs and copybooks first.
• If the values are wrong, fix the SQL that produced them, such as a missing join predicate.
• Guard the calculation, for example with a CASE expression that caps the value or skips the row, and report the rows you skipped.
• Change counters and totals to INTEGER, BIGINT or a larger DECIMAL before they reach their limit.

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.