Troubleshooting
Problem
Db2 might return the error, "SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003" while executing sum() function in V11.1 where it does not in V10.5.
Symptom
A SQL statement failed with SQL0802N Arithmetic overflow or other arithmetic exception occurred .
And the generated optimized statement was like the following example. It was different from the optimized statement with V10.5.
Example:
Original Statement:
------------------
SELECT ...
(SELECT SUM(T1.C1) FROM T1 WHERE T1.C2 = T2.C1)
...
(SELECT SUM(T1.C1) FROM T1 WHERE T1.C2 = T2.C1)
...
(SELECT SUM(T1.C1) FROM T1 WHERE T1.C2 = T2.C1)
...
) FROM T2
Nore:
The original statement has multiple subselect. They were rewritten to the following subselect using "group by".
Optimized Statement:
-------------------
SELECT ...
(SELECT
Q11.$C0
FROM
(SELECT
SUM(Q10.C1),
Q10."C2
FROM
(SELECT
Q9.C1,
Q9.C2
FROM
T1I AS Q9
) AS Q10
GROUP BY
Q10.C1
) AS Q11
WHERE
(Q11.C1 = Q8.C2)
) AS Q12
Cause
A query optimization in V11.1 might generate the optimized statement in this way.
This optimized statement can improve execution performance since it reduces multiple executions of same subselect to once.
However, it has a possibility for getting arithmetic overflow at the execution of sum() if the sum of the column T1.C1 in table T1 for all rows of the same value of column T1.C2 will produce an arithmetic overflow.
In this optimized statement, sum is expecuted on T1.C1 on all rows for each identical T1.C2.
In the original statement, SUM(T1.C2) is done only on the rows meeting "WHERE T1.C2 = T2.C1)".
For example, the second column of T1 is defined as C2 INT.
And T1 and T2 has the following data.
T1 has (1, 100, ...),(1, 200, ...),(2, 1000, ...),(2, 2000, ...), (999, 2147483647, ...),(999, 2147436847, ...).
T2 has (1, ...),(2, ...)
If the optimized statement are generated as above way, arithmetic overflow happens on summing two 2147436847 whose result exceeds the maximum value of INT although it does not happen on the original statement since sum is not done on those two rows having C2=2147483647.
Resolving The Problem
1. Set the following registry variable for getting Query Rewriter to use the same way as V10.5 and earlier releases.
db2set DB2COMPOPT=NO_GBPRDPU
2. Change the definition of column to be summed for avoiding arithmetic overflow such with using BIGINT instead of INT.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22011256