If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Add column between columns of a table NOT at the end of table, is it possible?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: canada
Posts: 230
Add column between columns of a table NOT at the end of table, is it possible?

Hi guys,
I am working with db2 v7.2 aix 5.2 , my question is;

Is it possible we add a column between existing column not at the end of table?
should I drop the table and recreate the table? please help.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,359
Seven years ago I used Informix. And in those times it was possible to add a column at a specified place in an existing table. The syntax was something like (if memory serves):

ALTER TABLE tablename
ADD COLUMN .... BEFORE columnname
;
And that was it.

Back to present day, DB2:

Now I have to copy or rename the existing table
drop it
recreate it with that extra column in a specific place
insert all the records from table_copy into the new table

Let's say there is a certain difference in efficiency and annoyance between those two DMS's for handling something as trivial as inserting a column in a specific place.

I read somewhere that in UDB 8.(1 ?, 2 ?) there is an improved Alter Table interface in the DB2 Control Center. But I don't like surprises between test environment and production environment. I like to write my scripts and test them in our test environment until they are rock solid. So when I apply them to our production environment I am 100% sure the exact same commands are issued in the exact same sequence. Something I can not be sure about if I have to do it manually in the DB2 Control Center (to err is human).

Unless you can live with the extra column being added after the last column of the table, or if you feel comfortable with manually doing things in the Command Center, you will indeed have to drop and recreate the table.

I like DB2 as a whole, but I still dislike this shortcoming.

Wim
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
The only advantage I can see in adding a column in the middle of a table is that when you use "select *", you get them columns in the order you want. I will not rant why using "select *" is bad, but you can always create a view with the columns in the order you want and "select *" from the view.

Andy
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
You cannot add columns in the middle of the table in DB2 with an alter. What you are really talking about is the "select order" and not the real order. When you create a new table, the order of the columns physically is not the same as the "select order" if you have any varchar columns (which DB2 puts at the end regardless of where they exist in the create table order of the columns).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,359
The advantage is in the logical grouping of attributes.

All our tables end with DA_CREATE, USR_CREATE, DA_LAST_MAINT, USR_LAST_MAINT.

If I would have to add a column MIDDLE_NAME to the existing PERSON table, I would like to see it shomewhere between FIRST_NAME and SURNAME, not at the end of the table after USR_LAST_MAINT.

I'm sure that in the tables you create, the attributes (columns) are logically grouped and not placed there at random.. Why wouldn't you like to keep it that way if you have to add columns to that table later ?

I don't see having to write and maintain an extra view as a good solution. It is merely a workaround for a shortcoming of the DBMS.

Wim

Quote:
Originally Posted by ARWinner
The only advantage I can see in adding a column in the middle of a table is that when you use "select *", you get them columns in the order you want. I will not rant why using "select *" is bad, but you can always create a view with the columns in the order you want and "select *" from the view.

Andy
Reply With Quote
  #6 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by Wim
It is merely a workaround for a shortcoming of the DBMS.
I would say it is the shortcoming of the data modeler

The data is physicaly on the pages that way. You're worried about meta data. Meta data is becoming more and more interywined with databases...SQL Server 2005 has done a lot with that...but even still, you can still maintain your own data dictionary for that purpose.

It still doesn't have anything to do with the order of the columns.

EDIT: You need to unload, drop and recreate, then load

What platform are you on?
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #7 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,332
Quote:
Originally Posted by Wim
If I would have to add a column MIDDLE_NAME to the existing PERSON table, I would like to see it shomewhere between FIRST_NAME and SURNAME, not at the end of the table after USR_LAST_MAINT.

I'm sure that in the tables you create, the attributes (columns) are logically grouped and not placed there at random..
I can agree that you may want to keep attributes in some logical order when you create your data model documentation. However, I cannot see what it has to do with the physical implementation, which is what ALTER TABLE statement is. I could implement a logical entity as multiple physical tables, or partition it all over the place, or normalize it 'till it aches, but it's not going to affect my logical model. Why then the logical model has to dictate physical implementation?
Reply With Quote
  #8 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
exactly...

Where's my old db2 v3.2 unleashed book?

Ah, here it is...

It even recommends that you limiy yourself to 1 varchar column, and that you place it on the end of the row...for performance reasons...
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
DB2 V 3.2 (I presume or the mainframe since there was no V3.x release on Linux, UNIX, Windows) works a little differently than the other DB2's.

DB2 for Linux, UNIX, Windows automatically puts varchar at the end of the row on a new table create. DB2 mainframe has not always worked that way, but it is possible that they changed it recently.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Netherlands
Posts: 96
Quote:
Originally Posted by Wim
I read somewhere that in UDB 8.(1 ?, 2 ?) there is an improved Alter Table interface in the DB2 Control Center. But I don't like surprises between test environment and production environment. I like to write my scripts and test them in our test environment until they are rock solid. So when I apply them to our production environment I am 100% sure the exact same commands are issued in the exact same sequence. Something I can not be sure about if I have to do it manually in the DB2 Control Center (to err is human).
As with (allmost?) all Control Center options you can click on the 'Show SQL' button to see the generated commands so you can use those in your own script.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,359
You're right about the distinction between the physical implementation and the logical model. It's not that I'd like to know or influence where DB2 physically stores the data of the added column.

I do feel very strong that technology should support "the human", not the opposite.

The DBMS should present a "logical view" of the tables and do the translation of where it actually physically stores the data. DB2 physically stores all VARCHARs at the end of the record. Yet a SELECT *, a DB2LOOK, ... will show the column at the place specified in the DLL. Why should DB2 behave like that only for VARCHARS and not for columns that are added later to the table? That's my point. Let it give a representation of the table that is more inuitive to the user.

Wim

Quote:
Originally Posted by n_i
I can agree that you may want to keep attributes in some logical order when you create your data model documentation. However, I cannot see what it has to do with the physical implementation, which is what ALTER TABLE statement is. I could implement a logical entity as multiple physical tables, or partition it all over the place, or normalize it 'till it aches, but it's not going to affect my logical model. Why then the logical model has to dictate physical implementation?
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,359
Thanks for the hint, I will give it a try.

Wim

> As with (allmost?) all Control Center options you can click on the 'Show SQL' button to see the generated commands so you can use those in your own script.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
Quote:
Originally Posted by Wim
I do feel very strong that technology should support "the human", not the opposite.
It comes down to a question of priorities and the amount of work involved to change this. If it were trivial to do, IBM would have done it long ago. Database products may differ in how easy it is to change this, depending on how they designed the internals to begin with.

Most of the DB2 enhancement priorities are determined by user groups (such as IDUG) so I would contact them if you feel strongly about it. Keep in mind that other users may have different priorities.

My own feeling about it is that people who worry about the order of the columns are obsessing about things that don't really matter. In any application program, one should specify the columns and never use "SELECT *" so you can determine the order however you want it.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #14 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Been doing a lot of sql server lately...and it's all smoke and mirrors.

If you think, that just because you can have an interface (Enterprise Manager is just an interface) that let's you drag and drop columns wherever you want, you need to take a look behind the scenes...because it does it exactly the same way....

CREATE TABLE
INSERT INTO
DROP TABLE
RENAME TABLE

NO MIRACLES
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,359
What's wrong with this solution? It takes the burden of doing it manually - and the inherent chance of mistakes - away, and it gives the user a representation of the table the way he likes it the most. As said before, how the DBMS physically takes care of this, is not important.

Just wonder how SQL Server deals with *really* big tables. The log file will overflow during the INSERT. If done manually, you can do the INSERT in a number of steps to avoid this.

Just in case you wonder, we also don't do a rename/create/insert/drop on our tables, we lazily add those extra columns at the end of the record. Only if a table must undergo such a profound reshape, that it has to be dropped anyway, will we do it the complex way.

I didn't thought this thread would turn into a kind of religious war.

Kind regards
Wim

Quote:
Originally Posted by Brett Kaiser
Been doing a lot of sql server lately...and it's all smoke and mirrors.

If you think, that just because you can have an interface (Enterprise Manager is just an interface) that let's you drag and drop columns wherever you want, you need to take a look behind the scenes...because it does it exactly the same way....

CREATE TABLE
INSERT INTO
DROP TABLE
RENAME TABLE

NO MIRACLES
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On