RE: In-place alter table

The Documentation Notes for the Informix Migration Guide also contain the following warnings about In-Place ALTER TABLE: Modify In-Place ALTER TABLE (Version 7.24 and Later) Upgrading to the database server, Version 7.24, and later occurs automatically. However, reverting from Version 7.24 (or later) to an earlier database server version is not possible if outstanding In-Place ALTER TABLEs exist. An In- Place ALTER TABLE is outstanding when data pages exist with the old definition. If you attempt to revert to a previous version, the code checks for outstanding alter operations and lists any that it finds. You need to update every row of each table in the outstanding alter list with an alter table version and then perform the reversion. If an In-Place ALTER TABLE was performed on a table, you can convert the older version pages to the latest version by running a test UPDATE statement. For example, run the following test UPDATE statement: update tab1 set column1 = column1 For more information on In-Place ALTER TABLE, see your Performance Guide. -----Original Message----- From: Bob Davis [mailto:rdavis@rmi.net] Sent: Wednesday, February 03, 1999 13:37 To: informix-list@iiug.org Subject: Re: In-place alter table In article <36B77824.83AF8917@bellsouth.net>, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > IDS 7.23.UC11 > HPUX 10.2 > > Due to various requirements, I will need to change the length of a char > column in various tables in my system. I understand that the alter > table will make this change in-place on this version. Since I haven't > done this before, I'm wondering if there are any gotchas out there. > > 1. What are the log space requirements for a database using buffered > logging? > 2. Is is better to force completion of the physical updates to the > table? > > I've scanned Dejanews and haven't found much. > > Still sounds better than an unload, drop, recreate, reload . . . > anything you all could add would be great. > > TIA > > John Carlson > Informix DBA > WHSmith USA > I only know of one "gotcha", but it's a doozie. However, you only need to worry about it if you make copies of your database using onunload/onload. Here's the scenario: 1) Add a new column to the end of a table. 2) Create a copy of the database using onunload before the table has had a chance to be updated. 3) Attempt to create a copy of the database using onload. All the chunks in the dbspace where you are creating the table in question will be marked down. There is no way to salvage the chunks and you are forced to perform a restore. The only way to avoid this situation is to guarantee the table is updated before an onunload. The best way to do this is by performing a "dummy" update right after the column is added. UPDATE this_table SET col1 = col1; I don't know if adding a column at the end of a table is the only way to activate the bug. To be safe, if you use onunload/onload make sure you run a dummy update after any ALTER TABLE statement that makes use of the in place alter algorithm. You might also check with Informix tech support to see if the bug has been fixed in 7.23.UC11. Sorry, don't have the bug number for you to reference. Bob ------------- -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own