That's a good idea! That would take care of the permissions issue and identity column issue. The constraint issue still remains if that column was included in a constraint.
Hope the feature request to add ALTER RENAME capability like standard DB2 gains steam. Will definitely save the developer time in these scenarios if it is implemented.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Friday, March 4, 2016 6:52 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Rename column in SQL?
Instead of having to recompute all the security, identity attributes, etc.
You should have tried this.
Create a new table with just the desired column (both names right) and an integer.
Copy just the existing column's data, and the value of that row's identity column to this new table.
alter the original table dropping this column alter the original table adding the column back with the right names.
copy the data back from the temporary table.
If both the long name, and the short name, were wrong you might have been able to shave down a step but since one was right the complicated it just a little.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Matt Olson <Matt.Olson@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/03/2016 04:17 PM
Subject: RE: Rename column in SQL?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Nevermind. It's 2016 and apparently like many things it isn't available on
IBM i.
http://requirements.common.org/powerbase/view/ideasIBM.htm?db=ideasIBM&Where=ID%20=%20295
Sigh.
Guess I have to spend several hours doing a rename instead of 5 seconds to
script the permissions, triggers, constraints, create new table,
deactivate identity column, migrate data from old table to new table,
reactivate identity column, drop old table, do a rain dance.
-----Original Message-----
From: Matt Olson [mailto:Matt.Olson@xxxxxxxx]
Sent: Thursday, March 3, 2016 3:07 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Rename column in SQL?
I'm trying to rename a column, simple task I know. But for some reason the
following syntax is not working:
ALTER TABLE APPLICATIONS RENAME COLUMN PASSWORDHISTORYENABLED TO
PASSWORDHISTORYENABLED FOR PASSHISTEN;
I wish to rename the column (long name is PASSWORDHISTORYENABLED and is
staying the same), just trying to fix the "System Name" which is unique to
IBM I's flavor of DB2 and set it to PASSHISTEN (10 character limit).
Thoughts?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
midrange.com
