IBM MAINFRAME HELP & SUPPORT FORUMS

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

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

dbzTHEdinosauer · Joined: 20 Oct 2006 Posts: 1618 Location: germany

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

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

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.

Suresh Ponnusamy · Joined: 22 Feb 2008 Posts: 92 Location: New York

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.

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

Thanks.
I suppose the performance will not be affected one way or the other.

Suresh Ponnusamy · Joined: 22 Feb 2008 Posts: 92 Location: New York

Yes.. Performance would not be different in either of the way.

dbzTHEdinosauer · Joined: 20 Oct 2006 Posts: 1618 Location: germany

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.

Antonio Barata · Joined: 04 Apr 2007 Posts: 36 Location: Lisbon, Portugal

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.