In Place Alters and Upgrade plans

IDS 11.50.FC6 HP-UX 11.31 PA-RISC As you can see above, we're on 11.50, and needing to upgrade before end-of-life. Because we're on PA-RISC, we can only upgrade to 11.70. Looking at the Migration manual, it states that best practice calls for removing any outstanding in-place alters prior to upgrading. The manual includes a link to a support article (http://www-01.ibm.com/support/docview.wss?uid=swg21144602) that details how to identify outstanding in-place alters via the 'oncheck -pT' command, looking at the Home Data Page Version Summary of that output. The support article above includes a link to another support article (http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923) that includes a shell script, inplacealter.sh, that is supposed to automate this process. The article includes a disclaimer that the script is not supported by Informix Technical Support. I have downloaded the script and tried to run it, but it has problems. For instance, I found at least three places in the script where it referenced a shell variable by name, but without the leading '$'. For instance, there was a while statement: while [ startbytes -lt numbytes ] instead of while [ $startbytes -lt $numbytes ] After making changes to add the '$' to the places that I spotted, I ran the script against a single table. It tells me that there are outstanding in-place alters, even though the manual method detailed in the first support article tells me that there are no outstanding alters, as all pages are shown as the '(current)' version. Even more, the script gives me an UPDATE statement to run to fix these in-place alters. I run the UPDATE, then re-run the script, and get the same results. Further, there seems to be an issue with the section that sets the shell variable 'count' (line 212). Sometimes, the variable ends up with an empty value, which causes problems a few lines later when it tries to pipe a command to the dc command, and also when it does let sum=$sum+$count So, my question (finally) is - does anyone have a debugged version of this script that they can share? Or an alternate automated method of identifying tables with in-place alters? Or is the documentation overstating the necessity of clearing the outstanding in-place alters? Thanks in advance.

AFAIK version 10 eliminated the need for eliminating outstanding in-place alters. If I recall correctly, the problem was not (never was) the upgrade, but a possible later reversion. Since that v10 change pre-existing outstanding IPAs, from before an upgrade, would not hinder a later reversion. Different story, though, for new IPAs (new ALTER TABLEs) introduced after upgrade. In case you just want to be in the know wrt. outstanding IPAs, I'd recommend https://github.com/domusonline/InformixScripts/tree/master/queries HTH, Andreas From: "MARK COLLINS" <markc@myfastmail.com> To: ids@iiug.org Date: 11/29/2017 03:57 PM Subject: In Place Alters and Upgrade plans [40294] Sent by: ids-bounces@iiug.org IDS 11.50.FC6 HP-UX 11.31 PA-RISC As you can see above, we're on 11.50, and needing to upgrade before end-of-life. Because we're on PA-RISC, we can only upgrade to 11.70. Looking at the Migration manual, it states that best practice calls for removing any outstanding in-place alters prior to upgrading. The manual includes a link to a support article (http://www-01.ibm.com/support/docview.wss?uid=swg21144602) that details how to identify outstanding in-place alters via the 'oncheck -pT' command, looking at the Home Data Page Version Summary of that output. The support article above includes a link to another support article (http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923) that includes a shell script, inplacealter.sh, that is supposed to automate this process. The article includes a disclaimer that the script is not supported by Informix Technical Support. I have downloaded the script and tried to run it, but it has problems. For instance, I found at least three places in the script where it referenced a shell variable by name, but without the leading '$'. For instance, there was a while statement: while [ startbytes -lt numbytes ] instead of while [ $startbytes -lt $numbytes ] After making changes to add the '$' to the places that I spotted, I ran the script against a single table. It tells me that there are outstanding in-place alters, even though the manual method detailed in the first support article tells me that there are no outstanding alters, as all pages are shown as the '(current)' version. Even more, the script gives me an UPDATE statement to run to fix these in-place alters. I run the UPDATE, then re-run the script, and get the same results. Further, there seems to be an issue with the section that sets the shell variable 'count' (line 212). Sometimes, the variable ends up with an empty value, which causes problems a few lines later when it tries to pipe a command to the dc command, and also when it does let sum=$sum+$count So, my question (finally) is - does anyone have a debugged version of this script that they can share? Or an alternate automated method of identifying tables with in-place alters? Or is the documentation overstating the necessity of clearing the outstanding in-place alters? Thanks in advance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Andreas, I believe you are correct about the original problem being one of reversion to the earlier release. And the document (https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.mig.doc/ids_ mig_015.htm) does say that "removing in-place alters is not required before upgrading, a best practice is to remove all in-place alters before upgrading." I will look at function that you suggested in GitHub. Thanks. ========================================== AFAIK version 10 eliminated the need for eliminating outstanding in-place alters. If I recall correctly, the problem was not (never was) the upgrade, but a possible later reversion. Since that v10 change pre-existing outstanding IPAs, from before an upgrade, would not hinder a later reversion. Different story, though, for new IPAs (new ALTER TABLEs) introduced after upgrade. In case you just want to be in the know wrt. outstanding IPAs, I'd recommend https://github.com/domusonline/InformixScripts/tree/master/queries HTH, Andreas

I have the same idea. The reason to remove the IPAs was to prevent issues if you wanted to rollback the upgrade. To be honest the only time I did reversions was on customer workshops... Never in a real situation. Having said that, the different behaviors between the script (stating you have IPAs) and the other method (showing you have not) is relatively simple and should be explained in the links you mention: The "oncheck -pT" shows if there are any pages (which is the "real" thing) but is painfully slow. The other method (script) would tell you if the table *had* any IPAs.... and that method if fast. What Andreas showed is an attempt to join both things: Getting the correct result, amazingly fast. It was an effort with the collaboration of Art Kagel and Andreas Legner (hope I'm not forgetting someone). It should work. In case it doesn't please return here and explain the issue. I'll do my best to fix it. Regards. On Wed, Nov 29, 2017 at 10:40 PM, Andreas Legner1 < Andreas.Legner1@de.ibm.com> wrote: > AFAIK version 10 eliminated the need for eliminating outstanding in-place > alters. > > If I recall correctly, the problem was not (never was) the upgrade, but a > possible later reversion. Since that v10 change pre-existing outstanding > IPAs, from before an upgrade, would not hinder a later reversion. > Different story, though, for new IPAs (new ALTER TABLEs) introduced after > upgrade. > > In case you just want to be in the know wrt. outstanding IPAs, I'd > recommend > https://github.com/domusonline/InformixScripts/tree/master/queries > > HTH, > Andreas > > From: "MARK COLLINS" <markc@myfastmail.com> > To: ids@iiug.org > Date: 11/29/2017 03:57 PM > Subject: In Place Alters and Upgrade plans [40294] > Sent by: ids-bounces@iiug.org > > IDS 11.50.FC6 > HP-UX 11.31 PA-RISC > > As you can see above, we're on 11.50, and needing to upgrade before > end-of-life. Because we're on PA-RISC, we can only upgrade to 11.70. > > Looking at the Migration manual, it states that best practice calls for > removing any outstanding in-place alters prior to upgrading. The manual > includes a link to a support article > (http://www-01.ibm.com/support/docview.wss?uid=swg21144602) that details > how > to identify outstanding in-place alters via the 'oncheck -pT' command, > looking > at the Home Data Page Version Summary of that output. > > The support article above includes a link to another support article > (http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923) that > includes > a shell script, inplacealter.sh, that is supposed to automate this process. > > The article includes a disclaimer that the script is not supported by > Informix > Technical Support. > > I have downloaded the script and tried to run it, but it has problems. For > instance, I found at least three places in the script where it referenced a > > shell variable by name, but without the leading '$'. For instance, there > was a > while statement: > > while [ startbytes -lt numbytes ] > > instead of > > while [ $startbytes -lt $numbytes ] > > After making changes to add the '$' to the places that I spotted, I ran the > > script against a single table. It tells me that there are outstanding > in-place > alters, even though the manual method detailed in the first support article > > tells me that there are no outstanding alters, as all pages are shown as > the > '(current)' version. > > Even more, the script gives me an UPDATE statement to run to fix these > in-place alters. I run the UPDATE, then re-run the script, and get the same > > results. > > Further, there seems to be an issue with the section that sets the shell > variable 'count' (line 212). Sometimes, the variable ends up with an empty > value, which causes problems a few lines later when it tries to pipe a > command > to the dc command, and also when it does > > let sum=$sum+$count > > So, my question (finally) is - does anyone have a debugged version of this > script that they can share? Or an alternate automated method of identifying > > tables with in-place alters? > > Or is the documentation overstating the necessity of clearing the > outstanding > in-place alters? > > Thanks in advance. > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

As a side note... IPAs are a DBA life saver... Many schema changes are made very quick because of them. It's a great feature that I'd love to see improved (by removing some exceptions wherever possible). However, it's also true that over time (decades) some bugs have only happened on tables with IPA... Not because an IPA is a bad thing, but simply because it makes the code more complex. It's really magical what the engine has to do on tables with IPA... So, what I'm trying to say is that we can take advantage of the IPAs to do really fast DDL changes, and then identify them and slowly trying to remove the old version pages by doing some updates. I think someone wrote an article about this... basically trying to update a single row in each page with an old version. This can be done in backgroud without any downtime... Regards. On Wed, Nov 29, 2017 at 11:24 PM, Fernando Nunes <domusonline@gmail.com> wrote: > I have the same idea. The reason to remove the IPAs was to prevent issues > if you wanted to rollback the upgrade. To be honest the only time I did > reversions was on customer workshops... Never in a real situation. > Having said that, the different behaviors between the script (stating you > have IPAs) and the other method (showing you have not) is relatively simple > and should be explained in the links you mention: > > The "oncheck -pT" shows if there are any pages (which is the "real" thing) > but is painfully slow. The other method (script) would tell you if the > table *had* any IPAs.... and that method if fast. > > What Andreas showed is an attempt to join both things: Getting the correct > result, amazingly fast. It was an effort with the collaboration of Art > Kagel and Andreas Legner (hope I'm not forgetting someone). > It should work. In case it doesn't please return here and explain the > issue. I'll do my best to fix it. > > Regards. > > On Wed, Nov 29, 2017 at 10:40 PM, Andreas Legner1 < > Andreas.Legner1@de.ibm.com> wrote: > > > AFAIK version 10 eliminated the need for eliminating outstanding in-place > > alters. > > > > If I recall correctly, the problem was not (never was) the upgrade, but a > > possible later reversion. Since that v10 change pre-existing outstanding > > IPAs, from before an upgrade, would not hinder a later reversion. > > Different story, though, for new IPAs (new ALTER TABLEs) introduced after > > upgrade. > > > > In case you just want to be in the know wrt. outstanding IPAs, I'd > > recommend > > https://github.com/domusonline/InformixScripts/tree/master/queries > > > > HTH, > > Andreas > > > > From: "MARK COLLINS" <markc@myfastmail.com> > > To: ids@iiug.org > > Date: 11/29/2017 03:57 PM > > Subject: In Place Alters and Upgrade plans [40294] > > Sent by: ids-bounces@iiug.org > > > > IDS 11.50.FC6 > > HP-UX 11.31 PA-RISC > > > > As you can see above, we're on 11.50, and needing to upgrade before > > end-of-life. Because we're on PA-RISC, we can only upgrade to 11.70. > > > > Looking at the Migration manual, it states that best practice calls for > > removing any outstanding in-place alters prior to upgrading. The manual > > includes a link to a support article > > (http://www-01.ibm.com/support/docview.wss?uid=swg21144602) that details > > how > > to identify outstanding in-place alters via the 'oncheck -pT' command, > > looking > > at the Home Data Page Version Summary of that output. > > > > The support article above includes a link to another support article > > (http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923) that > > includes > > a shell script, inplacealter.sh, that is supposed to automate this > process. > > > > The article includes a disclaimer that the script is not supported by > > Informix > > Technical Support. > > > > I have downloaded the script and tried to run it, but it has problems. > For > > instance, I found at least three places in the script where it > referenced a > > > > shell variable by name, but without the leading '$'. For instance, there > > was a > > while statement: > > > > while [ startbytes -lt numbytes ] > > > > instead of > > > > while [ $startbytes -lt $numbytes ] > > > > After making changes to add the '$' to the places that I spotted, I ran > the > > > > script against a single table. It tells me that there are outstanding > > in-place > > alters, even though the manual method detailed in the first support > article > > > > tells me that there are no outstanding alters, as all pages are shown as > > the > > '(current)' version. > > > > Even more, the script gives me an UPDATE statement to run to fix these > > in-place alters. I run the UPDATE, then re-run the script, and get the > same > > > > results. > > > > Further, there seems to be an issue with the section that sets the shell > > variable 'count' (line 212). Sometimes, the variable ends up with an > empty > > value, which causes problems a few lines later when it tries to pipe a > > command > > to the dc command, and also when it does > > > > let sum=$sum+$count > > > > So, my question (finally) is - does anyone have a debugged version of > this > > script that they can share? Or an alternate automated method of > identifying > > > > tables with in-place alters? > > > > Or is the documentation overstating the necessity of clearing the > > outstanding > > in-place alters? > > > > Thanks in advance. > > > > > > ************************************************************ > > ******************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

RFC 113669 submitted for a command to resolve in-place alters including as an online operation. http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=113669 Regards, David. > On 29 November 2017 at 22:30 Fernando Nunes <domusonline@gmail.com> wrote: > > > As a side note... > IPAs are a DBA life saver... Many schema changes are made very quick > because of them. It's a great feature that I'd love to see improved (by > removing some exceptions wherever possible). > > However, it's also true that over time (decades) some bugs have only > happened on tables with IPA... Not because an IPA is a bad thing, but > simply because it makes the code more complex. It's really magical what the > engine has to do on tables with IPA... > So, what I'm trying to say is that we can take advantage of the IPAs to do > really fast DDL changes, and then identify them and slowly trying to remove > the old version pages by doing some updates. I think someone wrote an > article about this... basically trying to update a single row in each page > with an old version. This can be done in backgroud without any downtime... > > Regards. > > On Wed, Nov 29, 2017 at 11:24 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > I have the same idea. The reason to remove the IPAs was to prevent issues > > if you wanted to rollback the upgrade. To be honest the only time I did > > reversions was on customer workshops... Never in a real situation. > > Having said that, the different behaviors between the script (stating you > > have IPAs) and the other method (showing you have not) is relatively simple > > and should be explained in the links you mention: > > > > The "oncheck -pT" shows if there are any pages (which is the "real" thing) > > but is painfully slow. The other method (script) would tell you if the > > table *had* any IPAs.... and that method if fast. > > > > What Andreas showed is an attempt to join both things: Getting the correct > > result, amazingly fast. It was an effort with the collaboration of Art > > Kagel and Andreas Legner (hope I'm not forgetting someone). > > It should work. In case it doesn't please return here and explain the > > issue. I'll do my best to fix it. > > > > Regards. > > > > On Wed, Nov 29, 2017 at 10:40 PM, Andreas Legner1 < > > Andreas.Legner1@de.ibm.com> wrote: > > > > > AFAIK version 10 eliminated the need for eliminating outstanding in-place > > > alters. > > > > > > If I recall correctly, the problem was not (never was) the upgrade, but a > > > possible later reversion. Since that v10 change pre-existing outstanding > > > IPAs, from before an upgrade, would not hinder a later reversion. > > > Different story, though, for new IPAs (new ALTER TABLEs) introduced after > > > upgrade. > > > > > > In case you just want to be in the know wrt. outstanding IPAs, I'd > > > recommend > > > https://github.com/domusonline/InformixScripts/tree/master/queries > > > > > > HTH, > > > Andreas > > > > > > From: "MARK COLLINS" <markc@myfastmail.com> > > > To: ids@iiug.org > > > Date: 11/29/2017 03:57 PM > > > Subject: In Place Alters and Upgrade plans [40294] > > > Sent by: ids-bounces@iiug.org > > > > > > IDS 11.50.FC6 > > > HP-UX 11.31 PA-RISC > > > > > > As you can see above, we're on 11.50, and needing to upgrade before > > > end-of-life. Because we're on PA-RISC, we can only upgrade to 11.70. > > > > > > Looking at the Migration manual, it states that best practice calls for > > > removing any outstanding in-place alters prior to upgrading. The manual > > > includes a link to a support article > > > (http://www-01.ibm.com/support/docview.wss?uid=swg21144602) that details > > > how > > > to identify outstanding in-place alters via the 'oncheck -pT' command, > > > looking > > > at the Home Data Page Version Summary of that output. > > > > > > The support article above includes a link to another support article > > > (http://www-01.ibm.com/support/docview.wss?rs=0&uid=swg21160923) that > > > includes > > > a shell script, inplacealter.sh, that is supposed to automate this > > process. > > > > > > The article includes a disclaimer that the script is not supported by > > > Informix > > > Technical Support. > > > > > > I have downloaded the script and tried to run it, but it has problems. > > For > > > instance, I found at least three places in the script where it > > referenced a > > > > > > shell variable by name, but without the leading '$'. For instance, there > > > was a > > > while statement: > > > > > > while [ startbytes -lt numbytes ] > > > > > > instead of > > > > > > while [ $startbytes -lt $numbytes ] > > > > > > After making changes to add the '$' to the places that I spotted, I ran > > the > > > > > > script against a single table. It tells me that there are outstanding > > > in-place > > > alters, even though the manual method detailed in the first support > > article > > > > > > tells me that there are no outstanding alters, as all pages are shown as > > > the > > > '(current)' version. > > > > > > Even more, the script gives me an UPDATE statement to run to fix these > > > in-place alters. I run the UPDATE, then re-run the script, and get the > > same > > > > > > results. > > > > > > Further, there seems to be an issue with the section that sets the shell > > > variable 'count' (line 212). Sometimes, the variable ends up with an > > empty > > > value, which causes problems a few lines later when it tries to pipe a > > > command > > > to the dc command, and also when it does > > > > > > let sum=$sum+$count > > > > > > So, my question (finally) is - does anyone have a debugged version of > > this > > > script that they can share? Or an alternate automated method of > > identifying > > > > > > tables with in-place alters? > > > > > > Or is the documentation overstating the necessity of clearing the > > > outstanding > > > in-place alters? > > > > > > Thanks in advance. > > > > > > > > > ************************************************************ > > > ******************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response i

Fernando, It would be a fantastic improvement if it were possible to update a single row on a prior-version page. Obviously, this could dramatically reduce the number of updates, less logging, faster execution, etc. I need to look at your SPL function in more detail to learn how it identifies specific pages and versions, but I'm assuming that the same logic could be used to identify which pages need to be updated. But how would one identify which page a given row resides on? >> So, what I'm trying to say is that we can take advantage of the IPAs to >> do really fast DDL changes, and then identify them and slowly trying to >> remove the old version pages by doing some updates. I think someone wrote >> an article about this... basically trying to update a single row in each >> page with an old version. This can be done in background without any >> downtime...

Fernando, >> The "oncheck -pT" shows if there are any pages (which is the "real" thing) >> but is painfully slow. The other method (script) would tell you if the >> table *had* any IPAs.... and that method if fast. I would question the assertion that the script is fast. It took over 4 hours to run against a single database of about 950 tables. But I understand the difference that you are describing. If that was documented at the links, I missed it. >> It should work. In case it doesn't please return here and explain the >> issue. I'll do my best to fix it. I ran it yesterday, with great results. In just a couple of minutes, it scanned the entire instance (over 200 databases) and identified about 150 IPAs. Some of those were cases where a single table had multiple outstanding IPAs. Thank you for providing this function.

I don't think the exact same logic can be used, as I think we're extracting this from the partition header page. To identify the specific pages I think a full scan is needed. However, if we have a list of pages (or if we're browsing through them) we can obtain a single ROWID which is used in that page and do an UPDATE on it. A ROWID can be calculated with the page number and a slot number (sysmaster.sysslttab). I really think someone already did some work about this... Maybe Ben Thomson or Andrew Ford? Regards. On Thu, Nov 30, 2017 at 4:28 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Fernando, > > It would be a fantastic improvement if it were possible to update a single > row > on a prior-version page. Obviously, this could dramatically reduce the > number > of updates, less logging, faster execution, etc. > > I need to look at your SPL function in more detail to learn how it > identifies > specific pages and versions, but I'm assuming that the same logic could be > used to identify which pages need to be updated. > > But how would one identify which page a given row resides on? > > >> So, what I'm trying to say is that we can take advantage of the IPAs to > >> do really fast DDL changes, and then identify them and slowly trying to > >> remove the old version pages by doing some updates. I think someone > wrote > >> an article about this... basically trying to update a single row in each > >> page with an old version. This can be done in background without any > >> downtime... > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...