Re: finding inplace alters

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...