Re: finding inplace alters

Yes, Floyd. If there is even a single table that has pending alters the reversion will fail. Well, it's more like it will refuse to start. If that happens, any way that you can eliminate the pending alters is OK. So unloading the troublesome tables and reloading (either before or after the reversion) is OK. The only problem with waiting until the possible time of reversion to eliminate the pending alters is that at that point you are usually in crunch mode and downtime is a problem. Most people say "But I have downtime scheduled for the upgrade and there's lots of slack in there in case I have to revert!" The problem that they are missing is that it is REALLY rare for the upgrade itself to fail (and if it does a restore is the more likely required way to repair the damage) the more likely scenario is that the upgrade will succeed but after the upgrade, once everything is back online and users are using the system you will discover some serious performance issue or a deal breaking bug that you hadn't found during your testing of the new version in QA beforehand. At that point, users are complaining to support, the head of support has camped out behind your chair, your boss is calling every few minutes to ask when the system will be back, and you are pulling out whatever little hair you have left after the last big problem. That's not the time to begin a multiple hour long update or unload to eliminate the barriers to reversion. You want to have done that during the several days or weeks prior to the upgrade. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Nov 18, 2010 at 6:14 AM, Floyd Wellershaus <floyd@fusemail.com>wrote: > Well Art, i guess you verified what Fernando said. Thank you. > So even if one table has a pending IPA, the whole reversion would fail ? > > I'm wondering, if the reversion fails and complains about a specific table, > if one could export that table in the upgraded version, then drop it, then > revert successfully then import the table in the reverted version. Is that a > possibility ? > > Thanks ! > Floyd > > > > *----- Original Message -----* > *From:* art.kagel@gmail.com > *Sent:* Wed, November 17, 2010, 8:06 PM > *Subject:* Re: finding inplace alters > > Fernando, you are correct that you can properly upgrade without completing > the in-place alters and they only matter if you have to revert to the > previous version. At that time the alters all have to be complete or the > reversion will fail. > > That said, since you have much more time BEFORE an upgrade than you > normally do if the upgrade results in some performance problems or bugs that > force an emergency reversion so I always recommend that one complete all > pending in-place alters before attempting the upgrade. > > Also, the OP is using Informix 10.00, but users of later versions of 11.50 > and of 11.70 no longer have to worry about in-place alters as reversions > within the ranges of those versions will complete even with pending in-place > alters outstanding. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > 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 Wed, Nov 17, 2010 at 7:21 PM, Fernando Nunes <domusonline@gmail.com>wrote: > >> >> >> On Wed, Nov 17, 2010 at 9:35 PM, Floyd Wellershaus <floyd@fwellers.com>wrote: >> >>> Hi, >>> Running ids10.0.fc5 on aix5.3 and needing to upgrade to 10.0.fc11 to get >>> a bugfix. >>> trying to find and fix inplace alters. The script ibm supplies doesn't >>> work for mulitple reasons so far. It fails on some oncheck -pp's and also >>> when it builds a list of tables out of our 60k plus tables, it runs out of >>> memory. >>> >>> So I am trying to use this that i got from somewhere a while ago, but I >>> guess it's not reliable because even after I do the dummy updates the tables >>> still show up. >>> >>> Any idea of a decent way to at least find the inplace atlers on a huge >>> ass database with over 60,000 tables some of them containing billions of >>> rows ? >>> >>> Thanks, >>> floyd >>> >>> database sysmaster; >>> set isolation to dirty read;>>> select pg_partnum + pg_pagenum - 1 partn from syspaghdr, sysdbspaces a >>> where pg_partnum = 1048576 * a.dbsnum + 1 and pg_next!=0 into temp pp with >>> no log; >>> >>> unload to altered_tabs>>> select b.dbsname database, b.tabname table from systabnames b, pp where >>> partn = partnum; >>> >>> >> This will tell you all the tables that had suffer an inplace alter table. >> What you want is the list of tables that have what is usually called >> "pending" inplace alters... >> If the number is relatively low, and the dummy updates run quickly, you >> should be fine. >> The script above will always give you the same set of tables (unless you >> recreate them). >> >> AFAIK there is no quick way to find pending inplace alters... Only a slow >> process. Not sure if Panther changed this... >> >> Also note: If I'm not mistaken, the issue with pending inplace alters only >> happens when you try to revert. The migration should work fine with pending >> inplace alters. Please confirm with the migration guide or tech support. >> >> Regards. >> >> -- >> Fernando Nunes >> Portugal >> >> http://informix-technology.blogspot.com >> My email works... but I don't check it frequently... >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >> >