Re: Updating statistics

What do you mean not Y2K compliant? Are you talking about a DBCENTURY bug/problem or is this something new? Please give me specifics. Thank you, Tracy >>> <Dick.Brieck@chase.com> 12/15/99 2:20:51 PM >>> djw@smooth1.demon.co.uk wrote: "Move to 7.31.UC4 now! 7.30.UC6 is not Y2K compliant and has bugs." I was surprised to hear this on December 13th! I thought we were compliant at 7.30.UC6. Would you please let me know what the bugs you referred to are? I am very interested. Thanks, Dick Brieck David Williams <djw@smooth1.demon.co.uk> on 12/13/99 05:59:08 PM Please respond to David Williams <djw@smooth1.demon.co.uk> To: informix-list@iiug.org cc: (bcc: Dick Brieck/CHASE) Subject: Re: Updating statistics In article <38555EBD.496B23B1@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Dick.Brieck@chase.com wrote: >> >> Greetings. I have a few questions regarding using distributions with IDS. >> We're currently using >> IDS 7.30.UC6 on Solaris 2.6. I've used Informix's prescribed algorithm for Move to 7.31.UC4 now! 7.30.UC6 is not Y2K compliant and has bugs. >> updating statistics on a >> limited basis on our databases. I've been gun shy about using them across the >> board as I've heard of >> bugs with UPDATE STATS HIGH in the past and experienced problems. At one point >> Informix told us >> to not use distributions. I'm not sure what may be lurking out there yet. I >> really appreciate sharing your >> insights with me on this. >> >> Here are my questions: >> >> 1) Have there been any problems using the Informix procedure for updating >> statistics? > >No direct problems in 7.30. There were bugs in 7.23 and the first release of >7.24 and in 7.31 distributions MAY aggrevate the problem with the bad index >page flags if you have an earlier version prior to the fix to that bug >(fixed in 7.31UC4 or 5). There may also be specific tables/queries which >will behave better with lower quality stats. In general these situations can >be solved using OPTIMIZER DIRECTIVES, but for some third party software this >is not practical. > >> 2) Do I need to consider changing nextsize for SYSDISTRIB from the default? >> What factors determine >> the size of the sysdistrib table? > >The disk table? Just the number of columns for which distributions are being >kept and the detail level of the stats (CONFIDENCE and RESOLUTION). > >> 3) Are there any other resources to consider before using distributions on a >> large scale? > >Yes the data distribution cache (onstat -g dsc). The default cache is >managed by a hash table with 31 buckets and either 20 or 127 entries per >bucket (depending on the version, onstat -g dsc reports the current values). >If you have more than the default number of active tables (or even close to >the number) you may want to increase the hash table size by adjusting the >undocumented ONCONFIG parameters DS_HASHSIZE (must be a prime number, default >31) and DS_POOLSIZE (usually a small integer, default 20 or 127). > >> 4) What disk space does the DBUPSPACE environmental variable refer to? I saw >> no answer for this >> in the Informix SQL ref. > >None. That environment variable controls the size of in-memory sort areas >and so indirectly affects the speed of large sorts including those needed >to calculate data distributions and stats. > >> 5) Are there any guidelines for the setting of DBUPSPACE? > >The default is normally fine but if memory is tight you can reduce it from >the default. > >> 6) I've compiled and played with Art Kagel's dostats program. It looks >> extremely useful. Has anyone >> had any noteworthy experiences using dostats, either good or bad? > >Welllll, I think it's great and use it all the time, but I guess you need to >hear from others. ;-) > Yep, no problems.. >Art S. Kagel -- David Williams

For whatever my 2 cents are worth... We have had 7.30.uc3 in a y2k testing environment for a year now and have uncovered no problems. That doesn't mean that there aren't any, just that the features that we use are stable. We do not use dbcentury. However, if there is a bug in the way that dbcentury performs, that doen't mean that the engine is not y2k compliant. My own opinion is that the dbcentury variable was created to help with applications that were not corrected to y2k compliance anyway and doesn't mean that the engine is not y2k compliant. Doug Tracy Sweeney wrote: > What do you mean not Y2K compliant? > Are you talking about a DBCENTURY bug/problem or is this something new? > Please give me specifics. > Thank you, > Tracy > > >>> <Dick.Brieck@chase.com> 12/15/99 2:20:51 PM >>> > > djw@smooth1.demon.co.uk wrote: > > "Move to 7.31.UC4 now! 7.30.UC6 is not Y2K compliant and has bugs." > > I was surprised to hear this on December 13th! I thought we were > compliant at 7.30.UC6. Would you please let me know what the bugs > you referred to are? I am very interested. > > Thanks, > > Dick Brieck > > David Williams <djw@smooth1.demon.co.uk> on 12/13/99 05:59:08 PM > > Please respond to David Williams <djw@smooth1.demon.co.uk> > > To: informix-list@iiug.org > cc: (bcc: Dick Brieck/CHASE) > Subject: Re: Updating statistics > > In article <38555EBD.496B23B1@bloomberg.net>, Art S. Kagel > <kagel@bloomberg.net> writes > >Dick.Brieck@chase.com wrote: > >> > >> Greetings. I have a few questions regarding using distributions with IDS. > >> We're currently using > >> IDS 7.30.UC6 on Solaris 2.6. I've used Informix's prescribed algorithm for > Move to 7.31.UC4 now! 7.30.UC6 is not Y2K compliant and has bugs. > > >> updating statistics on a > >> limited basis on our databases. I've been gun shy about using them across > the > >> board as I've heard of > >> bugs with UPDATE STATS HIGH in the past and experienced problems. At one > point > >> Informix told us > >> to not use distributions. I'm not sure what may be lurking out there yet. I > >> really appreciate sharing your > >> insights with me on this. > >> > >> Here are my questions: > >> > >> 1) Have there been any problems using the Informix procedure for updating > >> statistics? > > > >No direct problems in 7.30. There were bugs in 7.23 and the first release of > >7.24 and in 7.31 distributions MAY aggrevate the problem with the bad index > >page flags if you have an earlier version prior to the fix to that bug > >(fixed in 7.31UC4 or 5). There may also be specific tables/queries which > >will behave better with lower quality stats. In general these situations can > >be solved using OPTIMIZER DIRECTIVES, but for some third party software this > >is not practical. > > > >> 2) Do I need to consider changing nextsize for SYSDISTRIB from the default? > >> What factors determine > >> the size of the sysdistrib table? > > > >The disk table? Just the number of columns for which distributions are being > >kept and the detail level of the stats (CONFIDENCE and RESOLUTION). > > > >> 3) Are there any other resources to consider before using distributions on a > >> large scale? > > > >Yes the data distribution cache (onstat -g dsc). The default cache is > >managed by a hash table with 31 buckets and either 20 or 127 entries per > >bucket (depending on the version, onstat -g dsc reports the current values). > >If you have more than the default number of active tables (or even close to > >the number) you may want to increase the hash table size by adjusting the > >undocumented ONCONFIG parameters DS_HASHSIZE (must be a prime number, default > >31) and DS_POOLSIZE (usually a small integer, default 20 or 127). > > > >> 4) What disk space does the DBUPSPACE environmental variable refer to? I > saw > >> no answer for this > >> in the Informix SQL ref. > > > >None. That environment variable controls the size of in-memory sort areas > >and so indirectly affects the speed of large sorts including those needed > >to calculate data distributions and stats. > > > >> 5) Are there any guidelines for the setting of DBUPSPACE? > > > >The default is normally fine but if memory is tight you can reduce it from > >the default. > > > >> 6) I've compiled and played with Art Kagel's dostats program. It looks > >> extremely useful. Has anyone > >> had any noteworthy experiences using dostats, either good or bad? > > > >Welllll, I think it's great and use it all the time, but I guess you need to > >hear from others. ;-) > > > > Yep, no problems.. > > >Art S. Kagel > > -- > David Williams