Re: Update Statistics

Jim Gordon writes: { I said that update stats does not lock the table, but rather the syscat entry -davek} |> 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. You are correct, the row is retrieved with a lock placed, and this lock endures until the update is complete. I do not know for sure why this is done, but I will speculate the following: If you have been accessing the table, you will have a current version of the pertinant system catalog info already in your local memory, so you will not have to read the catalog info and thus should be able to continue accessing the data. A new user of the table will not have this data, and will be forced to wait, which, at some level, is desirable as they should receive the *new* picture of the table, which includes the new stats, i.e. they *should* be forced to wait. Note: I do not endorse this speculative reason (and, again, I am not saying that this *is* why it is done, only a possible reason - I will attempt to get a definitive answer if one exists). I think it would be better to lock only for the update. Dave