DB2 Comparing data with very large numbers

My DB2 SQL query is failing with msg: sql0802N: Arithmetic overflow or other arithmetic exception occured. SQLSTATE = 22003

Below is the code:

ALTER TABLE MYSCHEMA.MYTABLE
ADD COLUMN DATA_BUCKET VARCHAR(255);

UPDATE MYSCHEMA.MYTABLE
SET DATA_BUCKET = CASE 
WHEN DATAAMT<1000000 then 'Less than 10 lacs' 
    WHEN DATAAMT>=1000000 and DATAAMT<2500000 then 'CLASSA'
    WHEN DATAAMT>=2500000 and DATAAMT<5000000 then 'CLASSB'
    WHEN DATAAMT>=5000000 and DATAAMT<1*POWER(10, 7) then 'CLASSC'
    WHEN DATAAMT>=1*POWER(10, 7) and DATAAMT<5*POWER(10, 7) then 'CLASSD'
    WHEN DATAAMT>=5*POWER(10, 7) and DATAAMT<50*POWER(10, 7) then 'CLASSE'
    WHEN DATAAMT>=50*POWER(10, 7) and DATAAMT<100*POWER(10, 7) then 'CLASSF'
                        
    WHEN DATAAMT>=100*POWER(10, 7) and DATAAMT<500*POWER(10, 7) then 'CLASSG'
    WHEN DATAAMT>=500*POWER(10, 7) and DATAAMT<1000*POWER(10, 7) then 'CLASSH'
COMMIT;

I checked my query contains case statement where i am comparing column value with very large numbers. It's failing from number 500 * POWER(10, 7). How can i handle this. I have tried to cast it to double like CAST(500 * POWER(10, 7) as DOUBLE) OR CONVERT but it's not working.

Solution

POWER returns INTEGER if both arguments are INTEGER or SMALLINT, whereas the result may not fit into that. The problem can be demonstrated with:

db2 "values POWER(10, 10)"

1          
-----------
SQL0802N  Arithmetic overflow or other arithmetic exception occurred.  
SQLSTATE=22003

db2 "describe values POWER(10, 10)"

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
496   INTEGER                   4  1                                    1

If you cast one of the arguments to BIGINT the result is also casted to BIGINT:

db2 "values POWER(bigint(10), 10)"

1                   
--------------------
         10000000000

1 record(s) selected.

Your problem origins from multiplying an INTEGER with an INTEGER resulting in an INTEGER. You mention 500, but you will face the same problem starting at 215:

db2 "values 214*POWER(10, 7)"

1          
-----------
 2140000000

db2 "values 215*POWER(10, 7)"

1          
-----------
SQL0802N  Arithmetic overflow or other arithmetic exception occurred.  
SQLSTATE=22003

You can avoid it by casting one of the arguments with bigint:

db2 "values bigint(215)*POWER(10, 7), 215*POWER(10, bigint(7)), 215*POWER(bigint(10), 7)"

1                   
--------------------
          2150000000
          2150000000
          2150000000

  3 record(s) selected.

I can see the value (clarity) of using POWER(...) in your CASE expression, but for efficiency reasons, you may consider evaluating it once outside of the SQL.