SQL0802N Arithmetic overflow or other arithmetic exception occurred for count(*) Query

While working with count function with huge data set you will get error SQL0802N  Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003. You might also receive sqlcode -802, sqlstate 22003 error in your application log. You might get confused by seeing error Arithmetic overflow or other arithmetic exception occurred. for count function.

As you can see in above screenshot, a simple select count(*) function returned SQL0802N  Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003.

Now the question is why we got this error?  Let see what is mentioned in IBM documentation for error SQL0802N.

SQL0802N Arithmetic overflow or other arithmetic exception occurred.

Explanation:

The processing of a column function or arithmetic expression has resulted in an arithmetic overflow.
The statement cannot be processed. For the INSERT, UPDATE, or DELETE statements, no inserts or updates are performed.
 
User response:

Examine the SQL statement to determine the cause of the problem. If the problem is data dependent, it is necessary to examine the data processed when the error occurred. Refer to the SQL Reference to see the valid ranges for the data types.

This error may also be returned when column functions cannot handle the value that is returned by the SQL statement. For example, issuing a SELECT COUNT statement against a table that has more rows than defined by the MAX_LONGINT_INT SQL limit will cause an arithmetic overflow error. Consider using the COUNT_BIG column function for tables with more than 2 147 483 647 rows.

Federated system users: examine the SQL statement to determine the cause of the problem. If the problem is data dependent, examine the data being processed at the data sources when the error occurred. Refer to the corresponding SQL Reference for the data source to determine the valid ranges for the data types.

As we can see in IBM documentation, if data set which is getting processed has more than 2 147 483 647 rows we will get this error and in that case we should use COUNT_BIG function instead of COUNT function.

As we can see in above screenshot query with COUNT_BIG function returned row count successfully. Limit for COUNT_BIG is 9 223 372 036 854 775 807 or I would say limit of BIG INTEGER in DB2. 

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.