Re: dostats error

Call IBM Informix Support. They will be able to give you a definitive answer. If the internal format of the distributions has changed between 9.40 and 11.50 then your apps will not run well at all anyway. They will run as if there are no stats. Contact IBM. If they say yes you must drop distributions, then drop them, run a medium on every whole table, so that you will get reasonable performance out of your applications. Then you will have the luxury of running the full suite in the background. Note also that the utils2_ak package comes with the drive_dostats script that can run dostats for several tables in parallel. As long as you have the resources in your server to do that it can be a big winner in terms of reducing the elapsed time to get the stats done. Note that for those VERY large tables, you want to take advantage of the new SAMPLING SIZE option to MEDIUM distributions that's available in 11.50. By default, without RESOLUTION and CONFIDENCE adjusted and without SAMPLING SIZE set MEDIUM distributions sample only 2963 rows in a table no matter how many rows the table contains (that's why it seems to be so fast)! Using only RESOLUTION and CONFIDENCE you can get MEDIUM to sample up to about 12million rows by setting RESOLUTION to 0.01 and CONFIDENCE to 0.99. But at that rate it is virtually as slow as using HIGH. If you use SAMPLING SIZE you can set the number of rows sampled table by table to something reasonable and get better MEDIUM level stats without the extra runtime of a HIGH. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 14, 2009 at 10:36 AM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > We are migrating from 9.40.FC9W2 to 11.50.FC3. We have very big tables with > many indexes. I have at most the weekend to migrate an IDS instance. > > Though I experienced how fast update stats medium is update stats low and > high will last days or weeks really. It would cause very big trouble if I > had to drop the distributions. The apps here wouldn't run any more. > > Run dostats in the background on all tables seems possible and a good > solution. Where may I find answer if it is really neccessary to clean > sysdistrib? > > Regards, > Reinhard. > > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]On Behalf Of Art Kagel > Sent: Thursday, May 14, 2009 3:49 PM > To: Habichtsberg, Reinhard > Cc: Informix-List (E-Mail) > Subject: Re: dostats error > > > Depending on the version you upgraded from it may or may not be necessary > to > drop distributions. To be on the safe side, I recommend to our clients > that > one always should after an upgrade. When Oninit performs an upgrade for a > client we always drop distributions and recreate them into an empty > sysdistrib table. > > Dostats still has features that 11.50 and OAT do not incorporate in the AUS > code. > > Art S. Kagel > Oninit ( www.oninit.com <http://www.oninit.com>; ) > IIUG Board of Directors ( art@iiug.org <mailto:art@iiug.org> ) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > > > > On Thu, May 14, 2009 at 9:08 AM, Habichtsberg, Reinhard < > RHabichtsberg@arz-emmendingen.de <mailto:RHabichtsberg@arz-emmendingen.de> > > > wrote: > > > Hi Art, > > with the newest version of dostats compiled with csdk3.50.FC3 the error > doesn't appear any more. > > Thanks for the great tool, though I guess that the functions are provided > by > the server itself since IDS 11.50. > > To refresh all update stats as you recommended ealier is it enough to do > dostats over all tables or do one have to drop distributions explicitly? > > Regards, > Reinhard. > > > -----Original Message----- > From: informix-list-bounces@iiug.org <mailto: > informix-list-bounces@iiug.org> > > [mailto: informix-list-bounces@iiug.org > <mailto:informix-list-bounces@iiug.org> ]On Behalf Of Art Kagel > Sent: Thursday, May 14, 2009 1:14 PM > To: Habichtsberg, Reinhard > Cc: Informix-List (E-Mail) > Subject: Re: dostats error > > > SQLCODE -1215 is: > > > > -1215 Value too large to fit in an INTEGER. > > > The INTEGER or SERIAL data type can accept numbers with absolute values > from > 0 through 2,147,483,647 (plus or minus (2 to the 31st power) - 1). > > > > > > Version 1.137 is an older release. The current release, which is available > from the IIUG Software Repository, is 1.153 which may relieve the problem. > I looks like the number of rows in one of your tables is too large for the > integer nrows. If downloading and compiling the newer version doesn't fix > this for you, contact me directly. > > Art > > > Art S. Kagel > > Oninit ( www.oninit.com <http://www.oninit.com>; < http://www.oninit.com > <http://www.oninit.com>; > ) > IIUG Board of Directors ( art@iiug.org <mailto:art@iiug.org> <mailto: > art@iiug.org <mailto:art@iiug.org> > ) > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > > > > On Thu, May 14, 2009 at 6:16 AM, Habichtsberg, Reinhard < > > RHabichtsberg@arz-emmendingen.de <mailto:RHabichtsberg@arz-emmendingen.de> > <mailto: RHabichtsberg@arz-emmendingen.de > <mailto:RHabichtsberg@arz-emmendingen.de> > > > > wrote: > > > Hi, > > with the command dostats -d database --f file after a couple of successful > actions appears the following error: > > FAILED: Cannot FETCH table data (a1). SQLCODE = -1215, ISAM = 0. > Statement is: > SELECT a.tabid, a.partnum, a.nrows, a.tabname, a.owner,> indexkeyarray_out(b.indexkeys) AS allparts, 0 FROM > "informix".systables a, outer ("informix".sysindices b, outer > "informix".sysobjstate s) WHERE a.tabid = b.tabid AND tabtype = 'T' AND > a.tabid >= 100 AND a.tabid = s.tabid AND s.objtype = 'I' AND b.idxname = > > s.name <http://s.name>; < http://s.name <http://s.name>; > AND s.state != > 'D' ORDER BY 1, 3, 4, 5; > > > dostats: Features Version 5.10, Source Revision

Art Kagel wrote: > Call IBM Informix Support. They will be able to give you a definitive > answer. If the internal format of the distributions has changed between > 9.40 and 11.50 then your apps will not run well at all anyway. They > will run as if there are no stats. Contact IBM. If they say yes you > must drop distributions, then drop them, run a medium on every whole > table, so that you will get reasonable performance out of your > applications. Then you will have the luxury of running the full suite > in the background. Note also that the utils2_ak package comes with the > drive_dostats script that can run dostats for several tables in > parallel. As long as you have the resources in your server to do that > it can be a big winner in terms of reducing the elapsed time to get the > stats done. > > Note that for those VERY large tables, you want to take advantage of the > new SAMPLING SIZE option to MEDIUM distributions that's available in > 11.50. By default, without RESOLUTION and CONFIDENCE adjusted and > without SAMPLING SIZE set MEDIUM distributions sample only 2963 rows in > a table no matter how many rows the table contains (that's why it seems > to be so fast)! Using only RESOLUTION and CONFIDENCE you can get MEDIUM > to sample up to about 12million rows by setting RESOLUTION to 0.01 and > CONFIDENCE to 0.99. But at that rate it is virtually as slow as using > HIGH. If you use SAMPLING SIZE you can set the number of rows sampled > table by table to something reasonable and get better MEDIUM level stats > without the extra runtime of a HIGH. > > Art S. Kagel > Oninit (www.oninit.com <http://www.oninit.com>) > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>) > I would recommend that you do drop distributions when going from 9.40 all they way up to 11.50. You would need some pretty skewed data to get *really* bad query plans with no distributions. What have you got OPTCOMPIND set to?