#46 Arithmetic overflow in process_statistics

[Anonymous]

Module refreshgrid()
Gridname: process_statistics

Message :
Arithmetic overflow during explicit conversion of INT NULL .... to a NUMERIC Field
see JPG joined

Denis GOUDET

[Jean-Paul Martin]

Hello Denis,
can you execute the query with isql and see on which column you get the error and see if there is one or several rows in the database with very bad values ?
Cheers
Jpm

[Anonymous]

Hello ,
my name is Luca and I am an ASE server DBA where I found already installed Asemon v3.0.7.
Since I am experiencing the same issue Denis posted two years ago I wonder if it possibile to refresh this case. I execute the query with sql but the error was exactly the same, how to see which column cause the error.
Regards
Luca

[Jean-Paul Martin]

Hi Luca,
you can try the query under isql and retry the it by removing aggregate columns one by one up to make error disappear. You 'll find the culprit column. Tell me which column it is.

I see than in the query, 5 columns are not converted to float or large numeric :

  Trans=sum(Transactions),
  Cmits=sum(Commits),
  Rlbacks=sum(Rollbacks), 
  PktReceived=sum(PacketsReceived),
  PktSent=sum(PacketsSent),

Do you have the problem on one of these cols ?

Best regards
Jpm

[Anonymous]

Hi Jean Paul,
thank you for your answer.
I removed the aggr columns one by one aht the culprit is
PhyscalReads
by commenting this line
-- PReads=sum(convert(numeric(16,0),PhysicalReads)),

the query returns rows.
Best regards
Luca

[Anonymous]

Hello Jean-Paul I sent an answer several weeks ago but is still awaiting moderation, have you read it ?
In that post I wrote that the culprit is this statement:
PReads=sum(convert(numeric(16,0),PhysicalReads)),
Have you any suggestion ?
Best Regards
Luca

[Luca Casavola]

Hello Again, since the previous messages are awaiting moderation and the first one is in thhi state since several weeks , I registered my self on sourceforge in hoping you can see now my post.
As i told I found the culprit colum, the offending statemente is
PReads=sum(convert(numeric(16,0),PhysicalReads)),
Since you already converted it to numeric there is a way to overcome the problem ?
Regards
Luca

[Jean-Paul Martin]

Thank you for this info.
If the sum of PhysicalReads is above a numeric(16,0) (a huge number), it means that some figures in ASE MDA tables are completely wrong : sometime ASE set the number of physical IO to billions, which is impossible. In recent versions of asemon_logger I filter these wrong values ( in collector CnxActiv)
What is the asemon_logger version you use ?
You can verify if you have abnormal values for physicalreads by selecting directly in the xxxx_CnxActiv table. You can update manually these wrong values (set to zero for example) and after you 'll be able to use asemon_report
But I encourage you to use a rencent asemon_logger version

Regards
Jpm

[Luca Casavola]

Hi Jean-Paul,
in the short term I solved the problem just by increasing the convert from 16 to 19 as here indicated:
PReads=sum(convert(numeric(19,0),PhysicalReads))
In this way I was able to generate the pie charts with no other problems.
But, as you probably argue, the data on xxxx_CnxActiv table could be wrong but I wonder if by setting the value to zero I also will introduce an error or the procedure will re-calcuate the right value ?
Regards
Luca

[Jean-Paul Martin]

Hi Luca, when asemon_logger filters very high values, it resets it to zero. So you can update the wrong values to zero
Regards
Jpm