Question & Answer
Question
With arithmetic divisions that use decimal type or that uses the decimal built-in function, you can have a result that does not have the wanted scale.
Answer
In DB2 Universal Database, expressions involving arithmetic division with the decimal data type could 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. These 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 following:
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
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m0z000000XaqtAAC","label":"Query behaviour"},{"code":"a8m0z0000001gNyAAI","label":"Runtime"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
13 October 2023
UID
swg21214415