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

janto@luzdelsur.com.pe wrote: > 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 Try to commit the transaction after performing the 'update statistics for procedure'. The 'update statistics for table' will take longer and because you did the 'update statistics for procedure' first, the locks will be held on 'sysprocplan' until you commit the whole transaction. Best regards Eric -- IT-Consulting Herber WWW: http://www.herber-consulting.de Email: eric@herber-consulting.de *********************************************** Download the IFMX Database-Monitor for free at: http://www.herber-consulting.de/BusyBee ***********************************************