Search code examples
sqlmathdb2overflowtemporary

db2 temp table "with ... as ()" Arithmetic overflow

I am using the following temporary table construct inside a user-defined function to calculate fibonacci numbers. Unfortunately, the temp-table construct gives me an arithmetic overflow error for high values, but I don't how to change the data types the temp table is using ...

WITH FIB_SQL(I, RES_1, RES) AS (
    SELECT 1, 0, 1 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT I+1, RES, RES+RES_1 FROM FIB_SQL WHERE I < 50
)
SELECT MAX(RES) FROM FIB_SQL;

It works for "WHERE I < 40" but not for "WHERE I < 50"

Solution

  • The values in RES and RES_1 are large enough that when you sum the 2 values it results in a value that is large than the data type will store.

    Assume the data types are currently integers, you can cast the data types to be bigint as follows:

    WITH FIB_SQL(I, RES_1, RES) AS (
    SELECT 1, cast(0 as bigint), cast(1 as bigint) FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT I+1, RES, cast(RES as bigint) + cast(RES_1 as bigint) FROM FIB_SQL WHERE I < 50
)
SELECT MAX(RES) FROM FIB_SQL;

    More info about integer (The range of large integers is -2147483648 to +2147483647) http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_integer.htm

    vs. bigint (The range of big integers is -9223372036854775808 to +9223372036854775807) http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_biginteger.htm

    EDIT: This solution using BIGINT will allow you to go up to I < 92, but I < 93 will still give the arithmetic overflow error, b/c then you are outside of the range of allowed BIGINT values.

    If you need to use values outside the range of BIGINT, then checkout the decimal and decfloat data types. http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.intro/src/tpc/db2z_numericdatatypes.dita

    Example using the decimal data type, this allows you to go up to I < 150, which returns 9969216677189303386214405760200: 

    WITH FIB_SQL(I, RES_1, RES) AS (
    SELECT 1, CAST(0.0 AS decimal(31, 0)), CAST(1 AS decimal(31, 0))
    UNION ALL
    SELECT I+1, RES, CAST(RES +  RES_1 AS decimal(31, 0)) FROM FIB_SQL WHERE I < 150
)
SELECT MAX(RES) FROM FIB_SQL 
--OPTION (MAXRECURSION 150) --in SQL Server this is needed to increase the max recursion setting, not sure if this is needed in DB2 or not.
;