Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: column placement

    Hi All !

    In the Db2 for LUW world, does the order of columns in a table impact performance ?
    I know that on DB2 for z/OS (mainframe), you always tried to place frequently referenced columns before VARCHAR columns. Besides, column placement also influenced the amount of data that is logged during updates.

    Are these considerations during table design for DB2 tables on Linux (or any LUW platform) ?

    Thanks
    Anil

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 for LUW, it automatically puts varchar columns at the end. I am not sure what happens when you alter a table to add a column.
    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
    Oct 2005
    Location
    pune(india)
    Posts
    24
    Quote Originally Posted by Marcus_A
    In DB2 for LUW, it automatically puts varchar columns at the end. I am not sure what happens when you alter a table to add a column.
    well if you alter the table it certainly appends the columns at the end .but i guess if the table is properly indexed the column structure will rarely be a cause of performance.

    please share your research on this.

    Cheers,
    sinwar

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sinwar
    well if you alter the table it certainly appends the columns at the end .but i guess if the table is properly indexed the column structure will rarely be a cause of performance.

    please share your research on this.

    Cheers,
    sinwar
    The issue of physical column placement has to do with logging of UPDATEs to the row in the DB2 transaction log.

    With DB2 for z/OS it is well documented that on an update, only the data changed will be logged, except that if VARCHAR data length is changed (likely any time a VARCHAR column is updated) then DB2 needs to log from the changed data to the end of the row. Therefore it is often recommended that VARCHAR columns be placed at the end of the row if they are often updated.

    In DB2 for Linux, UNIX, and Windows the logging details are less well documented, but I have heard from IBM'ers that DB2 automatically will place VARCHAR columns physically at the end of a row when a table is created, regardless of the order of the columns as listed in the sycat.columns catalog table. But I have not seen any written documentation to verify this.

    Of course, all of this only affects logging performance of updates of VARCHAR columns (for INSERTs and DELETEs the entire row is logged, and for SELECTs there is no logging). Whether any of this makes a noticeable difference in performance in a DB2 database is questionable unless there is a high transaction rate of UPDATEs to VARCHAR 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

Posting Permissions

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