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.

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?

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.

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

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.