RE: Sysprocplan
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 >
On Tue, 06 May 2003 11:40:50 -0400, Bill Dare wrote: Here's one admittedly 'flaky' method that is safe and will do the job. Create another table containing just the key and other indexed columns from the original table that's giving you problems. You do not need the indexes. Load it with data from the original table. Run the update statistics on that table instead (if you run a HIGH or MEDIUM on the entire table that will be OK, if you use dostats or something similar have dostats output the update stats commands you want to disk (-f filename) and edit the file substituting the dummy table's name). Delete all of the records from sysdistrib that belong to the original table. Copy the sysdistrib records for the dummy table replacing the tabid with that of the original table. Finally update statistics on the stored procedures that access the original table. Flaky sounding I know but I have it on good authority that it is safe to muck around with the sysdistrib records. Art S. Kagel > 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 >>