RE: Rebuild Distributions After an Upgrade - B....

Thanks Art. I'm concerned about my larger tables being left with no distributions and wrong query plans for extended period of times. UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY; -- our distributions are gone now, right? -- any query plan generated between now and the completion of the high distributions only run (and to a lesser effect the medium run) will be generated without any distribution knowledge and could be wrong. -- on large tables this could be many hours -- does it make sense to insert a quick running "update statistics medium for table distributions only" to at least have something there for the optimizer? UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid) DISTRIBUTIONSONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, statchange, statlevel); -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, May 18, 2016 9:35 AM To: ids@iiug.org Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] Andrew: You can use the dostats --clean-distributions option that does what you propose. It starts processing each table by dropping the distributions on it. Here's a sample run: $ dostats -m -S -d art -t systables --clean-distributions -f - 2>/dev/null DATABASE art; SET ISOLATION COMMITTED READ; SET PDQPRIORITY 0; UPDATESTATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid) DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, statchange, statlevel); Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford <andrew@informix-dba.com> wrote: > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > am interested in the best way to drop and recreate distributions to > minimize impact. > > Would this be the most reasonable approach? > > for each table: > > update statistics for table <table> drop distributions only;> > update statistics medium for table <table> distributions only; -- to> quickly put SOME reasonable distributions in place > > -- do my normal update statistics from Art's dostats > > update statistics low for table <table> (<columns>);> > update statistics high for table <table> (<columns>) distributions> only; > > update statistics medium for table <table> (<columns>);> > Thanks, > > Andrew > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0103eb420ae35e05331ec469 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Please vote on an RFE that requested the engine should do the automatic upgrade os the statistics... It makes little sense to force such a huge burden on an upgrade... I admit we may need to change the way we store the distributions, but how many times did it happen? And what was introduced that would prevent an "inplace" change of the exixting distributions? This would not prevent that more information (if we add more info) would be required to have optimatl distributions.... but "porting" the existing ones should allow reasonable behavior of the optimizer. hmmm.... unfortunately the RFE was declined: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 Regards. On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> wrote: > Thanks Art. I'm concerned about my larger tables being left with no > distributions and wrong query plans for extended period of times. > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > -- our distributions are gone now, right? > -- any query plan generated between now and the completion of the high > distributions only run (and to a lesser effect the medium run) will be > generated without any distribution knowledge and could be wrong. > -- on large tables this could be many hours > -- does it make sense to insert a quick running "update statistics medium > for table distributions only" to at least have something there for the > optimizer? > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid) DISTRIBUTIONS> ONLY; > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, May 18, 2016 9:35 AM > To: ids@iiug.org > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > Andrew: > > You can use the dostats --clean-distributions option that does what you > propose. It starts processing each table by dropping the distributions on > it. Here's a sample run: > > $ dostats -m -S -d art -t systables --clean-distributions -f - 2>/dev/null > DATABASE art; SET ISOLATION COMMITTED READ; SET PDQPRIORITY 0; UPDATE> STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY; UPDATE > STATISTICS LOW FOR TABLE systables (tabname, owner); UPDATE STATISTICS LOW > FOR TABLE systables (tabid); UPDATE STATISTICS HIGH FOR TABLE systables > (tabname, tabid) DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE > systables (owner, partnum, rowsize, ncols, nindexes, nrows, created, > version, tabtype, locklevel, npused, fextsize, nextsize, flags, site, > dbname, type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > statchange, statlevel); > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > am interested in the best way to drop and recreate distributions to > > minimize impact. > > > > Would this be the most reasonable approach? > > > > for each table: > > > > update statistics for table <table> drop distributions only;> > > > update statistics medium for table <table> distributions only; -- to> > quickly put SOME reasonable distributions in place > > > > -- do my normal update statistics from Art's dostats > > > > update statistics low for table <table> (<columns>);> > > > update statistics high for table <table> (<columns>) distributions> > only; > > > > update statistics medium for table <table> (<columns>);> > > > Thanks, > > > > Andrew > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0103eb420ae35e05331ec469 > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11475b3cec10a005331f2b32

Andrew: I hear you. And what you say is reasonable. Two comments: 1- If you do that then the medium with column list is redundant and can be removed. 2-Before you change the ordering for a specific table, test on dev or qa whether having only the low stats will indeed cause poor query plans for typical queries against that/those table(s). Not having distributions invokes the older optimizer core which wasn't bad for most OLTP type queries. 3-That said, the medium will tend to be faster than the low unless you have sampling enabled for lows. So ymmv... Art On May 18, 2016 10:56, "Andrew Ford" <andrew@informix-dba.com> wrote: > Thanks Art. I'm concerned about my larger tables being left with no > distributions and wrong query plans for extended period of times. > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > -- our distributions are gone now, right? > -- any query plan generated between now and the completion of the high > distributions only run (and to a lesser effect the medium run) will be > generated without any distribution knowledge and could be wrong. > -- on large tables this could be many hours > -- does it make sense to insert a quick running "update statistics medium > for table distributions only" to at least have something there for the > optimizer? > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid) DISTRIBUTIONS> ONLY; > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, May 18, 2016 9:35 AM > To: ids@iiug.org > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > Andrew: > > You can use the dostats --clean-distributions option that does what you > propose. It starts processing each table by dropping the distributions on > it. Here's a sample run: > > $ dostats -m -S -d art -t systables --clean-distributions -f - 2>/dev/null > DATABASE art; SET ISOLATION COMMITTED READ; SET PDQPRIORITY 0; UPDATE> STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY; UPDATE > STATISTICS LOW FOR TABLE systables (tabname, owner); UPDATE STATISTICS LOW > FOR TABLE systables (tabid); UPDATE STATISTICS HIGH FOR TABLE systables > (tabname, tabid) DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE > systables (owner, partnum, rowsize, ncols, nindexes, nrows, created, > version, tabtype, locklevel, npused, fextsize, nextsize, flags, site, > dbname, type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > statchange, statlevel); > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > am interested in the best way to drop and recreate distributions to > > minimize impact. > > > > Would this be the most reasonable approach? > > > > for each table: > > > > update statistics for table <table> drop distributions only;> > > > update statistics medium for table <table> distributions only; -- to> > quickly put SOME reasonable distributions in place > > > > -- do my normal update statistics from Art's dostats > > > > update statistics low for table <table> (<columns>);> > > > update statistics high for table <table> (<columns>) distributions> > only; > > > > update statistics medium for table <table> (<columns>);> > > > Thanks, > > > > Andrew > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0103eb420ae35e05331ec469 > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c055f68c1cbc105331f4eb2

Once it's been declined, voting is no longer allowed IB. Art On May 18, 2016 11:04, "Fernando Nunes" <domusonline@gmail.com> wrote: > Please vote on an RFE that requested the engine should do the automatic > upgrade os the statistics... > It makes little sense to force such a huge burden on an upgrade... > I admit we may need to change the way we store the distributions, but how > many times did it happen? And what was introduced that would prevent an > "inplace" change of the exixting distributions? > > This would not prevent that more information (if we add more info) would be > required to have optimatl distributions.... but "porting" the existing ones > should allow reasonable behavior of the optimizer. > > hmmm.... unfortunately the RFE was declined: > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 > > Regards. > > On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Thanks Art. I'm concerned about my larger tables being left with no > > distributions and wrong query plans for extended period of times. > > > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > > > -- our distributions are gone now, right? > > -- any query plan generated between now and the completion of the high > > distributions only run (and to a lesser effect the medium run) will be > > generated without any distribution knowledge and could be wrong. > > -- on large tables this could be many hours > > -- does it make sense to insert a quick running "update statistics medium > > for table distributions only" to at least have something there for the > > optimizer? > > > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > > > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid) DISTRIBUTIONS> > ONLY; > > > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art > > Kagel > > Sent: Wednesday, May 18, 2016 9:35 AM > > To: ids@iiug.org > > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > > > Andrew: > > > > You can use the dostats --clean-distributions option that does what you > > propose. It starts processing each table by dropping the distributions on > > it. Here's a sample run: > > > > $ dostats -m -S -d art -t systables --clean-distributions -f - > 2>/dev/null > > DATABASE art; SET ISOLATION COMMITTED READ; SET PDQPRIORITY 0; UPDATE> > STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY; UPDATE > > STATISTICS LOW FOR TABLE systables (tabname, owner); UPDATE STATISTICS > LOW > > FOR TABLE systables (tabid); UPDATE STATISTICS HIGH FOR TABLE systables > > (tabname, tabid) DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE > > systables (owner, partnum, rowsize, ncols, nindexes, nrows, created, > > version, tabtype, locklevel, npused, fextsize, nextsize, flags, site, > > dbname, type_xid, am_id, pagesize, ustlowts, secpolicyid, > protgranularity, > > statchange, statlevel); > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on the IIUG, nor any other organization with which I am > > associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford <andrew@informix-dba.com> > > wrote: > > > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > > am interested in the best way to drop and recreate distributions to > > > minimize impact. > > > > > > Would this be the most reasonable approach? > > > > > > for each table: > > > > > > update statistics for table <table> drop distributions only;> > > > > > update statistics medium for table <table> distributions only; -- to> > > quickly put SOME reasonable distributions in place > > > > > > -- do my normal update statistics from Art's dostats > > > > > > update statistics low for table <table> (<columns>);> > > > > > update statistics high for table <table> (<columns>) distributions> > > only; > > > > > > update statistics medium for table <table> (<columns>);> > > > > > Thanks, > > > > > > Andrew > > > > > > > > > > > > > > > > > **************************************************************************** > > *** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e0103eb420ae35e05331ec469 > > > > > > > **************************************************************************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a11475b3cec10a005331f2b32 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c05ff024970d105331f547e

I'm a little confused by all of this drop distributions business anyway. If old Informix version A stores distributions in format A and new Informix version B stores them in format B... If you upgrade from version A to B and don't perform the "drop distributions only", what happens when you run update statistics high or medium? Are distributions stored in old format A or new format B? I would think that since we are on version B of the engine that new distributions would be created in format B and the drop distributions is redundant. Or will version B of the engine continue to store statistics in format A when update statistics high or medium is run if we don't initially perform the "drop distributions only"? Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, May 18, 2016 10:04 AM To: ids@iiug.org Subject: Re: Rebuild Distributions After an Upgrade - B.... [37157] Please vote on an RFE that requested the engine should do the automatic upgrade os the statistics... It makes little sense to force such a huge burden on an upgrade... I admit we may need to change the way we store the distributions, but how many times did it happen? And what was introduced that would prevent an "inplace" change of the exixting distributions? This would not prevent that more information (if we add more info) would be required to have optimatl distributions.... but "porting" the existing ones should allow reasonable behavior of the optimizer. hmmm.... unfortunately the RFE was declined: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 Regards. On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> wrote: > Thanks Art. I'm concerned about my larger tables being left with no > distributions and wrong query plans for extended period of times. > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > -- our distributions are gone now, right? > -- any query plan generated between now and the completion of the high > distributions only run (and to a lesser effect the medium run) will be > generated without any distribution knowledge and could be wrong. > -- on large tables this could be many hours > -- does it make sense to insert a quick running "update statistics > medium for table distributions only" to at least have something there > for the optimizer? > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> DISTRIBUTIONS ONLY; > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Wednesday, May 18, 2016 9:35 AM > To: ids@iiug.org > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > Andrew: > > You can use the dostats --clean-distributions option that does what > you propose. It starts processing each table by dropping the > distributions on it. Here's a sample run: > > $ dostats -m -S -d art -t systables --clean-distributions -f -> 2>/dev/null DATABASE art; SET ISOLATION COMMITTED READ; SET > PDQPRIORITY 0; UPDATE STATISTICS LOW FOR TABLE systables DROP > DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE systables > (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables > (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, > tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, > type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > statchange, statlevel); > > Art > > Art S. Kagel, President and Principal Consultant ASK Database > Management www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on the IIUG, nor any other organization > with which I am associated either explicitly, implicitly, or by > inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford > <andrew@informix-dba.com> > wrote: > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > am interested in the best way to drop and recreate distributions to > > minimize impact. > > > > Would this be the most reasonable approach? > > > > for each table: > > > > update statistics for table <table> drop distributions only;> > > > update statistics medium for table <table> distributions only; -- to> > quickly put SOME reasonable distributions in place > > > > -- do my normal update statistics from Art's dostats > > > > update statistics low for table <table> (<columns>);> > > > update statistics high for table <table> (<columns>) distributions> > only; > > > > update statistics medium for table <table> (<columns>);> > > > Thanks, > > > > Andrew > > > > > > > > > > ********************************************************************** > ****** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0103eb420ae35e05331ec469 > > > ********************************************************************** > ****** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11475b3cec10a005331f2b32 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

No one has ever explained that one to me to satisfaction either Andrew. I just know that often, if after an upgrade someone has just run update stats without dropping distributions first, sometimes performance rots until they do it all again with the drop. This was true even before 11.7 and the whole AUTO_STATS mode that might no-op the commands without the FORCE option. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 11:25 AM, Andrew Ford <andrew@informix-dba.com> wrote: > I'm a little confused by all of this drop distributions business anyway. > > If old Informix version A stores distributions in format A and new Informix > version B stores them in format B... > > If you upgrade from version A to B and don't perform the "drop > distributions > only", what happens when you run update statistics high or medium? > > Are distributions stored in old format A or new format B? > > I would think that since we are on version B of the engine that new > distributions would be created in format B and the drop distributions is > redundant. > > Or will version B of the engine continue to store statistics in format A > when update statistics high or medium is run if we don't initially perform > the "drop distributions only"? > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, May 18, 2016 10:04 AM > To: ids@iiug.org > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37157] > > Please vote on an RFE that requested the engine should do the automatic > upgrade os the statistics... > It makes little sense to force such a huge burden on an upgrade... > I admit we may need to change the way we store the distributions, but how > many times did it happen? And what was introduced that would prevent an > "inplace" change of the exixting distributions? > > This would not prevent that more information (if we add more info) would be > required to have optimatl distributions.... but "porting" the existing ones > should allow reasonable behavior of the optimizer. > > hmmm.... unfortunately the RFE was declined: > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 > > Regards. > > On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Thanks Art. I'm concerned about my larger tables being left with no > > distributions and wrong query plans for extended period of times. > > > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > > > -- our distributions are gone now, right? > > -- any query plan generated between now and the completion of the high > > distributions only run (and to a lesser effect the medium run) will be > > generated without any distribution knowledge and could be wrong. > > -- on large tables this could be many hours > > -- does it make sense to insert a quick running "update statistics > > medium for table distributions only" to at least have something there > > for the optimizer? > > > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > > > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > DISTRIBUTIONS ONLY; > > > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Art Kagel > > Sent: Wednesday, May 18, 2016 9:35 AM > > To: ids@iiug.org > > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > > > Andrew: > > > > You can use the dostats --clean-distributions option that does what > > you propose. It starts processing each table by dropping the > > distributions on it. Here's a sample run: > > > > $ dostats -m -S -d art -t systables --clean-distributions -f -> > 2>/dev/null DATABASE art; SET ISOLATION COMMITTED READ; SET > > PDQPRIORITY 0; UPDATE STATISTICS LOW FOR TABLE systables DROP > > DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE systables > > (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables > > (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, > > tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, > > type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > > statchange, statlevel); > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database > > Management www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on the IIUG, nor any other organization > > with which I am associated either explicitly, implicitly, or by > > inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford > > <andrew@informix-dba.com> > > wrote: > > > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > > am interested in the best way to drop and recreate distributions to > > > minimize impact. > > > > > > Would this be the most reasonable approach? > > > > > > for each table: > > > > > > update statistics for table <table> drop distributions only;> > > > > > update statistics medium for table <table> distributions only; -- to> > > quickly put SOME reasonable distributions in place > > > > > > -- do my normal update statistics from Art's dostats > > > > > > update statistics low for table <table> (<columns>);> > > > > > update statistics high for table <table> (<columns>) distributions> > > only; > > > > > > update statistics medium for table <table> (<columns>);> > > > > > Thanks, > > > > > > Andrew > > > > > > > > > > > > > > > > ********************************************************************** > > ****** > > *** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e0103eb420ae35e05331ec469 > > > > > > ********************************************************

As far as I understand "DROP DISTRIBUTIONS" is only useful if you really need to test a query without distributions. ... WITH DISTIBUTIONS ONLY should replace existing ones. Regards On Wed, May 18, 2016 at 4:25 PM, Andrew Ford <andrew@informix-dba.com> wrote: > I'm a little confused by all of this drop distributions business anyway. > > If old Informix version A stores distributions in format A and new Informix > version B stores them in format B... > > If you upgrade from version A to B and don't perform the "drop > distributions > only", what happens when you run update statistics high or medium? > > Are distributions stored in old format A or new format B? > > I would think that since we are on version B of the engine that new > distributions would be created in format B and the drop distributions is > redundant. > > Or will version B of the engine continue to store statistics in format A > when update statistics high or medium is run if we don't initially perform > the "drop distributions only"? > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Wednesday, May 18, 2016 10:04 AM > To: ids@iiug.org > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37157] > > Please vote on an RFE that requested the engine should do the automatic > upgrade os the statistics... > It makes little sense to force such a huge burden on an upgrade... > I admit we may need to change the way we store the distributions, but how > many times did it happen? And what was introduced that would prevent an > "inplace" change of the exixting distributions? > > This would not prevent that more information (if we add more info) would be > required to have optimatl distributions.... but "porting" the existing ones > should allow reasonable behavior of the optimizer. > > hmmm.... unfortunately the RFE was declined: > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 > > Regards. > > On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > Thanks Art. I'm concerned about my larger tables being left with no > > distributions and wrong query plans for extended period of times. > > > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > > > -- our distributions are gone now, right? > > -- any query plan generated between now and the completion of the high > > distributions only run (and to a lesser effect the medium run) will be > > generated without any distribution knowledge and could be wrong. > > -- on large tables this could be many hours > > -- does it make sense to insert a quick running "update statistics > > medium for table distributions only" to at least have something there > > for the optimizer? > > > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > > > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > DISTRIBUTIONS ONLY; > > > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Art Kagel > > Sent: Wednesday, May 18, 2016 9:35 AM > > To: ids@iiug.org > > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > > > Andrew: > > > > You can use the dostats --clean-distributions option that does what > > you propose. It starts processing each table by dropping the > > distributions on it. Here's a sample run: > > > > $ dostats -m -S -d art -t systables --clean-distributions -f -> > 2>/dev/null DATABASE art; SET ISOLATION COMMITTED READ; SET > > PDQPRIORITY 0; UPDATE STATISTICS LOW FOR TABLE systables DROP > > DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE systables > > (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables > > (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, > > tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, > > type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > > statchange, statlevel); > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database > > Management www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on the IIUG, nor any other organization > > with which I am associated either explicitly, implicitly, or by > > inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford > > <andrew@informix-dba.com> > > wrote: > > > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > > am interested in the best way to drop and recreate distributions to > > > minimize impact. > > > > > > Would this be the most reasonable approach? > > > > > > for each table: > > > > > > update statistics for table <table> drop distributions only;> > > > > > update statistics medium for table <table> distributions only; -- to> > > quickly put SOME reasonable distributions in place > > > > > > -- do my normal update statistics from Art's dostats > > > > > > update statistics low for table <table> (<columns>);> > > > > > update statistics high for table <table> (<columns>) distributions> > > only; > > > > > > update statistics medium for table <table> (<columns>);> > > > > > Thanks, > > > > > > Andrew > > > > > > > > > > > > > > > > ********************************************************************** > > ****** > > *** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e0103eb420ae35e05331ec469 > > > > > > ********************************************************************** > > ****** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a11475b3cec10a005331f2b32 > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a respo

And yet, the migration guide recommends dropping distributions and THEN rebuilding them after an upgrade. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Wed, May 18, 2016 at 12:04 PM, Fernando Nunes <domusonline@gmail.com> wrote: > As far as I understand "DROP DISTRIBUTIONS" is only useful if you really > need to test a query without distributions. > .... WITH DISTIBUTIONS ONLY should replace existing ones. > > Regards > > On Wed, May 18, 2016 at 4:25 PM, Andrew Ford <andrew@informix-dba.com> > wrote: > > > I'm a little confused by all of this drop distributions business anyway. > > > > If old Informix version A stores distributions in format A and new > Informix > > version B stores them in format B... > > > > If you upgrade from version A to B and don't perform the "drop > > distributions > > only", what happens when you run update statistics high or medium? > > > > Are distributions stored in old format A or new format B? > > > > I would think that since we are on version B of the engine that new > > distributions would be created in format B and the drop distributions is > > redundant. > > > > Or will version B of the engine continue to store statistics in format A > > when update statistics high or medium is run if we don't initially > perform > > the "drop distributions only"? > > > > Andrew > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando Nunes > > Sent: Wednesday, May 18, 2016 10:04 AM > > To: ids@iiug.org > > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37157] > > > > Please vote on an RFE that requested the engine should do the automatic > > upgrade os the statistics... > > It makes little sense to force such a huge burden on an upgrade... > > I admit we may need to change the way we store the distributions, but how > > many times did it happen? And what was introduced that would prevent an > > "inplace" change of the exixting distributions? > > > > This would not prevent that more information (if we add more info) would > be > > required to have optimatl distributions.... but "porting" the existing > ones > > should allow reasonable behavior of the optimizer. > > > > hmmm.... unfortunately the RFE was declined: > > > > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33799 > > > > Regards. > > > > On Wed, May 18, 2016 at 3:56 PM, Andrew Ford <andrew@informix-dba.com> > > wrote: > > > > > Thanks Art. I'm concerned about my larger tables being left with no > > > distributions and wrong query plans for extended period of times. > > > > > > UPDATE STATISTICS LOW FOR TABLE systables DROP DISTRIBUTIONS ONLY;> > > > > > -- our distributions are gone now, right? > > > -- any query plan generated between now and the completion of the high > > > distributions only run (and to a lesser effect the medium run) will be > > > generated without any distribution knowledge and could be wrong. > > > -- on large tables this could be many hours > > > -- does it make sense to insert a quick running "update statistics > > > medium for table distributions only" to at least have something there > > > for the optimizer? > > > > > > UPDATE STATISTICS LOW FOR TABLE systables (tabname, owner);> > > > > > UPDATE STATISTICS LOW FOR TABLE systables (tabid);> > > > > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > > DISTRIBUTIONS ONLY; > > > > > > UPDATE STATISTICS MEDIUM FOR TABLE systables (owner, partnum, rowsize, > > > ncols, nindexes, nrows, created, version, tabtype, locklevel, npused, > > > fextsize, nextsize, flags, site, dbname, type_xid, am_id, pagesize, > > > ustlowts, secpolicyid, protgranularity, statchange, statlevel);> > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Art Kagel > > > Sent: Wednesday, May 18, 2016 9:35 AM > > > To: ids@iiug.org > > > Subject: Re: Rebuild Distributions After an Upgrade - B.... [37155] > > > > > > Andrew: > > > > > > You can use the dostats --clean-distributions option that does what > > > you propose. It starts processing each table by dropping the > > > distributions on it. Here's a sample run: > > > > > > $ dostats -m -S -d art -t systables --clean-distributions -f -> > > 2>/dev/null DATABASE art; SET ISOLATION COMMITTED READ; SET > > > PDQPRIORITY 0; UPDATE STATISTICS LOW FOR TABLE systables DROP > > > DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE systables > > > (tabname, owner); UPDATE STATISTICS LOW FOR TABLE systables (tabid); > > > UPDATE STATISTICS HIGH FOR TABLE systables (tabname, tabid)> > > DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE systables > > > (owner, partnum, rowsize, ncols, nindexes, nrows, created, version, > > > tabtype, locklevel, npused, fextsize, nextsize, flags, site, dbname, > > > type_xid, am_id, pagesize, ustlowts, secpolicyid, protgranularity, > > > statchange, statlevel); > > > > > > Art > > > > > > Art S. Kagel, President and Principal Consultant ASK Database > > > Management www.askdbmgt.com > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions and do not reflect on the IIUG, nor any other organization > > > with which I am associated either explicitly, implicitly, or by > > > inference. 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 Wed, May 18, 2016 at 10:14 AM, Andrew Ford > > > <andrew@informix-dba.com> > > > wrote: > > > > > > > I'm performing in-place upgrades from 11.50 and 11.70 to 12.10 and I > > > > am interested in the best way to drop and recreate distributions to > > > > minimize impact. > > > > > > > > Would this be the most reasonable approach? > > > > > > > > for each table: > > > > > > > > update statistics for table <table> drop distributions only;> > > > > > > > update statistics medium for table <table> distributions only; -- to> > > > quickly put SOME reasonable distributions in place > > > > > > > > -- do my normal update statistics from Art's dostats > > > > > > > > update statistics low for table <table> (<columns>);> > > > > > > > update statistics high for table <table> (<columns>) distributions> > > > only; > > > > > > > > update statistics medium for table <table> (<columns>);> > > > > > > > Thanks, > > > > > > > > Andrew > > > > > > > > > >