Results 1 to 15 of 15
  1. #1
    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,377
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    4,130
    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. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,377
    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

  6. #6
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,427
    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?

  8. #8
    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.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  10. #10
    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.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,377
    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?

  12. #12
    Join Date
    Nov 2004
    Posts
    1,377
    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.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  14. #14
    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.

  15. #15
    Join Date
    Nov 2004
    Posts
    1,377
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •