Re: Complete in-place alters

Habichtsberg, Reinhard wrote: > > Fernando Nunes wrote >> Habichtsberg, Reinhard wrote: >>> Hi all, >>> >>> In preperation for migration from IDS 9.40 to 11.5 we have >> to complete all >>> in-place alters. We have tables up to a billion rows with >> many pending >>> in-place alters. The amount of data adds up to some TB. >> Why do you "have to"? Is that in IDS 11.50 documentation? >> I know that this question can turn this into an hot topic... >> I browsed through >> the documentation very quickly and I think that step is not there... >> >> Note that if you need to revert you should not have pending >> in-place alter tables. > > In Migration Guide chapter 1: Overview of Dynamic Server Migration page 1.3 > and 1.4 topics: Upgrading Dynamic Server (In-place Migration) and Migrating > Dynamic Server (Non-in-place Migration): 1. Prepare your system. That > includes removing outstanding in-place alters, ... > > May be the reason is the reverting issue as you an OTC pointed out. Anyway > it seems to be no mistake to the job. What I'm looking for is some advice to > do it well... > > Any further suggestions? Nice. On the detailed sections the reference does not exists as it's not a requirement for the upgrade. As stated it's a requirement for the reversion. Which leads me to a question: How many of you did a reversion, and why you did it? Not flame intended. I understand perfectly the "prefer to be safe" point of view... Regarding your points, or in other words, assuming you will remove the in-place alters...: There is no real "magic" here. You can find out quickly with SQL which tables _had_ an in-place alter at any time in the past. AFAIK there is no quick way to get which tables have _pending_ in-place alters. I believe there is a feature request for this, although I can't think of a way to do it quickly. Some suggestions: - You can start by the quick SQL, and then look at the tables returned... If they are small, it's probably better to do the dummy updates. If they're big, you may want to consider the use of oncheck. - Note that a table that suffers the dummy updates will still appear in the "quick SQL" - You can spend some days (if you have the time) doing some dummy updates in batches, eventually at off-peak hours - If you have the hardware to do it (and most environments will not) you can create an HDR secondary. If your migration is not successful, redirect the clients to the secondary, while you restore your primary. Your secondary will be with the image before the upgrade. Obviously this is not an option if you decide to revert after running thins on the new version.... this leads to my final comments: The question above, about how many times did you revert, and why (not a question for you, but for whom wants to answer), is because, there are so many restrictions on the reversion process that I find it hard to use it in a real live scenario. I had migrations that aborted (always while testing, thankfully), but in this cases I ended up with a crashed instance. Reversion was not an option. Other situations where I could consider reversion are probably related to problems in the most important phases of a migrations: plan, test, plan, test, plan, test.... ;) This year I did a lot of 7.31 to 10.00.FC8 (in place migrations) rather successfully. Next year it will be for 11.50 (with platform change, so not in-place). Reversion was never an option I considered. By the way, someone posted a script to find the in-place alters in the IIUG list. I'm not sure if you're posting through IIUG or not, but in the newsgroup I don't see the message with the script. It could be useful for you. If you don't see it someone that follows the IIUG can send it to you. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

----- Original Message ----- From: "Fernando Nunes" <domusonline@gmail.com> Newsgroups: comp.databases.informix Sent: Friday, October 31, 2008 11:21 PM Subject: Re: Complete in-place alters > Nice. On the detailed sections the reference does not exists as it's not a > requirement for the upgrade. As stated it's a requirement for the > reversion. > Which leads me to a question: How many of you did a reversion, and why you > did it? Not flame intended. I understand perfectly the "prefer to be safe" > point of view... I did a reversion of an upgrade to 11.10 because there is a bug in same that crashes the instance if certain types of stored procedures are present, which unfortunately they are in my customer's case. I believe that this bug remains at large in 11.10 at least.

Neil Truby wrote: > ----- Original Message ----- From: "Fernando Nunes" <domusonline@gmail.com> > Newsgroups: comp.databases.informix > Sent: Friday, October 31, 2008 11:21 PM > Subject: Re: Complete in-place alters > > >> Nice. On the detailed sections the reference does not exists as it's >> not a requirement for the upgrade. As stated it's a requirement for >> the reversion. >> Which leads me to a question: How many of you did a reversion, and why >> you did it? Not flame intended. I understand perfectly the "prefer to >> be safe" point of view... > > I did a reversion of an upgrade to 11.10 because there is a bug in same > that crashes the instance if certain types of stored procedures are > present, which unfortunately they are in my customer's case. I believe > that this bug remains at large in 11.10 at least. Ok. But how did it pass through the test phase? -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

"Fernando Nunes" <domusonline@gmail.com> wrote in message news:gekjho$a7q$1@registered.motzarella.org... > Neil Truby wrote: >> ----- Original Message ----- From: "Fernando Nunes" >> <domusonline@gmail.com> >> Newsgroups: comp.databases.informix >> Sent: Friday, October 31, 2008 11:21 PM >> Subject: Re: Complete in-place alters >> >> >>> Nice. On the detailed sections the reference does not exists as it's not >>> a requirement for the upgrade. As stated it's a requirement for the >>> reversion. >>> Which leads me to a question: How many of you did a reversion, and why >>> you did it? Not flame intended. I understand perfectly the "prefer to be >>> safe" point of view... >> >> I did a reversion of an upgrade to 11.10 because there is a bug in same >> that crashes the instance if certain types of stored procedures are >> present, which unfortunately they are in my customer's case. I believe >> that this bug remains at large in 11.10 at least. > > Ok. But how did it pass through the test phase? Are you asking me how we didn't spot this in testing? Or, how it got past IBM QA?

Neil Truby wrote: > > "Fernando Nunes" <domusonline@gmail.com> wrote in message > news:gekjho$a7q$1@registered.motzarella.org... >> Neil Truby wrote: >>> ----- Original Message ----- From: "Fernando Nunes" >>> <domusonline@gmail.com> >>> Newsgroups: comp.databases.informix >>> Sent: Friday, October 31, 2008 11:21 PM >>> Subject: Re: Complete in-place alters >>> >>> >>>> Nice. On the detailed sections the reference does not exists as it's >>>> not a requirement for the upgrade. As stated it's a requirement for >>>> the reversion. >>>> Which leads me to a question: How many of you did a reversion, and >>>> why you did it? Not flame intended. I understand perfectly the >>>> "prefer to be safe" point of view... >>> >>> I did a reversion of an upgrade to 11.10 because there is a bug in >>> same that crashes the instance if certain types of stored procedures >>> are present, which unfortunately they are in my customer's case. I >>> believe that this bug remains at large in 11.10 at least. >> >> Ok. But how did it pass through the test phase? > > Are you asking me how we didn't spot this in testing? > Or, how it got past IBM QA? > I was thinking about your customer testing. But I've seen situations which make your question valid... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Hello Reinhard, Here is an SQL that should help. If any of it (or its comments) are wrong then I hope others out there will correct me: --# This SQL finds tables that have been altered but the data change is pending. DATABASE sysmaster; SET ISOLATION TO DIRTY READ;SELECT t.dbsname database, t.tabname table_name FROM systabnames t, sysdbspaces d, syspaghdr p --<-- Same as systabinfo. WHERE t.partnum = p.pg_partnum + p.pg_pagenum - 1 AND p.pg_partnum = 1048576 * d.dbsnum + 1 AND p.pg_next != 0 ORDER BY 1,2 --# To fix the altered table, run SQL below (the engine is smart enough to not --# log this dummy SQL). --UPDATE table_name SET first_col=first_col WHERE 1=1