how to grab the update statistics sql statements of a database

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

Hello there, I'm interested in this topic as well. ------------------------------ Paul Ilechko ------------------------------

Eric: You can use myschema for this: art@Elbereth : ~/Desktop $ myschema -d art --distributions-file=statfile >/dev/nullart@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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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

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 ------------------------------

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

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

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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!