Removing Distributions Following Informix Upgrade

All, We are upgrading a large instance from IDS 9.40FC5 to IDS11.70.FC6. I have seen various comments on how to remove old distributions immediately after the upgrade. Is it safe to truncate the sysdistrib table in order to remove all old distributions, or should we run "update statistics low drop distributions only"? I am looking for the fastest way to initially remove all old distributions that is supported. Thanks. Pam Ekstrand Database Administrator Email: pamela.a.ekstrand.-nd@disney.com Home Office: 727-729-8210 Cell: 727-365-3781

Remove the rows from sysdistrib for each database in your instance. I would do this on the 9.40 database prior to the upgrade. delete from sysdistrib where 1=1; I have done this in the past and it works very well. For ANSI logged database the drop distributions takes forever. This worked for ANSI logged PeopleSoft numerous times.

Pam: It is supposed to be safe to just delete from sysdistrib, however, it will not be any faster than UPDATE STATISTICS LOW DROP DISTRIBUTIONS ONLY; since that does essentially the same thing and nothing else - and this is the approved method. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Nov 26, 2013 at 3:03 PM, Ekstrand, Pamela A. -ND < Pamela.A.Ekstrand.-ND@disney.com> wrote: > All, > > We are upgrading a large instance from IDS 9.40FC5 to IDS11.70.FC6. I have > seen various comments on how to remove old distributions immediately after > the > upgrade. > > Is it safe to truncate the sysdistrib table in order to remove all old > distributions, or should we run "update statistics low drop distributions > only"? I am looking for the fastest way to initially remove all old > distributions that is supported. > > Thanks. > > Pam Ekstrand > Database Administrator > Email: pamela.a.ekstrand.-nd@disney.com > Home Office: 727-729-8210 > Cell: 727-365-3781 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133a2ae49416b04ec1a2487

Note, the "DROP DISTRIBUTIONS ONLY" clause is not supported in 9.40, only in 11.xx+. So, run it shortly after the upgrade to 11.70, and don't forget the "ONLY" keyword or the engine will calculate LOW stats on all tables which WILL be slow, as Kernoal points out. BTW: Note that dostats has an option, --drop-distributions, to do this for you if you will be using dostats to recreate the distributions anyway. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Nov 26, 2013 at 3:14 PM, Art Kagel <art.kagel@gmail.com> wrote: > Pam: > > It is supposed to be safe to just delete from sysdistrib, however, it will > not be any faster than UPDATE STATISTICS LOW DROP DISTRIBUTIONS ONLY; since > that does essentially the same thing and nothing else - and this is the > approved method. > > Art > > > Art S. Kagel, Principal Consultant > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Nov 26, 2013 at 3:03 PM, Ekstrand, Pamela A. -ND < > Pamela.A.Ekstrand.-ND@disney.com> wrote: > >> All, >> >> We are upgrading a large instance from IDS 9.40FC5 to IDS11.70.FC6. I have >> seen various comments on how to remove old distributions immediately >> after the >> upgrade. >> >> Is it safe to truncate the sysdistrib table in order to remove all old >> distributions, or should we run "update statistics low drop distributions >> only"? I am looking for the fastest way to initially remove all old >> distributions that is supported. >> >> Thanks. >> >> Pam Ekstrand >> Database Administrator >> Email: pamela.a.ekstrand.-nd@disney.com >> Home Office: 727-729-8210 >> Cell: 727-365-3781 >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a11c3e6241469a804ec1a3f64