Re: Lock problem with sysprocplan???

Ok, I finally found the problem. It was because we were updating statistics. Each time the statistics were updated on an object that were used by a stored, on-line evaluate that it must rebuild the plan of excution. So the first program executing the stored proc. after an update statistic automatically rebuild the plan and take a lock on sysmaster.sysprocplan. The problem in my case was that this lock was never release. I don't know if it's an informix bug, but if the program was a daemon, the lock was never release on sysprocplan, so at the next update statistics and execution of the stored I get locked. Because, we need to continue being able to update statistics on the Database, I found this way to workaround the problem. First, when I update statistics on a object, I alway's update statistics on the stored procedure that depends on this object. Second, when I call a stored procedure that I don't want to be update as needed, I set optimization to low with "SET OPTIMIZATION TO LOW" before calling it. After calling it, i set optimization to high again. It's important to note that when I first created the sotred, optimization was set to high, so the plan for the stored proc is high. I just want to avoid updating the stored if statistics were updated. The same way, when I update statistics on the stored itself, optimization is set to high. Third, If you have triggers calling stored proc, you have to write a procedure to set optimization levels. So you can call the optimization to low at the beginning et to high at the end (last instruction of the trigger) hope than can help somebody else... Donald Leclerc Novertech 819-560-1222 dl@novertech.com donald.leclerc@domtar.com duck wrote in message ... >Hi! > >I actually have an error with triggers calling stored procedures. > >I just implemented a system to make an historic of production data. The >problem occurs when I try to call some stored procedures. I get lock >error -211 isam -154. This tell that I have a lock on a system table. I >looked with onstat -k, onstat -u and make some select on syslocks table to >find out that I have lock of type "X" on some of my stored procedure in the >table sysprocplan??? > >I would like to know when informix take an "X" lock on this table and why. >Can I avoid this!!! Those lock seems to stuck there, and why other >processes are also trying to lock the sysprocplan, we use dirty read!!! > >So, we are using online dynamic server 7.3, isolation dirty read with no >logging. > >It's an urgent problem, so if you could send me an e-mail at >donald.leclerc@domtar.com I would really appreciate. > >Donald Leclerc >Novertech >donald.leclerc@domtar.com >dl@novertech.com > > >