SUM() or AVG() function returns an error in Data Virtualization

Last updated: Nov 26, 2024

You might encounter an SQL0802N error when you use the SUM() or AVG() function in certain SQL statements.

Symptoms

You use the SUM() or AVG() function in your SQL statements. Certain SQL statement might fail with the following error message:

SQL0802N Arithmetic overflow or other arithmetic exception occurred.

Causes

An arithmetic overflow operation might occur when you run a SUM() or AVG() function if the result or an intermediate type is not large enough to handle the amount of processed data. For example, an arithmetic overflow might occur during the execution of SUM() of an integer column if the amount of summed data exceeds the maximum size of an integer.

Depending on where the SUM() or AVG() is used in the query, the error can also occur if a plan change exists that causes more data to be processed by the function, and the result or intermediate result is too large.

Resolving the problem

To resolve these issues, choose one of the following methods based on the cause and the data types of the function arguments.

Change the data type of the argument that is being referenced in the function or add a CAST specification. For example, you can change the data type of the argument from an INT to a BIGINT (or DECIMAL), or you can cast the argument of the function to a BIGINT (or DECIMAL). If the data type of the argument is DECIMAL, a cast might be needed to adjust the precision and scale of the data to prevent overflow.
Note: Adding a CAST might change the access plan or the performance of the query.
Disable the early aggregation for all queries by changing the DB2_DV_OVERRIDES registry variable. A Platform administrator must run the following command from the c-dv2u-cv-db2u-0 pod.
```
db2set | grep DB2_DV_OVERRIDES; 
db2set -im DB2_DV_OVERRIDES="Existing_values, NO_EAGGB";
db2 flush package cache dynamic; 
```

Disable early aggregation for this query only by adding the NO_EAGGB setting as an optimizer hint as shown in the following example.

select .... 
         SUM() .... 
          /* <OPTGUIDELINES>
                  <REGISTRY>
                  <OPTION NAME='DB2_DV_OVERRIDES'   VALUE='Existing_values,NO_EAGGB'/>
                  </REGISTRY>
             </OPTGUIDELINES> */

For more information, see the following resources:
- AVG aggregate function
- SUM aggregate function