In-place alter table
Carlson@WHSmith
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
Carlson@WHSmith
expected when you modify a column definition (change from char(10) to
char(22)). Looks like the ol' unload / mod schema / recreate tables /
reload syndrome.
Are there any guidelines concerning when an alter table will do an
in-place alter?
TIA
John Carlson
Informix DBA
WHSmith USA
Carlson@WHSmith
alters and HP. Has anyone else noticed this?
Bob Davis
"Carlson@WHSmith" <carl...@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
Art S. Kagel
>
> 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.
V7.23 only does in-place alter for appended columns or dropping the
last column and a few other specific alters. V7.24 extended this and
7.3 extended it even further. I just checked and 7.24 will expand a
character column in the middle of a row in-place.
I cannot vouch for 7.23, I suspect not. If not you are left with only
two good options: 1) Unload, drop, recreate with new schema, reload; or
2) Create a new table with new schema, copy the data, drop old table,
rename new table. The latter can be done with minimal locking using
my dbcopy.ec or some similar utility.
Art S. Kagel
Bernstein, Rick
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:rda...@rmi.net]
Sent: Wednesday, February 03, 1999 13:37
To: inform...@iiug.org
Subject: Re: In-place alter table
In article <36B77824...@bellsouth.net>,
"Carlson@WHSmith" <carl...@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.
>
mos...@wellsfargo.com
See "Informix Guide to SQL: Syntax", section on "ALTER TABLE" stmt,
subsection "Scope of the In-Place Alter Algorithm" (p. 1-49 in my copy).
This is for 7.2x. I understand that 7.3x has much fewer restrictions for
getting an in-place alter to occur.
================================
Paul A. Mosser, Open Systems DBA
Informix Certified DBSA
Wells Fargo & Co.
Tempe, Arizona
paul....@wellsfargo.com
================================
-----Original Message-----
From: Carlson@WHSmith [mailto:carl...@bellsouth.net]
Sent: Wednesday, February 03, 1999 3:49 AM
To: inform...@iiug.org
Subject: Re: In-place alter table
Just started and learned that the in-place alter table will not work as
expected when you modify a column definition (change from char(10) to
char(22)). Looks like the ol' unload / mod schema / recreate tables /
reload syndrome.
Are there any guidelines concerning when an alter table will do an
in-place alter?
TIA
John Carlson
Informix DBA
WHSmith USA