Re: Sysprocplan

Sounds as if IBM techs are reading Bill Gate's book: the section about how many Micro$oft engineers does it take to change a light bulb. Answer: None. They merely define "dark" as the default operating state. Bill Dare wrote: > I went round and round with IBM/Informix tech support on this issue. They > insist that this is not a defect, but "documented behavior". The > "documented behavior" turned up when I upgraded from release 7.31.UC6 to > 7.31.UD5. I have verified that the "documented behavior" still exists in > release 9.3. I think that is a load of crap, but that's the way it goes. > They are IBM and if they say the world is flat, then it is most definitely > flat. So, if you don't like this "documented behavior", you should learn to > like it. > > If you were told this is a confirmed bug, did you get a defect number? > > Regards, > Bill > > > > > >>-----Original Message----- >>From: Dale Gilbert [SMTP:gilbert_dale@hotmail.com] >>Sent: Tuesday, May 06, 2003 10:09 AM >>To: informix-list@iiug.org >>Subject: Sysprocplan >> >>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" < dareb@jevic.com <mailto:dareb@jevic.com>> >>To: < janto@luzdelsur.com.pe <mailto:janto@luzdelsur.com.pe>>; < >>informix-list@iiug.org <mailto:informix-list@iiug.org>> >>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: >>> >>> >> >><http://www-1.ibm.com/support/docview.wss?rs=203&context=SW000&q=sysprocpl >>an>& >> >>>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 >> > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'

> >>>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. So update stats on all tables then update stats on all procedures after the table. How many procedures do you have? Surely each one only takes a few seconds? There might even be a way to work out the procedures which depend on each table!