Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    45

    Unanswered: Optimizing UPDATE logging

    Hey folks,

    I'm currently doing some research concerning UPDATE logging at DB2. I found out at IBM site that this could be done by organizing columns at the table definition, right ??

    But, I found out also that the article was at DB2 ver 9, and after I searched it at DB2 UDB 8, I can't find a thing.

    Is this "feature" only available at DB2 9 ?? And, I actually curious if DB2 9, available in z/OS and LUW ? Or just one of them ??

    Oh yeah, the organizing (CHAR and VARCHAR) seems to be able to reduce logging overhead, locks, etc. But the thing is, how ??? How actually DB2 could be optimized by only doing the organizing ?

    This question probably concerns DB2's internal/architectural parts, but if anyone happens to know, please, I'm so curious about this part.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe you could explain what exactly you are referring to? Do you mean the ROW CHANGE timestamp (as described here: http://publib.boulder.ibm.com/infoce.../r0052407.html) or something else?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think he may be talking about putting frequently updated columns (especially varchars) at the end of the row to minimize the amount of data that must be logged in an update.

    This was a concern in DB2 for z/OS, but I am not sure about current releases. This was never a concern in DB2 for LUW, because it automatically puts varchar columns at the physical end of the row, regardless of where there are logically in the create table statement.

    I don't believe that the order of the columns will affect row locking.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It has always been a recommendation to put frequently updated columns towards the end of the physical row in a table, regardless of version. This has nothing to do with locking, but rather to minimize what gets logged when an update occurs. In some places this is not a concern as they use DATA CAPTURE CHANGES when creating a table, which means the entire row is logged. In other shops they try to eek every performance gain they can get out of a system, they would ensure placement of columns in a table were optimized in order to minimize the logging as DB2 logs from the first changed byte to the end of the row. I think that part holds true on both Z/OS and LUW. In LUW and now V9 on Z/OS VARCHARS are auotmagically at the end of the row, regardless of where you state they are in the CREATE TABLE statement.
    Lastly, V9 is available on both flavors of UDB, though there are differences between the two.

    Dave Nance

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am fairly certain that DB2 for LUW put varchars at the end of the row before version 9. I recall someone from the DB2 Lab in Toronto mentioning it quite a while ago.
    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
  •