Informix

 View Only
Expand all | Collapse all

how to grab the update statistics sql statements of a database

  • 1.  how to grab the update statistics sql statements of a database

    Posted Sat May 23, 2020 05:08 AM

    Hi folks

    as you know, dbexport generates the UPDATE STATISTICS sql statements of the exported database, at the end of the dbname.exp/dbname.sql file, which is obviously useful for the import phase.

    as you would have noticed, dbschema does NOT generate those UPDATE STATISTICS statements, at least with the documented options of it.

    Is there a hidden option for that, or another way to get those statements ?

    Building it with from sysdistrib table won't be that easy. dbschema -d -hd can be a starting point.

    Checking this before starting one more perl utility :-)
    thanks in advance
    Eric



    ------------------------------
    [eric] [Vercelletto] []
    [Founder]
    [kandooerp.org]
    [Pont l'Abbé] [France]
    [+33 626 52 50 68]

    Disclaimer: 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.
    ------------------------------

    #Informix


  • 2.  RE: how to grab the update statistics sql statements of a database

    Posted Sun May 24, 2020 05:48 PM
    Hello there,

    I'm interested in this topic as well.

    ------------------------------
    Paul Ilechko
    ------------------------------

    Original Message


  • 3.  RE: how to grab the update statistics sql statements of a database

    IBM Champion
    Posted Sun May 24, 2020 07:28 PM
    Eric:

    You can use myschema for this:

    art@Elbereth:~/Desktop$ myschema -d art --distributions-file=statfile >/dev/null
    art@Elbereth:~/Desktop$ head statfile 
    UPDATE STATISTICS MEDIUM FOR TABLE sysblderrorlog( order )
           SAMPLING SIZE    0.0299999993 RESOLUTION    2.0000000000    0.8000000119 DISTRIBUTIONS ONLY;
    UPDATE STATISTICS MEDIUM FOR TABLE sysblderrorlog( bld_id )
           SAMPLING SIZE    0.0299999993 RESOLUTION    2.0000000000    0.8000000119 DISTRIBUTIONS ONLY;
    UPDATE STATISTICS MEDIUM FOR TABLE sysblderrorlog( err_operation )
           SAMPLING SIZE    0.0299999993 RESOLUTION    2.0000000000    0.8000000119 DISTRIBUTIONS ONLY;
    UPDATE STATISTICS MEDIUM FOR TABLE sysblderrorlog( err_expected )
           SAMPLING SIZE    0.0299999993 RESOLUTION    2.0000000000    0.8000000119 DISTRIBUTIONS ONLY;
    UPDATE STATISTICS LOW FOR TABLE sysbldobjects (  
           create_order );
    ...

    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.






    Original Message


  • 4.  RE: how to grab the update statistics sql statements of a database

    Posted Mon May 25, 2020 03:49 AM

    I was coming to look at this solution ��
    Thanks Art!

     

    Eric Vercelletto
    KandooERP Founder and Community Manager

    www: https://kandooerp.org
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:     ericv@kandooerp.org

     




    Original Message


  • 5.  RE: how to grab the update statistics sql statements of a database

    IBM Champion
    Posted Tue May 26, 2020 12:35 AM
    Edited by System Fri January 20, 2023 04:17 PM
    Hi Eric,

    it's not a large secret. You can use my "Henri :-)" draft script to grab the things from the current database and produce the needed sql.....
    .......
    tabelle=$1
    mydbname=$2

    echo "update statistics medium for table $tabelle ("
    echo "select distinct colname[1,30]||',' from sysdistrib d,systables t,syscolumns c where t.tabid=d.tabid and c.tabid=d.tabid and c.colno=d.colno and t.tabname='$tabelle' and mode='M' order by 1"|dbaccess $mydbname
    echo ");"

    echo "update statistics high for table $tabelle ("
    echo "select distinct colname[1,30]||',' from sysdistrib d,systables t,syscolumns c where t.tabid=d.tabid and c.tabid=d.tabid and c.colno=d.colno and t.tabname='$tabelle' and mode='H' order by 1"|dbaccess $mydbname
    echo ");"


    Have fun..
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    ------------------------------

    Original Message


  • 6.  RE: how to grab the update statistics sql statements of a database

    IBM Champion
    Posted Tue May 26, 2020 09:31 AM
    Eric,

    I did a Webcast on Informix Webcast 2019 – Informix Update Statistics – Best Practices for Informix DBAs last year which has a scrip on how to get them out of the sysadmin database is you are using AUS.   

    See - https://advancedatatools.com/webcasts/informix-webcast-2019-informix-update-statistics-best-practices-for-informix-dbas/

    This will unload the commads used by AUS...

    Regards - Lester
    -- #############################################################################
    -- #############################################################################
    -- ## Module: @(#)03_aus_unload.sql.sql      2.0     Date: 11/01/2019
    -- ## Author: Lester Knutsen  Email: lester@advancedatatools.com
    -- ##         Advanced DataTools Corporation-- ## Update Statistics Webcast Sample Scripts
    -- #############################################################################
    database sysadmin;
    -- AUS Commands unload
    unload to current_low_update_statics.sql delimiter ';'
    select aus_cmd_exe from aus_cmd_listwhere aus_cmd_type in ( "l", "L" );
    unload to current_med_update_statics.sql delimiter ';'
    select aus_cmd_exe from aus_cmd_listwhere aus_cmd_type in ( "m", "M" );
    unload to current_high_update_statics.sql delimiter ';'
    select aus_cmd_exe from aus_cmd_listwhere aus_cmd_type in ( "h", "H" );

    #Informix


  • 7.  RE: how to grab the update statistics sql statements of a database

    IBM Champion
    Posted Tue May 26, 2020 09:59 AM
    Hi Eric,

    I thought I replied yesterday but do not see my response, sorry if this is a duplicate.

    You can get some of this information from Automaticated Update Statistics (AUS) in the sysadmin database,  Below is a script to generate Update statistics commands based on the data used by AUS.   I did a webcast Informix Update Statistics – Best Practices for Informix DBAs on November 21, 2019,  that is available on our website under Free Tech Infor - > Webcasts Replays.

    Regards - Lester

    -- #############################################################################
-- ## Module: @(#)03_aus_unload.sql.sql      2.0     Date: 11/01/2019
-- ## Author: Lester Knutsen  Email: lester@advancedatatools.com
-- ##         Advanced DataTools Corporation
-- ##		Update Statistics Webcast Sample Scripts
-- #############################################################################

database sysadmin;
-- AUS Commands unload
unload to current_low_update_statics.sql delimiter ';'
select aus_cmd_exe from aus_cmd_list
where aus_cmd_type in ( "l", "L" );

unload to current_med_update_statics.sql delimiter ';'
select aus_cmd_exe from aus_cmd_list
where aus_cmd_type in ( "m", "M" );

unload to current_high_update_statics.sql delimiter ';'
select aus_cmd_exe from aus_cmd_list
where aus_cmd_type in ( "h", "H" );
​


    #Informix


  • 8.  RE: how to grab the update statistics sql statements of a database

    Posted Thu May 28, 2020 09:48 AM

    Hi.


    I feel ... older than when I wrote this:

    https://www.oninitgroup.com/another-set-utilities-generate-and-run-single-stream-appropriate-update-statistics-ibm-informix

    JJ



    ------------------------------
    Jon Ritson
    ------------------------------

    Original Message


  • 9.  RE: how to grab the update statistics sql statements of a database

    Posted Fri May 29, 2020 01:05 AM

    Hello Eric,


    here is the one-liner to get statistics from dbschema -hd:

    $ dbschema -d DATABASE -hd all | nawk 'BEGIN{printf("SET PDQPRIORITY 25;\nUPDATE STATISTICS LOW DROP DISTRIBUTIONS;\n")}{if($1~/^Distribution/){split($3,T,".")};if($0~/ Mode, /){M=toupper($1);printf("UPDATE STATISTICS %s FOR TABLE %s.%s(%s) DISTRIBUTIONS ONLY;\n",M,T[1],T[2],T[3])}}'

    Cheers,

    Markus



    ------------------------------
    Markus Holzbauer
    ------------------------------

    Original Message


  • 10.  RE: how to grab the update statistics sql statements of a database

    Posted Fri May 29, 2020 02:22 AM

    and if someone prefer multi column update stats:

    dbschema -d DATABASE -hd all | nawk 'BEGIN{S="UPDATE STATISTICS %s FOR TABLE %s(%s) DISTRIBUTIONS ONLY;\n";printf("SET PDQPRIORITY 25;\nUPDATE STATISTICS LOW DROP DISTRIBUTIONS;\n")}{if($1~/^Distribution/){split($3,T,".");OT=T[1]"."T[2];C=C","T[3]};if($0~/ Mode, /){M=toupper($1);if(NR>1&&(OT!=OT_SAV||M!=M_SAV)){sub(/^,/,"",C);printf(S,M_SAV,OT_SAV,C_SAV);C=T[3]};OT_SAV=OT;M_SAV=M;C_SAV=C}}END{printf(S,M_SAV,OT_SAV,C_SAV)}'

    Cheers,

    Markus



    ------------------------------
    Markus Holzbauer
    ------------------------------

    Original Message


  • 11.  RE: how to grab the update statistics sql statements of a database

    Posted Fri May 29, 2020 02:59 AM

    OOps - bugfix:

    $ dbschema -d $DATABASE -hd all | nawk 'BEGIN{S="UPDATE STATISTICS %s FOR TABLE %s(%s) DISTRIBUTIONS ONLY;\n";printf("SET PDQPRIORITY 25;\nUPDATE STATISTICS LOW DROP DISTRIBUTIONS;\n")}{if($1~/^Distribution/){split($3,T,".");OT=T[1]"."T[2];C=C","T[3]};if($0~/ Mode, /){M=toupper($1);if(NR>1&&(OT!=OT_SAV||M!=M_SAV)){sub(/^,/,"",C);if(OT_SAV!=""){printf(S,M_SAV,OT_SAV,C_SAV)};C=T[3]};OT_SAV=OT;M_SAV=M;C_SAV=C}}END{printf(S
    ,M_SAV,OT_SAV,C_SAV)}'

    Cheers,

    Markus



    ------------------------------
    Markus Holzbauer
    ------------------------------

    Original Message


  • 12.  RE: how to grab the update statistics sql statements of a database

    Posted Fri May 29, 2020 03:10 AM

    Thanks Markus, this one is neat ��

    Beautiful one liner !

     

    And thank you for the other solutions too ( Lester, Art, Doug ) I have been rude not to thank you, but a bit overload here. Thanks all!

     




    Original Message