update statistics

How can i know which tables needs update statistics , Is there any query in sysmaster which can provide this information ?

It's always a good idea to post your version and platform information even when you think that it's completely irrelevant. A couple of points: - If you have Informix 11.50 or later and you have not disabled it, the Auto Update Statistics (AUS) evaluator task is calculating which tables need updated stats every day and the AUS updater is updating them later. - You can get my dostats utility which will automate all of this for you, including detecting which tables need to be updated and it can be used as a replacement for AUS if you want. IB that dostats uses less overhead than the AUS evaluator task. - If you have Panther running yet (unlikely since it was just released on Tuesday) you have other options. That all said, there is no table that says "this table and this table need updating", you have to derive the condition from other data. There are three main criteria that would indicate that a table needs updated stats: - It has not distributions. Distributions are in the sysdistrib catalog table or you can view them using dbschema -d <database> -hd <table> - The distributions are old. It is your definition of how many days old it has to be and that will even differ by table sometimes. Again the dbschema will show the date stats were generated as will the sysdistrib record. - The distributions are out-of-sync with the data. This one is harder to determine. Dostats and AUS use a calculation of the number of rows. If the count of rows in systables is different from the number of rows in the tables partition header (sysmaster:systptnhdr or sysmaster:sysactptnhdr depending on version) by a sufficient percentage that triggers new stats. Again, AUS and dostats can automate this for you and the thresholds are configurable. Dostats is contained in the package utils2_ak which you can download free from the IIUG Software Repository. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Oct 13, 2010 at 11:29 PM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > How can i know which tables needs update statistics , Is there any query in > sysmaster which can provide this information ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016361e872cce3d6a049293c565