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
 
Addin a column to a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 1:47 pm    Post subject: Addin a column to a table
Reply with quote

Hello
I have a table that has a VARCHAR column as the last one.
Now I need to add a new column to that table. It will be a unique INDEX used very often.
The question is: Should I add it to the end or, put it just before the VARCHAR?

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

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Wed Jul 09, 2008 2:06 pm    Post subject:
Reply with quote

how would you 'put it before the varchar column'?
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 2:10 pm    Post subject:
Reply with quote

Quote:
how would you 'put it before the varchar column'?

I need to DROP the table, and CREATE it again. If I put it at the end, I only need an ALTER TABLE.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Jul 09, 2008 7:47 pm    Post subject:
Reply with quote

Hi

Putting at the last will reduce lot of your efforts.

Advantages:

1) You do not need to drop the table. As you said you can add using Alter command
2) Also, if the table has data, then you will lose all the data, if you drop it.
3) If you are UNLOADing the data and after making changes to the table, you cannot load the same data in to the table using LOAD utility since a column is added in the middle.
4) Code change will be minimal.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 7:59 pm    Post subject: Reply to: Addin a column to a table
Reply with quote

Thanks.
I suppose the performance will not be affected one way or the other.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Jul 09, 2008 8:05 pm    Post subject:
Reply with quote

Yes.. Performance would not be different in either of the way.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Wed Jul 09, 2008 8:26 pm    Post subject:
Reply with quote

IBM suggests always putting VARCHAR at end. So, I image there will be some performance impact if the new column is at the end.

if nothing else, you can not always insure that you will not have a tablescan on the new (at the end) column, which by definition is in a 'variable' place. db2 can handle it, just requires a little more code.


Loading a new table, after unloading, dropping, creating is not impossible, actually it is not that hard--------otherwise no one would drop/create w/new column.

I am not a dba, you need to speak to your dba about the easiest way to repopulate your new table.

If you are really stuck, create a table under your userid that matches the old.
Code:

INSERT into yours
select *
from old

then drop the old, create a new, 
 
INSERT into new
 Select col1
        , col2
        , col3
        , literal for new column
        , col4
from your table.


When this is migrated to production, let the dba's worry about the new table.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Wed Jul 09, 2008 9:01 pm    Post subject: Reply to: Addin a column to a table
Reply with quote

I'm not worried about preserving the contents of the table.
One way or the other, I will migrate the data.
My only concern is with performance.
The column will be a unique index, and will be used as a condition and also in a "order by" clause in a cursor.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 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

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