How to rename a column on a DB2/400 database table ?

[ar]

Hi,

I'd like to rename a table column.
Other DBMS (Informix, PostgreSQL, ...) have something like :
ALTER TABLE my_table RENAME COLUMN old_name TO new_name;

I can't find the DB2 equivalent.

Is there another solution than :
ALTER TABLE my_table ADD COLUMN new_name ...;
UPDATE my_table SET new_name = old_name;
ALTER TABLE my_table DROP COLUMN old_name;

Regards

[JTF]

I don't think there is, so your work around, until proven otherwise,
would be the only way.

[CRPence]

For tables that are not huge [for which a somewhat more storage
efficient copy by the ALTER feature is required or desirable], a better
method might be to perform the following requests instead; performing
only one data copy, instead of two:

rename table my_table to old_table
create table my_table (x, new_name, z)
-- optionally omit unique & primary keys
insert into my_table (x, new_name, z)
select x, old_name, z from old_table
drop table old_table
/* alter table my_table add constraint
-- if omitted earlier */

Regards, Chuck

[ar]

Thanks for your answers.

Rename table + Create table + Insert into + Drop table can effectively be
a solution except that eventual foreign key constraints from other tables
on my_table's id column aren't preserved.

Regards