dostats questions

Hi Folks, could someone please give me a hint about the following: I have created a table called asciitab (char char(1), ascii smallint) and two unique indexes on column (char) and (ascii). Additionatly a unique constraint for (char, ascii) and a referential constraint on (ascii, char) (I know that this is some sort of overkill) When running dostats it gives the following output: UPDATE STATISTICS MEDIUM FOR TABLE asciitab DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE asciitab (ascii); UPDATE STATISTICS HIGH FOR TABLE asciitab ( char ) DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE asciitab (ascii, char); UPDATE STATISTICS LOW FOR TABLE asciitab (char); UPDATE STATISTICS LOW FOR TABLE asciitab (char, ascii); The question is, why do the two UPDATE STATISTICS HIGH differ? Which one is the correct one? When dropping the indexes, it seems to work well. Another thing I noticed when I was running the SQL-Statements that were generated by dostats, was that sometimes when watching with onstat -g sql an ISAM error -111 showed up. The SQL Error said 0. I regularly run oncheck -cDI and nothing seems broken. So what about the error codes. Is that something I should worry about? This phenomen does show up on IDS 7.23UC6 on AT&T Unix as on IDS 7.30UC10-1 on Linux. Thanks for any comments. -- Roland Wintgen (Systemadministrator) ### # # ### # # # # # EVG Martens GmbH & Co. KG Tel. : +49 2166/550823 # ### # # ### # Trompeterallee 244-246 Fax : +49 2166/550890 # # # # # D-41189 Moenchengladbach e-mail: rw@evg.de ### # ###

Roland Wintgen wrote: > > Hi Folks, > > could someone please give me a hint about the following: > I have created a table called asciitab (char char(1), ascii smallint) and > two unique indexes on column (char) and (ascii). Additionatly a unique > constraint for (char, ascii) and a referential constraint on (ascii, char) > (I know that this is some sort of overkill) > When running dostats it gives the following output: > > UPDATE STATISTICS MEDIUM FOR TABLE asciitab DISTRIBUTIONS ONLY; > UPDATE STATISTICS HIGH FOR TABLE asciitab (ascii); > UPDATE STATISTICS HIGH FOR TABLE asciitab ( char ) DISTRIBUTIONS ONLY; > UPDATE STATISTICS LOW FOR TABLE asciitab (ascii, char); > UPDATE STATISTICS LOW FOR TABLE asciitab (char); > UPDATE STATISTICS LOW FOR TABLE asciitab (char, ascii);> > The question is, why do the two UPDATE STATISTICS HIGH differ? Which one > is the correct one? When dropping the indexes, it seems to work well. OK, dostats tries very hard to do the minimum of work it can get away with doing and to do what it has to do as fast as possible. It turns out that UPDATE STATISTICS HIGH also does LOW unless you include the DISTRIBUTIONS ONLY. When the statement for column ascii is decided on the current index is on ascii only so running without the D-O clause will calculate both HIGH and LOW on that index at the same time. Next it looks at the index on (ascii, char) and sees that it already did HIGH on ascii but not on char yet. However, it knows that it must do a LOW on the entire index key and that will calculate the values for colmin and colmax for char also so it runs the HIGH for char with the D-O clause included. This is the minimum work, however, on SOME systems (actually I only got one report) including the D-O clause for ascii and then also doing the LOW may be faster than the combined statement (I suspect it happens on systems short on memory) so I have included the -F option which disables this optimization and will output the same statements for ascii as for char. Try both ways on your system and see which is faster. For me it is the default whenever I test it. > Another thing I noticed when I was running the SQL-Statements that were > generated by dostats, was that sometimes when watching with onstat -g sql > an ISAM error -111 showed up. The SQL Error said 0. > I regularly run oncheck -cDI and nothing seems broken. So what about > the error codes. Is that something I should worry about? > This phenomen does show up on IDS 7.23UC6 on AT&T Unix as on IDS 7.30UC10-1 > on Linux. Spurious error which happens during update statistics somtimes. Informix has never been able to explain better than 'Just ignore it, it worked didn't it?". I suspect that it is caused by IDS's attempt to cleanup the old records in the sysdistrib table if there are none. Art S. Kagel > Thanks for any comments. > > -- > Roland Wintgen (Systemadministrator) ### # # ### > # # # # # > EVG Martens GmbH & Co. KG Tel. : +49 2166/550823 # ### # # ### # > Trompeterallee 244-246 Fax : +49 2166/550890 # # # # # > D-41189 Moenchengladbach e-mail: rw@evg.de ### # ###