RE: sysprocplan ??

The link below will give you a good description of the problem(IBM calls it documented behavior) you are expieriencing: http://www-1.ibm.com/support/docview.wss?rs=203&context=SW000&q=sysprocplan&; uid=swg21079720&loc=en_US&cs=utf-8&lang=en My problem with this were with 2 stored procedures called from triggers. The triggers were on very dynamic tables, constantly updated and inserted to. Every time update statistics was run on the tables accessed by the procedures, users accessing the table with the trigger got a lock on sysprocplan and held it until the transaction was committed. Effectively, an exclusive table level lock on the table with the trigger. So, I get a user creating a manifest which takes about 3-4 minutes all in a single transaction. The other ~300 users have to wait until that is finished. The best way around the problem is to SET OPTIMIZATION LOW. Since this is not a valid statement in a trigger I had to call an intermediate stored procedure, SET OPTIMIZATION LOW there and then call the real stored procedure. I've had no problems since I made that change. Regards, Bill Dare > -----Original Message----- > From: janto@luzdelsur.com.pe [SMTP:janto@luzdelsur.com.pe] > Sent: Monday, May 05, 2003 8:54 PM > To: informix-list@iiug.org > Subject: sysprocplan ?? > > I have a problems whit stores procedures, why do I need to update > statistics for table before update statistic for procedures ? > It's my procedure: > > create procedure "sistec".tiempo_paso(vfec_entrega date, > vfec_admisible date,vtiempo_lds integer,vetapa char(1),vestado > char(1), > vfec_aprobador date,vtip_solicitud char(2)) > returning integer; > define vartiempo_paso integer; > if vestado = 'A' or vestado = 'F' then > if vtip_solicitud <> 'TD' then > if vetapa = 'E' or vetapa = 'P' then > let vartiempo_paso = vfec_entrega - vfec_admisible; > else > select vtiempo_lds - (today - vfec_admisible) into > vartiempo_paso > from dual; > end if > else > if vetapa = 'A' then > let vartiempo_paso = vfec_aprobador - vfec_admisible; > else > select vtiempo_lds - (today - vfec_admisible) into > vartiempo_paso > from dual; > end if > end if > else > let vartiempo_paso = 0; > end if > if vartiempo_paso is null then > let vartiempo_paso = 0; > end if > return vartiempo_paso; > end procedure; > > I update statistics for procedure before update statistics for table, > and > the sysprocplan table becomes locked and gives a second user an SQL > -211 error. > I'm using Informix Online 7.31FD3W1