I invoke the SQL statement of "insert into session.temp_tab select sdate,CDWBH,IHYLB1,sum(IFDL),sum(IGDL),sum(IPDL),s um(IDLHJ) from im.dl_origin_a group by cube(sdate,CDWBH,IHYLB1)" in my SQL procedure. DB2 returned the -802(SQLCODE) code.
The structure of im.dl_origin_a is
$ db2 describe table im.dl_origin_a
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- -----
SDATE SYSIBM CHARACTER 7 0 No
CDWBH SYSIBM CHARACTER 6 0 No
IHYLB1 SYSIBM CHARACTER 3 0 No
IFDL SYSIBM INTEGER 4 0 Yes
IGDL SYSIBM INTEGER 4 0 Yes
IPDL SYSIBM INTEGER 4 0 Yes
IDLHJ SYSIBM INTEGER 4 0 Yes
The table session.temp_tab is the same as im.dl_origin_a. After I change the type of IFDL...IDLHJ to bigint I invoke the SQL statement. DB2 return the -802 code again.
Please tell me how to resolve this problem.
It looks like there's an arithmetic overflow on one of the functions used. Make sure the target columns can support the values resulting from the functions which are being used in the select statement.
Here's how to check out these returns.
db2 "? sql0802n"
SQL0802N Arithmetic overflow or other arithmetic exception
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
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.