Re: anyone have a copy of dostats for 4gl?

On Thu, 29 Apr 2004 09:25:56 -0400, TBP wrote: > Hi Art, > > Could you clarify what you mean by "more complete"? > > The script I provided works on the following basis : > > "update statistics low drop distributions" > "update statistics medium for trailing columns in indexes" > "update statistics high for leading columns in indexes" Missing: update statistics LOW for the entire key for all indexes. No data distributions for columns not indexed at all. (This can cause the optimizer to make poor decisions if filters or joins are performed on non-indexed columns. Bad idea I know but users will be users.) IIRC Roland's script includes both of these. Also since you'll be doing that for any index columns anyway the HIGH on index leading columns can include DISTRIBUTIONS ONLY so the LOW values are not calculated twice for index leading columns (except for a single key index where dropping the DISTRIBUTIONS ONLY clause and not doing the LOW will usually save a bit of processing time (I say usually because some users on SCO UNIX reported that leaving out that last optimization actually made the run a bit faster hence the -F option in dostats). But this latter has nothing to do with differences between your SQL and Rolands. BTW, the performance guide suggests, and my testing has confirmed, that doing a MEDIUM against the whole table is faster than doing the individual index trailing columns separately (unless there aren't many I guess). ALso you get distributions for all non-indexed columns as a free bonus. > "update statistics for stored procedures" > > Also, I tried running Rolands against a V9.40 instance and this fails with : > > and abs(sysindexes.part16) = syscolumns.colno and exists (select si.tabid > from sysindexes si > where si.tabid = systables.tabid > and abs(si.part1) = abs(sysindexes.part1) and abs(si.part2) > = abs(sysindexes.part2) and abs(si.part3) = > abs(sysindexes.part3) and abs(si.part4) = > abs(sysindexes.part4) and abs(si.part5) = > abs(sysindexes.part5) and abs(si.part6) = > abs(sysindexes.part6) and abs(si.part7) = > abs(sysindexes.part7) and abs(si.part8) = > abs(sysindexes.part8) and abs(si.part9) = > abs(sysindexes.part9) and abs(si.part10) = > abs(sysindexes.part10) and abs(si.part11) = > abs(sysindexes.part11) and abs(si.part12) = > abs(sysindexes.part12) and abs(si.part13) = > abs(sysindexes.part13) and abs(si.part14) = > abs(sysindexes.part14) and abs(si.part15) = > abs(sysindexes.part15) and abs(si.part16) <> > abs(sysindexes.part16)) > > order by 2, 3 desc; > # ^ > # 392: System error - unexpected null pointer encountered. Most likely this has to do with non-btree or functional indexes both of which cannot be handled by reading the sysindexes VIEW. You must read sysindices and decode the varchar containing the key definitions. Art S. Kagel > TBP > Art S. Kagel wrote: > >> On Thu, 29 Apr 2004 07:55:53 -0400, TBP wrote: >> >> Roland's script is more complete than TBP's SQL. Also there is a Perl >> version of dostats someone else wrote (sorry I can't keep track of who >> wrote what anymore). As Roland says, not as many options as dostats, but >> they get the badsic job done. >> >> Art S. Kagel >> >> >>>Roland Wintgen wrote: >>> >>>>sumGirl wrote: >>>> >>>> >>>>>Hello. I was thinking I would like to try Art's dostats tool but we are a >>>>>4gl shop and do not use ESQL and I would have not idea how to compile the >>>>>version he has placed out on IIUG. I am running IDS 9.40.FC2 on AIX 5.2 >>>>>if that matter at all. >>>>> >>>>>Thanks in advance to my new favorite forum! >>>> >>>> >>>>some time ago, I wrote a little SQL-script that tries to achieve the same >>>>as Art's dostats but with pure SQL, so all you need is dbaccess. Sure my >>>>script lacks of many features Art's program has, but it can be used to >>>>perform the minimal update statistics statmenets as described in the >>>>Performance Guide. >>>>Comments and enhancements are always welcome. >>>> >>>> >>>> >>>Littler : >>> >>>unload to update_stats.sql delimiter ";">>> >>>select "update statistics low drop distributions" from systables where >>>tabid = 1 >>> >>>union ALL >>>select unique "update statistics medium for table >>>"||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,>>>systables t where i.tabid > 99 >>>and i.tabid = c.tabid >>>and i.tabid = t.tabid >>>and c.colno in ( >>>i.part2, i.part3, i.part4, i.part5, i.part6, i.part7, i.part8, >>>i.part9, >>>i.part10, i.part11, i.part12, i.part13, i.part14, i.part15, i.part16) and >>>tabtype = 'T' >>>and c.colno not in (select i1.part1 from sysindexes i1 where i1.tabid = >>>t.tabid) >>> >>>union ALL >>>select unique "update statistics high for table >>>"||t.tabname||"("||trim(c.colname)||")" from sysindexes i, syscolumns c,>>>systables t where i.tabid > 99 >>>and i.tabid = c.tabid >>>and i.tabid = t.tabid >>>and i.part1 = c.colno >>>and tabtype = 'T' >>> >>>union ALL >>>select "update statistics for procedure" from systables where tabid >>>= 1;