RE: On sysprocplan being locked

We've also come across the same problem, and also issued SET OPTIMIZATION HIGH before the CREATE PROCEDURE statement as well as the SET OPTIMIZATION LOW in the procedure called from the trigger. This certainly seems to drastically reduce the problem. Regards Simon -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Bill Dare Sent: 21 December 2005 13:49 To: owner-informix-list@iiug.org; jpierrot@chubb.com; informix-list@iiug.org Subject: 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 ********************************************************************** The information in this e-mail and any attachment is confidential. It is intended only for the named recipient(s). If you are not a named recipient please notify the sender immediately and do not disclose the contents to another person or take copies. Although Axxia Systems has taken every reasonable precaution to ensure that any attachment to this e-mail has been checked for viruses, it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Axxia Systems reserves the right and senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to axxia.com. ********************************************************************** sending to informix-list