Re: change column name in sql?

On 14 May 2013 15:34, CRPence wrote:

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?

<<SNIP>>

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.

A reply with the scripts is offered here regardless it is off-topic.

Given:

create table TheFile (f1 char, a_wrong_name for right_name int)
;

To get the column name correction, from a_wrong_name to the_right_name exposed via a VIEW [then the applications may refer to the VIEW instead of the TABLE]:

create view TheFileV
(f1, the_right_name for right_name)
as (select * from TheFile)

A[n untested] script to get TheFile to have the long column name the_right_name instead of a_wrong_name using save\restore; incompatible dependents will not be restored:

crtsavf TheSavFile /* use other media if required\desired */
chgobjown TheSavFile *file curownaut(*revoke)
/* change owner to same as owner of the saved TheFile */
grtobjaut TheSavFile *file refobj(TheLibr/TheFile)
/* make effective backup of public and private authorities */
savobj (TheFile ...) TheLibr *savf *file savf(TheSavFile) pvtaut(*yes)
/* include all dependents in the save(s) */
drop table TheLibr/TheFile cascade
;
create table TheFile (f1 char, the_right_name for right_name int)
;
chgobjown TheLibr/TheFile *file curownaut(*revoke)
/* change owner to same as owner of the saved TheFile */
rvkobjaut TheFile *file *all *all
grtobjaut TheLibr/TheFile *file refobj(SavFile)
rstobj (TheFile ...) TheLibr *savf *file savf(TheSavFile) pvtaut(*yes) option(*all) mbropt(*all) alwobjaif(*filelvl)
/* include all dependents in the restore(s) */

Note: Each GRTOBJAUT in the above script may be unnecessary, thus removing need for the ownership change for the save file, because "If an object is being restored over an existing object on the system, ... the Private authorities (PVTAUT) parameter may add saved private authorities to the existing private authorities." I believe "may add" means to suggest if PRVAUT(*YES) was requested, thus the grant requests "would be unnecessary" vs "may be unnecessary", because the script saved the authorities and asks to restore them.

A [tested] script to get TheFile to have the long column name the_right_name instead of a_wrong_name using just SQL ALTER requests; IIRC any incompatible dependents are implicitly dropped, though notified by an inquiry of the consequence:

alter table TheFile add column a_bogus_name for bogus_name int
;
update TheFile set a_bogus_name = a_wrong_name
;
alter table TheFile drop column a_wrong_name
;
alter table TheFile add column the_right_name for right_name int
;
update TheFile set the_right_name = a_bogus_name
;
alter table TheFile drop column a_bogus_name
;

Note: the UPDATE would have to resolve any modifications to the data type and attributes as well, if changing the name was not the *only* change to the column definition.

If both the short field name and the long name (alias or "Alternative name") were incorrect, then the below script with ALTER is more succinct; e.g.:

Given:

create table TheFile (f1 char, a_wrong_name for wrong_name int)
;

Then the [untested] script to effect the column rename from the /wrong/ names to the /right/ names is just these statements:

alter table TheFile add column the_right_name for right_name int
;
update TheFile set the_right_name = a_wrong_name
;
alter table TheFile drop column a_bogus_name
;