Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    25

    Unanswered: Adding columns to the middle of columns

    Hi, this is narasimha, how to add the columns to the middle of the table in production environment. my prodction server is db2 ese 9.7 fixpack 6.
    please help to me.


    Thanks and Regards
    Narasimha A

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Unless you want to create a new table, you cannot do that. New columns added to a table with the alter table statement will put columns at the end of the table (at least they appear to be at the end in a "select *" statement. This is only a problem for people with deep emotional and psychological problems. These persons should get over it and move on to more important things.

    However, you could rename the real table, and then create a view with the original table name that changes the 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

  3. #3
    Join Date
    Mar 2006
    Location
    Bogota, Colombia
    Posts
    21

    Improve logging

    The advantage to leave the more changing columns at the end of the row is to increase log performance.

    When you update a row, DB2 will write in the transaction log from the first modified column until the end of the row. For example if you have

    | A | BB | CCC | DDDD | EEEEE |

    and you update BB by bb, then DB2 will write this in the transaction logs

    | BB | CCC | DDDD | EEEEE |, | bb | CCC | DDDD | EEEEEE |

    As you can see, DB2 wil write many things that are common between the two rows. If you are going to do this update very frequently, you will write usless stuff. In this case it is better to move the changing column to the end in order to write less in the transaction log

    | A | CCC | DDDD | EEEEE | BB |

    Probably, Narasimha has a non changing column, and this is the reason he wants to put it in the middle. Another reason could be compression, if you put common columns together, you could improve compression.

    Finally, the only way to put a new column in the middle is by recreating the table. You could create the admin_move_table stored procedure.

    Table Movement Made Easy
    Ordering columns to minimize update logging - IBM DB2 9.7 for Linux, UNIX, and Windows
    AngocA

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by angoca View Post
    The advantage to leave the more changing columns at the end of the row is to increase log performance.

    When you update a row, DB2 will write in the transaction log from the first modified column until the end of the row. For example if you have

    | A | BB | CCC | DDDD | EEEEE |

    and you update BB by bb, then DB2 will write this in the transaction logs

    | BB | CCC | DDDD | EEEEE |, | bb | CCC | DDDD | EEEEEE |

    As you can see, DB2 wil write many things that are common between the two rows. If you are going to do this update very frequently, you will write usless stuff. In this case it is better to move the changing column to the end in order to write less in the transaction log

    | A | CCC | DDDD | EEEEE | BB |
    That sounds fine (if one is doing a lot of UPDATE's as opposed to INSERT or DELETE), but DB2 LUW puts VARCHAR columns physically at the end of the table, regardless of where they are logically in the syscat.columns catalog view. For a newly added column, this will take effect the next time the table is reorged. DB2 for z/OS added this feature in V9.

    Quote Originally Posted by angoca View Post
    Probably, Narasimha has a non changing column, and this is the reason he wants to put it in the middle. Another reason could be compression, if you put common columns together, you could improve compression.
    That is possible, but I doubt it. Probably either Narasimha or someone else wants the columns together because they logically belong together from a business point of view, nothing to do with performance. For an application with continuous availability where there is not enough time to take an outage, people just have to get over it.

    Quote Originally Posted by angoca View Post
    Finally, the only way to put a new column in the middle is by recreating the table.
    I already mentioned that.
    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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    DB2 for z/OS added this feature in V9.
    I do not think of it as a feature. Was actually pretty upset by it. We had for performance reasons placed columns in a specific order, then got our feet yanked from under us, when we reorged a table.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dav1mo View Post
    I do not think of it as a feature. Was actually pretty upset by it. We had for performance reasons placed columns in a specific order, then got our feet yanked from under us, when we reorged a table.
    I didn't say it whether it was a good or bad feature, just a feature.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by dav1mo View Post
    I do not think of it as a feature. Was actually pretty upset by it. We had for performance reasons placed columns in a specific order, then got our feet yanked from under us, when we reorged a table.
    I think this can be disabled with RRF zparm.

Posting Permissions

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