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:
Sent: 5/29/2020 2:59:00 AM
From: Markus Holzbauer
Subject: RE: how to grab the update statistics sql statements of a database
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:
Sent: Fri May 29, 2020 02:22 AM
From: Markus Holzbauer
Subject: how to grab the update statistics sql statements of a database
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:
Sent: Fri May 29, 2020 01:04 AM
From: Markus Holzbauer
Subject: how to grab the update statistics sql statements of a database
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:
Sent: Sat May 23, 2020 05:08 AM
From: Eric Vercelletto
Subject: 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