Tags: aix, column, columns, database, db2, existing, guys, isis, mysql, oracle, sql, table, working

Add column between columns of a table NOT at the end of table, is it possible?

On Database » DB2

10,317 words with 14 Comments; publish: Thu, 06 Dec 2007 02:57:00 GMT; (250234.38, « »)

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.

All Comments

Leave a comment...

  • 14 Comments
    • 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

      #1; Tue, 11 Dec 2007 18:02:00 GMT
    • 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

      #2; Tue, 11 Dec 2007 18:03:00 GMT
    • 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).
      #3; Tue, 11 Dec 2007 18:04:00 GMT
    • 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

      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

      #4; Tue, 11 Dec 2007 18:05:00 GMT
    • 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?

      #5; Tue, 11 Dec 2007 18:06:00 GMT
    • 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?

      #6; Tue, 11 Dec 2007 18:07:00 GMT
    • 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...

      #7; Tue, 11 Dec 2007 18:08:00 GMT
    • 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.

      #8; Tue, 11 Dec 2007 18:09:00 GMT
    • 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.

      #9; Tue, 11 Dec 2007 18:10:00 GMT
    • 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

      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?

      #10; Tue, 11 Dec 2007 18:11:00 GMT
    • 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.

      #11; Tue, 11 Dec 2007 18:12:00 GMT
    • 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.

      #12; Tue, 11 Dec 2007 18:13:00 GMT
    • 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

      #13; Tue, 11 Dec 2007 18:14:00 GMT
    • 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

      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

      #14; Tue, 11 Dec 2007 18:15:00 GMT