When you define columns using the CREATE TABLE statement,
consider the order of the columns, particularly for update-intensive
workloads. Columns which are updated frequently should be grouped
together, and defined toward or at the end of the table definition.
This results in better performance, fewer bytes logged, and fewer
log pages written, as well as a smaller active log space requirement
for transactions performing a large number of updates.
The database manager does not automatically assume that columns
specified in the SET clause of an UPDATE statement are changing in
value. In order to limit index maintenance and the amount of the row
which needs to be logged, the database compares the new column value
against the old column value to determine if the column is changing.
Only the columns that are changing in value are treated as being updated.
Exceptions to this UPDATE behavior occur for columns where the data
is stored outside of the data row (long, LOB, ADT, and XML column
types), or for fixed-length columns when the registry variable DB2ASSUMEUPDATE
is enabled. For these exceptions, the column value is assumed to be
changing so no comparison will be made between the new and old column
value.
There are four different types of UPDATE log records.
- Full before and after row image logging. The entire before and
after image of the row is logged. This is the only type of logging
performed on tables enabled with DATA CAPTURE CHANGES, and results
in the most number of bytes being logged for an update to a row.
- Full before row image, changed bytes, and for size increasing
updates the new data appended to end of the row. This is logged for
databases supporting Currently Committed when DATA CAPTURE CHANGES
is not in effect for the table, when update is the first action against
this row for a transaction. This logs the before image required for
Currently Committed and the minimum required on top of that for redo/undo.
Ordering frequently updated columns at the end minimizes the logging
for the changed portion of the row.
- Full XOR logging. The XOR differences between the before and after
row images, from the first byte that is changing until the end of
the smaller row, then any residual bytes in the longer row. This results
in less logged bytes than the full before and after image logging,
with the number of bytes of data beyond the log record header information
being the size of the largest row image.
- Partial XOR logging. The XOR differences between the before and
after row images, from the first byte that is changing until the last
byte that is changing. Byte positions can be first or last bytes of
a column. This results in the least number of bytes being logged and
the most efficient type of log record for an update to a row.
For the first two types of UPDATE log records listed previously,
when DATA CAPTURE CHANGES is not enabled on the table, the amount
of data that is logged for an update depends on:
- The proximity of the updated columns (COLNO)
- Whether the updated columns are fixed in length or variable length
- Whether row compression (COMPRESS YES) is enabled
When the total length of the row is not changing, even when row
compression is enabled, the database manager computes and writes the
optimal partial XOR log record.
When the total length of the row is changing, which is common when
variable-length columns are updated and row compression is enabled,
the database manager determines which byte is first to be changed
and write a full XOR log record.
DB2 Version 10.1 for Linux, UNIX, and Windows