Re: change column name in sql?

On 14 May 2013 13:58, dale janus wrote:

I would like to change one of the long names into something a little
more accurate. But there appears to be no command to alter table,
alter column that applies to the column name. <<SNIP>> SQL source
statements, delete the table, change the name in the source, and
then execute the source.

Is this the preferred method or is there a better way?

Not sure how this relates to RPG, but anyhow...

When the TABLE is still under design, then that would be my preferred method. If somehow such a mistake made it into production, then I would probably just overlook the issue in the TABLE [i.e. live with the incorrect name], and optionally (re)create the logical VIEW files with the desired column name(s) to include one VIEW which is an actual or effective "SELECT *" query but having defined the proper column names.

However AFaIK the ALIAS column name is not part of the hash that defines the Record Format Level Identifier, so one could just save, DROP, CREATE with the correct column name, and then restore just the data into the new\corrected TABLE [using OPTION(*OLD) MBROPT(*OLD) ALWOBJDIF(*FILELVL); of course a bit more complicated than that, to properly effect the recovery for an effective DR of a file and its relations, which requires ownership, authority to the TABLE and any dependents as well as corrections for any dependents]. The big issue after dealing with the physical data is the dependencies; some which may be dependent specifically on the incorrect column name.

I could offer some specific scripts [probably just overlooking any failures for any dependents] to effect the re-create and data restore, but that seems best done on a more appropriate list.