IBM Support

Division considerations using decimals

Technote (FAQ)


Question

When dividing using decimal type or using the decimal built-in function, you may have a result that does not have the desired scale.

Answer

In DB2 Universal Database, expressions involving arithmetic division with the decimal data type may lead to incorrect results or results not having the desired precision after the decimal point. To understand this, consider the following select statement where the denominator of the divisions is the same in each expression:

select dec(dec(1.234,12,3) / ((100+dec(0.00,5,2))/100),5,3) as prec_12,
      dec(dec(1.234,13,3) / ((100+dec(0.00,5,2))/100),5,3) as prec_13,
      dec(dec(1.234,14,3) / ((100+dec(0.00,5,2))/100),5,3) as prec_14,
      dec(dec(1.234,15,3) / ((100+dec(0.00,5,2))/100),5,3) as prec_15
from sysibm.sysdummy1



These expressions basically divide the number 1.234 by 1. In the above expression examples, we added the complexity to the denominator to illustrate how it will be processed and is by no means the most efficient way of doing things. The result from the example select statement is the folowing:

PREC_12 PREC_13 PREC_14 PREC_15
------- ------- ------- -------
1.234   1.230   1.200   1.000
                               
1 record(s) selected.



In the absence of a specified precision and scale, consider the following:

select ((100+dec(0.00,5,2)) / 100) as denominator_scale_19
from sysibm.sysdummy1

DENOMINATOR_SCALE_19
---------------------------------
1.0000000000000000000

1 record(s) selected.



You can see that to resolve the denominator, DB2 has used a decimal(31,19) in the absence of a specified precision and scale. The resulting scale of expressions using divisions with decimal type is determined by the following formula:


31 - Numerator Precision + Numerator Scale - Denominator Scale




This formula is documented along with the “SQL0419N A decimal divide operation is not valid because the result would have a negative scale.” error. If we take the expressions in the above select, we have the following result scale calculation:

     NP  NS   DS    Result Scale
31 - 12 + 3 - 19 =      3
31 - 13 + 3 - 19 =      2
31 - 14 + 3 - 19 =      1
31 - 15 + 3 - 19 =      0


If we would have used a numerator of dec(1.234,16,3) in the expression using the same denominator, it would have failled with an SQL0419N error because the resulting scale calculation would yield a value of –1 as illustrated below:




So in light of this, the best practice to avoid error is to explicitly specify the precision and scale of all decimal expression results


dec(dec(1.234,15,3) / ( dec(((100+dec(0.00,5,2))/100),5,3)),5,3)



In DB2 Universal Database version 8, MIN_DEC_DIV_3, a new database configuration parameter has been introduced to help regarding this issue.

Related information

Decimal division scale to 3 configuration parameter

Document information

More support for: DB2 for Linux, UNIX and Windows
Routines (SP & UDF) - Internal Routines

Software version: 9.1, 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s): Platform Independent

Software edition: Edition Independent

Reference #: 1214415

Modified date: 12 June 2014


Translate this page: