Rename columns

[gregwga50]

Is there a way to rename columns in a table using SQL. I saw some documentation on IBM's website showing how to do it with ALTER TABLE. When I went to try it some of the options were not available

[jj_dahlheimer]

You want the label on command. http://www.mcpressonline.com/sql/pra...and-label.html

[gregwga50]

You want the label on command.

Maybe I am doing something wrong but that just changes the text but not the field names. A little background here, I have an Excel ss that I brought down to the Iseries box using Client Access file xfer. When Creating a new file using CA xfer, it defaults the field names to F1, F2,...........Fn which you can change if you don't mind typing into that little dialogue box. I just thought it would be easier to use the CA defaults and then change them using SQL (if possible)

[arrow483]

You could just create a SQL view. Create view Nicename as (Select F1 as Company, F2 as Customer, F3 as Street from FxNames)

This gives the advantage of changing also the fields type, adding new fields or replacing values if needed.

[jj_dahlheimer]

There is a alter table rename function but I don't think db2 for i supports it or at least I couldn't find it. I did see the it for DB2 for LUW.

Couple of things I do in this situation. The view above would work well, could also create the table using the same definitions of the source file while renaming the columns on the create table command.

 create or replace table lib/newtable as (select f1 as col1 from lib/oldtable) with data on replace delete rows

Or I just create the new table ahead of the time and move the data into it.

insert into lib/newtable (select * from lib/oldtable)

Performance wise the view is probably the best bet if dealing with larger data sets. I like creating the table and moving the data into it as most of the time these are just one off transfers for me and excel can often use odd data types/column definitions.

[Rocky]

You can accomplish this by first adding a field:

ALTER TABLE TESTTABLE ADD COLUMN ASSET NUMERIC (8 , 0) NOT   
NULL WITH DEFAULT

Then using update to copy the data form old to new:

UPDATE RAMLIB/TESTTABLE SET ASSET = FANUMB

And finally dropping the old:

ALTER TABLE RAMLIB/TESTTABLE DROP COLUMN FANUMB CASCADE

You might want to put a label on the new field

LABEL ON COLUMN RAMLIB/TESTTABLE (ASSET IS 'Asset Number')   
LABEL ON COLUMN RAMLIB/TESTTABLE (ASSET TEXT IS 'Asset Number')

To put it all together:

ALTER TABLE TESTTABLE ADD COLUMN ASSET NUMERIC (8 , 0) NOT   NULL WITH DEFAULT;                                                  
UPDATE RAMLIB/TESTTABLE SET ASSET = FANUMB;
ALTER TABLE RAMLIB/TESTTABLE DROP COLUMN FANUMB CASCADE ;
LABEL ON COLUMN RAMLIB/TESTTABLE (ASSET IS 'Asset Number') ;  
LABEL ON COLUMN RAMLIB/TESTTABLE (ASSET TEXT IS 'Asset Number') ;