Re: Complete in-place alters

2008/10/30 Habichtsberg, Reinhard <RHabichtsberg@arz-emmendingen.de>: > 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. > > The database servers are in 7x24 production. Now I'm looking for the best > way to do what have to be done. But there are some ambiguity: > > - What happens if the a tables is updated with a fake update? Mostly the > rows have been grown, e.g. could it happen, that one page can hold two rows > of the former version but only one of the current. I imagine that the > distribution of the table becomes rather scattered. > - If I use HPL in nonconversation mode to unload/load a table: Will the > in-place alters are completed in the new table? > > What strategy is recommended to do the job under the given conditions? Is > there a better way than the above mentioned? > > TIA, Reinhard. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Reinhard Some random musings. Anything that unloads/loads the data will obviously write all records in the latest format as that will be the table layout to whicj it is writing. The issue is that you are going to needs somewhere in which to place the unloaded data, also (possibly quite a lot) of down time to unload, delete, reload and re-index the data. A fake update will sort the in-place alters out with no down-time and possibly minimal impact on the users, especially if it is done in manageable chunks. The disadvantage is that records may indeed be relocated if they will not fit on their existing page in the new format, the fact the data becomes 'scattered' is not an issue in itself (unless you are doing a large number of serial reads) because that is what indexes are for, however the physicl relocation will take time and will require index updates. You would be better dropping most indexes and then recreating after, however this then requires down-time and you come back to, is it quicker to unload, reload and reindex. Keith