Re: finding inplace alters

On Nov 17, 9:35 pm, "Floyd Wellershaus" <fl...@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; I should jump on the forums more often... sorrt for the late reply but IPA is interesting especially as everyone has to do it. If it's huge arse DB I'm interested to know what you did in the end for next time I likely need to address. IPA really is no fun, it's tempting to throw it at the down systems group if it fails because of such a dumb design by Informix, although the reason for the reversion problem is quite a tricky one. I think someone in Lenexa wrote a script to get around this but of course it's a ton of downtime. I presume you did some testing so am wondering why you might want to revert. If you don't need to revert you are giving yourself a headache for no reason, forget about the IPA's they'll get fixed eventually although multiple versions of tables is not ideal. Or perhaps with a huge DB you can only test for your bug once upgraded? With SAN's you can always sync up a copy of disk and revert if you have a problem in no time, but then I guess you lose transactions if you didnt just perform testing once upgraded. Restore is also probably too slow for you. Maybe you can get a coded binary with just your bug fix so you know there should not be any other new features which might cause the need for reversion. IPA's I find on huge databases need to be fixed over a few weeks and make sure you have a ton of log space depending on how you fix em. Best get onto 11.5+ ASAP, at least you'll have your IPA's done )

The IPA reversion problem is fixed as of 11.70. No longer a problem. 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 Sat, Dec 18, 2010 at 8:52 AM, PeterP <peterpain@gmail.com> wrote: > On Nov 17, 9:35 pm, "Floyd Wellershaus" <fl...@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; > > I should jump on the forums more often... sorrt for the late reply but > IPA is interesting especially as everyone has to do it. > > If it's huge arse DB I'm interested to know what you did in the end > for next time I likely need to address. > IPA really is no fun, it's tempting to throw it at the down systems > group if it fails because of such a dumb design by Informix, although > the reason for the reversion problem is quite a tricky one. I think > someone in Lenexa wrote a script to get around this but of course it's > a ton of downtime. > > I presume you did some testing so am wondering why you might want to > revert. If you don't need to revert you are giving yourself a headache > for no reason, forget about the IPA's they'll get fixed eventually > although multiple versions of tables is not ideal. > Or perhaps with a huge DB you can only test for your bug once > upgraded? > With SAN's you can always sync up a copy of disk and revert if you > have a problem in no time, but then I guess you lose transactions if > you didnt just perform testing once upgraded. Restore is also probably > too slow for you. > Maybe you can get a coded binary with just your bug fix so you know > there should not be any other new features which might cause the need > for reversion. > > IPA's I find on huge databases need to be fixed over a few weeks and > make sure you have a ton of log space depending on how you fix em. > Best get onto 11.5+ ASAP, at least you'll have your IPA's done ) > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Dec 18, 10:52 pm, Art Kagel <art.ka...@gmail.com> wrote: > The IPA reversion problem is fixed as of 11.70.  No longer a problem. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > Blog:http://informix-myview.blogspot.com/ I thought it was fixed in 11.50 with IPA pages now being treated differently since 11.50 or was it just the rollback code for revision. I can't easily dd out the partition pages at the moment and guess / work out what's changed in the structures. 11.70 just tells you how many pages have IPA for performance I thought. Got any links ..? Still there are IPA bugs for things like compression etc. Generally I don't worry about them (unless you have almost 255) but everytime a table has a problem it's another thing to consider, especially with fragmented. You would think there would be a tbl_IPA_cleaner thread or at least some kind of IPA admin fix task. Maybe a environment variable like NOFUZZYCKPT (strangely similar to IPA) NOIPA for when you DDL small tables. Anyone else had sleepless nights over damn IPA's with their associated LTX's. Yeah, Onion was the tool I was thinking of. ))

On Dec 20, 4:22 pm, PeterP <peterp...@gmail.com> wrote: > On Dec 18, 10:52 pm, Art Kagel <art.ka...@gmail.com> wrote: > > > The IPA reversion problem is fixed as of 11.70.  No longer a problem. > > > Art > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > Blog:http://informix-myview.blogspot.com/ > > I thought it was fixed in 11.50 with IPA pages now being treated > differently since 11.50 or was it just the rollback code for revision. > I can't easily dd out the partition pages at the moment and guess / > work out what's changed in the structures. > 11.70 just tells you how many pages have IPA for performance I > thought. Got any links ..? > > Still there are IPA bugs for things like compression etc. > Generally I don't worry about them (unless you have almost 255) but > everytime a table has a problem it's another thing to consider, > especially with fragmented. > > You would think there would be a tbl_IPA_cleaner thread or at least > some kind of IPA admin fix task. > Maybe a environment variable like NOFUZZYCKPT (strangely similar to > IPA) NOIPA for when you DDL small tables. > Anyone else had sleepless nights over damn IPA's with their associated > LTX's. > Yeah, Onion was the tool I was thinking of. )) I've had a script forever that identifies IPA's, but am certain it need tweaked to be complete - even after you resolve the IPAs, the script will still identify them. I believe that Jonathon (Leffler) actually modified the code to take care of this issue. I will post it here in a few minutes. For the record, what you're looking for are "slot 6 pages", or as Andreas said, "secondary partition pages." Traditionally, each partition has a single partition page that has 5 slots. With an IPA there is another page, the slot 6 page. My biggest usage of the IPA script was in fact the reversion issue, followed by any potential performance concerns. As Art mentioned, the reversion issue was fixed in 11.7 which is a huge and important fix. For those of you not on 11.7 (many I would think for now), word on the street was always "you can't upgrade if there are IPAs." That was always a myth - you can't revert (easily) if there are IPAs. I used to mention this "all over the world", and for the longest time very few people ever believed it. If they ever "hit it", then they believed it. ;) Thanks - Mark Scranton The Mark Scranton Group,LLC

On Tue, Dec 21, 2010 at 6:37 PM, Mark Scranton <mark@markscranton.com>wrote: > On Dec 20, 4:22 pm, PeterP <peterp...@gmail.com> wrote: > > On Dec 18, 10:52 pm, Art Kagel <art.ka...@gmail.com> wrote: > > > > > The IPA reversion problem is fixed as of 11.70. No longer a problem. > > > > > Art > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > IIUG Board of Directors (a...@iiug.org) > > > Blog:http://informix-myview.blogspot.com/ > > > > I thought it was fixed in 11.50 with IPA pages now being treated > > differently since 11.50 or was it just the rollback code for revision. > > I can't easily dd out the partition pages at the moment and guess / > > work out what's changed in the structures. > > 11.70 just tells you how many pages have IPA for performance I > > thought. Got any links ..? > > > > Still there are IPA bugs for things like compression etc. > > Generally I don't worry about them (unless you have almost 255) but > > everytime a table has a problem it's another thing to consider, > > especially with fragmented. > > > > You would think there would be a tbl_IPA_cleaner thread or at least > > some kind of IPA admin fix task. > > Maybe a environment variable like NOFUZZYCKPT (strangely similar to > > IPA) NOIPA for when you DDL small tables. > > Anyone else had sleepless nights over damn IPA's with their associated > > LTX's. > > Yeah, Onion was the tool I was thinking of. )) > > I've had a script forever that identifies IPA's, but am certain it > need tweaked to be complete - even after you resolve the IPAs, the > script will still identify them. I believe that Jonathon (Leffler) > actually modified the code to take care of this issue. I will post it > here in a few minutes. For the record, what you're looking for are > "slot 6 pages", or as Andreas said, "secondary partition pages." > Traditionally, each partition has a single partition page that has 5 > slots. With an IPA there is another page, the slot 6 page. My biggest > usage of the IPA script was in fact the reversion issue, followed by > any potential performance concerns. As Art mentioned, the reversion > issue was fixed in 11.7 which is a huge and important fix. For those > of you not on 11.7 (many I would think for now), word on the street > was always "you can't upgrade if there are IPAs." That was always a > myth - you can't revert (easily) if there are IPAs. I used to mention > this "all over the world", and for the longest time very few people > ever believed it. If they ever "hit it", then they believed it. ;) > > Thanks - > Mark Scranton > The Mark Scranton Group,LLC > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > I believe it's possible to get the pending in place alters using SQL. But it needs syssltdat and I'm not sure when it was introduced. And yes, as Andreas wrote this requires some knowledge about the internal structures... but then again... You can't get more internal than Onion :) We can get the extending partition pages from sysmaster, and then get the slot 6 data from them and interpret it to get the number of pages in each version. If any returns != 0 we have a pending inplace alter. This should run fairly quickly but I'd need to check this... If anybody has a V10 or V9 or v7 at hand I'd like to know if syssltdatt is present in sysmaster -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...