Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Adding column in between a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sunnyk

New User


Joined: 20 Oct 2004
Posts: 59

PostPosted: Wed Dec 08, 2004 8:15 pm    Post subject: Adding column in between a table
Reply with quote

Hi all,
Suppose there are 10 columns in a table and i want to insert a new column between position say 5 and 6.How to do it?
I think we can`t use alter table command for this.

Is this a solution:
Taking image copy of the table and then dropping a table,redefinining and then reloading with new columns(now 11)?????
If there is any simple solution please update me.
Back to top
View user's profile Send private message

jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Wed Dec 08, 2004 10:59 pm    Post subject:
Reply with quote

yes what you said is the solution, the details process of this, I have answered before.
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Thu Dec 09, 2004 11:14 am    Post subject: Here is a simple solution..
Reply with quote

Hi,

You can do this in the following way...

1. Add new column using alter table that will be added at last position(11th)

2. Create a view by taking the above table as base table and 11th column as 5th column.
3. Use the view instead of table thats it.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jul 06, 2006 3:03 pm    Post subject:
Reply with quote

Hi,
I couldn't really understand.
In which situation you would like to insert a column between existing columns. Can we live with ALTER TABLE to add a new column at the end.?
Back to top
View user's profile Send private message
karthi1

New User


Joined: 07 Jul 2006
Posts: 2
Location: Bangalore

PostPosted: Fri Jul 07, 2006 11:27 am    Post subject:
Reply with quote

Hi Parikshit,

There are situations where u need to add column in the middle of the table.The best solution is given by sunny itself which is to take backup,drop and recreate the table.For adding a new column at the end can be dont using ALTER TABLE statement itself.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Fri Jul 07, 2006 2:24 pm    Post subject:
Reply with quote

Hi,

I really can't think of any situation wherein you might need to add a column between two columns.

I would appriciate if you can describe at least one situation.

well, solution described by ovreddy seems fine. But there is a problem in that. What if we want to update that view? Please note that not all the views are updatable.

Thanks,
parikshit
Back to top
View user's profile Send private message
honeypria567

New User


Joined: 20 Nov 2006
Posts: 14
Location: chennai

PostPosted: Mon Jan 08, 2007 3:50 pm    Post subject:
Reply with quote

hi,

The best solution is given by sunny and it is the safest way to add the

column.
Back to top
View user's profile Send private message
dharmendra_kp

New User


Joined: 10 Mar 2005
Posts: 33
Location: Lewiston

PostPosted: Mon Jan 08, 2007 5:17 pm    Post subject:
Reply with quote

I too agree with Parikshit. How does it matter if you add the column at the end or b/w 5th & 6th column. I don't think now a days any of the program does SELECT * FROM TAB. Yes I have seen some utility jobs that unloads the table containt in a PS file that file using SELECT *, that may in turn be used as input to another pgm here too you need to just change the file structure such that you place the new column field at end.
Back to top
View user's profile Send private message
dharmendra_kp

New User


Joined: 10 Mar 2005
Posts: 33
Location: Lewiston

PostPosted: Mon Jan 08, 2007 5:19 pm    Post subject:
Reply with quote

If anyone visualizes a situation wherein you need to put a column b/w existing columns please let the forum know.

Thanks.
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Tue Jan 09, 2007 12:52 pm    Post subject: Re: Adding column in between a table
Reply with quote

sunny,
Unload the data (instead of taking the image copy).then drop and recreate the table incase you want to add new column in the middle.

Image copy won't be useful in this case since table structure got changed.


Regards
Rags
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Wed Jan 10, 2007 8:02 pm    Post subject:
Reply with quote

Hi Dharmendra,

Here I am visualizing one simple situation where it is necessary to insert a new column between the existing columns.


Let us consider that we are areating a EMPLOYEE table. In that the required fields are as follows:


First_name
Last_name
Join_Date
Emp_ID
Experience
Salary
Mail_Id
Comm_Address
Parmanent_Address
Cell_Number.


I have created the EMPLOYEE table with these fields, After some time I got confused from the huge database I like to add Middle_name in between First_name and Last_name. In this situation we can't put this field in the last postition. We need to insert this column only at the 2nd postition. Then only it looks good.


If anything wrong in my answer please correct me.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Jan 11, 2007 3:14 am    Post subject: Re: Adding column in between a table
Reply with quote

mkk157,

One of the real beauties of DB2 is the independence between the users/programmers view of the data, and the physical layout of the data. There is no real reason to insert a column in between two existing columns, in the physical sense. As ?ovreddy? suggested, the correct way is to create a view of the order you want the columns returned in, and then use that. As a user/programmer, you should not encumber yourself with the physical properties of the table.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Jan 11, 2007 3:54 am    Post subject:
Reply with quote

Dave,
DavidatK wrote:
One of the real beauties of DB2 is the independence between the users/programmers view of the data, and the physical layout of the data. There is no real reason to insert a column in between two existing columns, in the physical sense.
I absolutely agree with you, but, there again is the "elegance" of it, as
mkk157 wrote:
Then only it looks good.
....sigh.... icon_wink.gif

Bill
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us