Sysprocplan

Posted in 2003

Dale Gilbert — Tue, 06 May 2003 07:09:24 -0700 — source: Usenet: comp.databases.informix

<html><div style='background-color:'><DIV>We have had this problem on 7.31TC4 (Windoze) and it has been confirmed as a bug. I don't know if it exists on any/all other versions. ----- Original Message ----- From: "Bill Dare" <<A href="mailto:dareb@jevic.com">dareb@jevic.com</A>> To: <<A href="mailto:janto@luzdelsur.com.pe">janto@luzdelsur.com.pe</A>>; <<A href="mailto:informix-list@iiug.org">informix-list@iiug.org</A>> Sent: Tuesday, May 06, 2003 5:56 AM Subject: RE: sysprocplan ?? > The link below will give you a good description of the problem(IBM calls it > documented behavior) you are expieriencing: > > <A href="http://www-1.ibm.com/support/docview.wss?rs=203&context=SW000&q=sysprocplan">http://www-1.ibm.com/support/docview.wss?rs=203&context=SW000&q=sysprocplan</A>& > 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 D! are > > > -----Original Message----- > &g! t; From: <A href="mailto:janto@luzdelsur.com.pe">janto@luzdelsur.com.pe</A> [SMTP:janto@luzdelsur.com.pe] > > Sent: Monday, May 05, 2003 8:54 PM > > To: <A href="mailto:informix-list@iiug.org">informix-list@iiug.org</A> > > 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; > > &nb! sp;    end if > >   end if<B! R>> & gt; 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 > > </DIV></div> <hr>Add photos to your messages with <a href="http://g.msn.com/8HMKENUS/2749">MSN 8. </a> Get 2 months FREE*.</html>

Sysprocplan

Related threads

Your privacy choices