Re: SQL - Creating a duplicate of an existing column

Realize that the /hacky/ process is limited in function. Perhaps more aptly, there will be a [potentially significant] loss of function; e.g. ownership, authorities, RI, triggers, journaling, dependencies, identity, etc.. If such a method can safely be assumed to be sufficient, then consider that "rename" first followed by "create table as" will reduce to just one rename, and that the given process loses any data for lack of "insert into select from" or a "with data" clause on the create. Of course all activity should be under commitment control to ensure implicit rollback given any errors are encountered in the script of statements.

Regards, Chuck

Pete Helgren wrote:

"Look a little further. Then next section in the DB2 SQL
Reference covers DESCRIBE TABLE."

<<SNIP>> Meanwhile I came up with this hacky workaround but
now I'll go back and revisit the DESCRIBE TABLE function.

Pseudo Code: (Hacky workaround)

# Clone the existing table adding the aliased column
execute "create table tmp_rename as (
select #{table_name}.*,
#{table_name}.#{column_name} as #{new_column_name}
from #{table_name}) definition only"
# Drop the old column
execute "Alter table tmp_rename drop column #{column_name}"
# Rename the current table
execute "RENAME table #{table_name} to tmp_oldtbl"
# Rename the new table back to old
execute "RENAME table tmp_rename to #{table_name}"
# Drop the old table
execute "Drop table tmp_oldtbl"

The copy of the table and dropping of columns and tables and rename of tables is probably very inefficient so I will take a
look at the describe function. Of course I need to copy the data
from the old to new table in these steps as well