Re: Update Statistics

> 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. > > Paul Friday, does this count as non-silence on the part of Informix ;-)? > > Dave Dave, Thanks for the reply, I'm sure Paul will also thank you in his normal style :-) What you say above is basically correct!! Of course it should be it comes from Informix ;-) As far as I can tell Informix is locking the system catalog rather than the table. BUT, and this is what causes us problems, it appears to lock the table entry in the system catalog from the time you start the update stats on a table to when it completes its calculation. I can't off hand remember the complete set of problems I have faced in the past but basically during this time the table is effectively locked for many operations as other programs can't get to the system catalog info. But this doesnt seem necessary to me. Why does it have to hold the lock for the entire time? Why not read the table, calculate the new info and then take the lock and do an update transaction on the catalog? This would only hold the lock for a second rather than minutes and yes, DHL does have some BIG tables. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------