# 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