Re: Update Statistics

In article <1993Apr2.220310.23051@informix.com> davek@somerset.informix.com (Dave Kosenko) writes: } } Cathy Kipp writes: } |> Not to be difficult here.... ok, nevermind, I'm going to be difficult... } |> } |> We are about to migrate from Informix standard engine to on-line. } |> } |> When they said ON-LINE we assumed they meant ON-LINE. One of our big } |> reasons for moving is that our users won't have to be down for backups. } |> Why then would we ever want our users to have to be down for UPDATE } |> STATISTICS? } |> } |> Under standard engine, we can UPDATE STATISTICS with all users logged on } |> doing whatever they like. } } I just checked the source, and I do not see anywhere where we lock the } table during update statistics. What we *do*, and this is true for SE } as well as OnLine, is lock the system catalog entries as they are being } changed (of course we would do that - it is an update after all). Has } anyone actually *seen* a table lock on a table for which stats are being } updated? UnLess it is a mighty BIG table, the timeframe would be pretty } small regardless. } I remember while working for a company, that had 550+ tables in their Online database, we always ran into some type of problem with UPDATE STATISTICS on the whole database. Either we were running out of locks or blowing the logs away, I forget which. Whatever the problem was I was forced to write the following script to work around the it: Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip : # update_em # Run UPDATE STATISTICS on a table by table basis # DATABASE=$1 if [ -z "$DATABASE" ] then echo "usage: update_em dbname" >&2 exit 1 fi isql $DATABASE - <<EOF 2>/dev/null | isql $DATABASE - output to pipe "cat" without headings select "update statistics for table ", tabname, ";" from systables where tabid >= 100 order by tabname; EOF exit 0 Here --------- Snip Here --------- Snip Here --------- Snip Here --------- Snip DAS -- David Snyder @ Snide Computer Services - Folcroft, PA Current Release is db4glgen-3.11 UUCP: ..!uunet!das13!dave INTERNET: dave.snyder@snide.com