Informix

 View Only
  • 1.  Update Statistics

    Posted Tue March 24, 2020 08:59 PM
    Hi,
    Is there a way to identify whether 'Update Statistics' is running on a server and finding its history?

    What are the good practices to schedule update statistics?

    With the best regards and thanks,
    Saradhi Motamarri
    +61430022130
    Sent from Yahoo Mail for iPhone

    #Informix


  • 2.  RE: Update Statistics

    IBM Champion
    Posted Tue March 24, 2020 09:30 PM
    Hi,

    The answer is it depends on how you are running Update Statistics, from AUS or from and SQL script or using Art's Dostats program.  What are you trying to discover? 

    I did a Webcast a couple of months ago on this topic, the replay, and slides, are available on our website at: Informix Webcast 2019 - Informix Update Statistics - Best Practices for Informix DBAs - Advanced DataTools

    In the webcasts, I showed a number of scripts, one to tell when update statistics was run last on a database by table, the command history if run from AUS,  and other examples.

    To catch if it is running right now try onstat -g sql | grep "UPDATE STATIST" 

    If you are using AUS, to see completed commands do:
    database sysadmin;
    select * from aus_cmd_comp;

    If it says no rows found then you have not been using AUS.


    Hi,
    Is there a way to identify whether 'Update Statistics' is running on a server and finding its history?

    What are the good practices to schedule update statistics?

    With the best regards and thanks,
    Saradhi Motamarri
    Saradhi Motamarri,  Tue March 24, 2020 08:58 PM


    ------------------------------
    Lester Knutsen
    lester@advancedatatools.com
    Advanced DataTools Corporation
    Voice: 703-256-0267 x102
    Visit our Web page: http://www.advancedatatools.com
    ------------------------------



  • 3.  RE: Update Statistics

    Posted Tue March 24, 2020 10:31 PM
    Thank you so much Lester.
    Will review the material.

    With the best regards and thanks,
    Saradhi Motamarri
    +61430022130
    Sent from Yahoo Mail for iPhone





  • 4.  RE: Update Statistics

    Posted Wed March 25, 2020 03:11 AM

    Hi Saradhi,

     

    I am not aware of any built-in history of Update Statistics "process", but one indicator is the column ustlowts In systables, which tells the last date update stats low has been applied on this table.

     

    In systables, you will also see 'nrows' which is set by update statistics low, with the real count of rows at this time.

     

    IMO, IBM's AUS is a good start to have your statistics up to date, but Art kagel's 'do_stats' utility goes further than AUS, in a more accurate and efficient way.

    It is also handled by the Informix scheduler, so it has history.

     

    You can find do_stats in the IIUG Software Repository, along with bunch of excellent free utilities which I highly recommend you to take a look at.

    Here is where you'll have to take your browser

    https://sourceforge.net/projects/iiug-software-repository/

     

    It is in DBAdmin-Tools / utilis2_ak

     

    Take time to browse the repository

     

    Take care

    Eric

    Eric Vercelletto

    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org

    image001.jpg@01CDC3E9.1425CBB0

    image002.jpg@01CDC3E9.1425CBB0

    image003.jpg@01CDC3E9.1425CBB0

     

     






  • 5.  RE: Update Statistics

    Posted Thu March 26, 2020 03:00 AM
    Hi Eric,
    Thank you.

    Nice to see valuable information, digesting them.





  • 6.  RE: Update Statistics

    IBM Champion
    Posted Tue March 24, 2020 10:50 PM
    Saradhi:

    Lester's response and the presentation he mentioned are great sources and I recommend going through it all. For the benefit of anyone in a pinch to get a quick look when searching this forum down the road, here is a quick query to run in each database:

    SELECT tabname, min(constructed) as oldest, max( constructed ) as latest 
    FROM systables as st, sysdistrib as sd 
    WHERE st.tabid = sd.tabid 
    GROUP BY 1 
    ORDER by 2 ASC;

    Add a filter for "AND tabid > 99 " to eliminate system catalog tables if you only want to look at user tables.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 7.  RE: Update Statistics

    Posted Wed March 25, 2020 01:02 AM
    Thank you very much Art..