SUMMARY
The term arithmetic overflow or simply overflow has the following meanings.
1. In a computer, the condition that occurs when a calculation produces a result that is greater in magnitude than that which a given register or storage location can store or represent.
2. In a computer, the amount by which a calculated value is greater in magnitude than that which a given register or storage location can store or represent. Note that the overflow may be placed at another location.
SYMPTOM
The Enterprise Manager dataload was completed successfully and most Enterprise Manager out of the box reports run without any problems. However, running reports that encompass a large volume of data yields the error message, “Arithmetic overflow error converting expression to data type int”, like shown bellow:
CAUSE
This error can be caused by a large volume of data being stored in the Enterprise Manager Warehouse as it is being converted to values on the report. By default, many columns in the Enterprise Manager Warehouse are stored as integers as this should meet most needs for end users. However, if the values are large enough there may be problems as seen in the table below:
bigint | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | 8 Bytes |
int | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) | 4 Bytes |
smallint | -2^15 (-32,768) to 2^15-1 (32,767) | 2 Bytes |
tinyint | 0 to 255 | 1 Byte |
In this particular case, the RP Number of Result Rows metric is simply the sum of the IS_REP_RESULT_SIZE column in the IS_REP_FACT table at the report level. The report level is project, weekday, and month. And the purpose of this metric is to count the number of result rows returned from a report execution.
In sum, this is a report that's showing the combined total number of result rows returned for all reports run for an entire 3 month period for her monitored projects at the project, weekday and month levels. So the end result is that one will receive the "arithmetic overflow" error since one is aggregating a metric on a high level of granularity.
ACTION
Convert the datatype of the is_rep_result_size from int to bigint.
This can be done by executing the following SQL:
ALTER TABLE is_rep_fact
ALTER COLUMN is_rep_result_size bigint
After this is performed, update the table structure of the IS_REP_FACT table through the warehouse catalog and update schema.
Please be sure to take a backup of the Enterprise Manager statistics database and the metadata before making these schema changes to the Enterprise Manager project.
