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