SYMPTOM
Strategy Enterprise Manager 9.x data load fails with an error similar to the following:
8/3/2011 10:29:08 PM Fact Migration: Transaction 9 of 14; Sequence 1 of 1.
Error executing SQL :Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Arithmetic overflow error for type int, value = -3521546262601.000000. The statement has been terminated.INSERT INTO IS_REP_FACT SELECT DISTINCT MAX(RS.RECORDTIME), MAX(SES.EM_LOAD_TS),........................................................(SELECT CHS.JOBID FROM IS_CACHE_HIT_STATS CHS WHERE CHS.CACHESESSIONID = RS.SESSIONID AND CHS.JOBID = RS.JOBID) GROUP BY RS.SESSIONID, RS.JOBID
8/3/2011 10:29:08 PM There was a failure during the process:
CAUSE
As the error message indicates, the error is because a value which exceeds the boundaries of the INT data type is attempted to be inserted into a column which is defined as an Integer.
One possible reason for this issue to occur is if there are any entries in the IS_REPORT_STATS statistics table, where the execution duration of the report job is very long. For example, a value such as '2678400166' can be seen in the EXECFINISHTIME column. The data in the EXECFINISHTIME column is recorded in milliseconds and is an offset from the start time of job execution. The above value corresponds to a duration of ~31 days. It is possible to see such large values getting recorded in the report execution statistics tables if there are no covering settings imposed for report jobs execution duration.
ACTION
For Strategy Enterprise Manager data load to proceed, entries causing the overflow error should first be identified and later updated or deleted. The following SQL can be used:
SELECT * FROM IS_REPORT_STATS WHERE EXECFINISHTIME > 2147483647
The value of 2147483647 is the maximum value an integer data type can accommodate.
Contact Strategy Technical Support with the results of this query.
