RE: On sysprocplan being locked

I always ran into this problem when my daily update statistics would run. Caused real problems with stored procedures called from triggers on very busy tables. If statistics were updated on a table which was accessed from the stored procedure, the optimizer would re-optimize the procedure and hence lock the row in sysprocplan. That lock would remain until the user did a commit/rollback of the transaction he was in. Any other user trying to run the same transaction on other data would then have to wait until the first user completed his transaction Work-around/solution was to SET OPTIMIZATION LOW when executing the stored procedure in question. In order to accomplish that from a trigger, all of my procedures called from a trigger simply execute SET OPTIMIZATION LOW and then execute the real stored procedure. Eliminates the re-optimization. Bill > -----Original Message----- > From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org] > On Behalf Of jpierrot@chubb.com > Sent: Tuesday, December 20, 2005 5:00 PM > To: informix-list@iiug.org > Subject: On sysprocplan being locked > > Guys, > This had been question of discussion in the iiug forum in many > occasions or > for years. Correct me if I am wrong, I can recall one saying that > altering > and modifying objects in the database will cause IDS to force the > reoptimization of stored procedures thereby at times causing > sysprocplan to > lock. > Does the "altering and modifying objects" apply to temp tables where > we > create and drop indexes. > In my situation, killing the sessions and update stats for the > procedures > resolve it. But I am only looking for a root cause now, trying to > narrow > down or isolate the problem to its underlying cause. For, besides > creating > tmp tables, add indexes and drop them, we are not altering and > modifying > real objects. > > Thanks! > > JP > sending to informix-list sending to informix-list