LOB manager log records

The large object (LOB) manager marks LOB update and insert log records as propagatable when the table has DATA CAPTURE CHANGES enabled. Prior to version 10.1, these log records were only written to the log stream but they were never marked as propagatable. The information in LOB manager log records can be used by replication solutions to replicate LOB columns.

Update and insert LOB log records appear before the DMS row log record, which makes it difficult for replication solutions to track and apply the changes. As a result, there is a DMS log record that marks the beginning of these out-of-row data log records. This DMS log record is an UNDO only log record; when it is rolled back, an informational compensation log record is written out. The compensation log record is also marked as propagatable

All LOB manager log records begin with a header. When a table has been altered to enable DATA CAPTURE CHANGES, the following things occur:
  • The LOB manager writes the appropriate LOB log record.
  • When LOB data is updated, one of two things happen:
    • If the update is not a concatenation, the update is treated as a delete of the old LOB value, followed by an insert of the new LOB value; however, the delete operation is not propagated. To determine whether or not an add LOB data record is associated with an update operation on the table, the original operation value is logged to the LOB manager log record.
    • If the update is a concatenation, the LOB manager logs this operation by logging an add LOB data log record of the data being concatenated to the LOB data.
  • The LOB manager might write multiple add LOB data log records for one insert or update operation. This can happen in the following cases:
    • The column was defined with the COMPACT option
    • The LOB data being inserted is greater than 32 KB in length
  • The LOB manager never writes log records for zero length LOB data that is being insert or updated. If the LOB data can be inlined, then the LOB data is stored in the DMS row, so no LOB log record is logged in that case.
  • If the NOT LOGGED option is specified for a LOB column, an Add LOB amount record is logged, and propagated, in place of each Add LOB data record that would have been logged for that particular column.

LOB manager log record header (LOBLogRecordHeader)

Table 1. LOB manager log record header (LOBLogRecordHeader)
Description Type Offset (Bytes)
Originator code (component identifier = 5) unsigned char 0 (1)
Operation type unsigned char 1 (1)
Table space identifier unsigned short 2 (2)
Object identifier unsigned short 4 (2)
Parent table space identifier unsigned short 6 (2)
Parent object identifier unsigned short 8 (2)
Internal Internal 10 (2)
The total length of the LOB manager log record header is 12 bytes.

LOB manager log record header operation type values and definitions

Table 2. LOB manager log record header operation type values and definitions
Value Definition
64 Add LOB data record
65 Add LOB amount record
66 Delete LOB data record (information only)
67 Non-update LOB data record (information only)

Add LOB data and add LOB amount log record structure

Table 3. Add LOB data and add LOB amount log record structure
Description Type Offset (Bytes)
Log header LOBLogRecordHeader 0 (12)
LOB length1 sqluint32 12 (4)
Byte offset2 sqluint64 16 (8)
Internal Internal 24 (1)
Original operation type3 unsigned char 25 (1)
Column identifier4 unsigned short 26 (2)
Internal Internal 28 (4)
LOB data5 char[] 32 (variable)
1 LOB data length in bytes.
2 Byte offset into LOB data object where data is to be located.
3 Original operation type:
  • 1: Insert
  • 2: Delete
  • 4: Update
  • 8: Concat

4 The column number that the log record is applied to. The column number starts at 0. All out-of-row varying-length string data that is stored in a LOB data object is consolidated into a single data object and the column number is set to 65535 (see the following section for more information).5 The add LOB amount log record does not contain any actual LOB data, so you should ignore this field.
Log records with column number 65535:
LOB log records with a column number of 65535 have the following behaviors:
  • When a row is inserted and part of its varying-length string data is stored as LOB data, the LOB manager writes the appropriate LOB data log record with a column identifier of 65535 and the original operation type in the log record set to 1. This log record is written before the data manager insert log record.
  • When a row is deleted, the old LOB data (containing the old string data) is deleted and is logged if DATA CAPTURE is ON. The original operation type in the log record is set to 2 and the column identifier is set to 65535. This log record is written after the data manager delete log record.
  • When a row with part of its varying-length string data stored as LOB data has its out-of-row varying-length string data updated, the update is treated as an insert and a delete. The old LOB data is deleted and new LOB data might be inserted, if the updated row contains out-of-row varying-length string data. Both log records have the original operation type set to 4 and the column identifier is set to 65535. The log records are written before the data manager update log record.
  • When a row with part of its varying-length string data stored as LOB data is updated, but there is no change to the out-of-row varying length string data, a non-update LOB data record is written before the data manager update log record.
  • The delete LOB data record and the non-update LOB data record are information only log records.

If a LOB data object is too big to be logged, it can be split and logged by multiple LOB data log records. In such cases, you can locate all the LOB data log records with the same column identifier, operation type, and original operation type and then concatenate all the LOB data from each LOB data log record.

When the column identifier is 65535, the corresponding LOB data (after applying concatenation, if necessary) is the consolidation of all the out-of-row varying-length string data. The structure to retrieve individual varying-length string data consists of the following:
Header
The first four bytes is the header for the structure.
  • The first byte has an "eye-catcher" value of 0x12.
  • The next 3 bytes indicate the size of the structure (including header) in big endian format.
Offset array and data
An offset array and the data portion follow the header. Each entry in the offset array is a 4-byte offset to the corresponding column data in the data portion. The number of entries in the offset array is one plus the number of table columns at the time when this log record was written.
  • For a varying-length string data with column identifier ’n’ (column identifiers start at 0), you can look up its offset from the offset array.
  • The length of the string data is the difference between the current offset and the offset of the next column.
  • Only out-of-row varying-length string data has a non-zero length.

Log flow examples

The log record sequence for the following examples flow from top to bottom, and only propagated log records are shown.

Assume the table DDL is defined as follows:
CREATE TABLE T1 (C1 INT, C2 CHAR(30), C3 CLOB, C4 CLOB) DATA CAPTURE CHANGES
  • If you insert a row into T1, the sequence of log records is as follows:
    1. Start of the out-of-row data log record
    2. Add LOB data log record (for column C3)
    3. Add LOB data log record (for column C4)
    4. DMS insert record log record (the DMS row data for the rest of the row)
    5. Commit log record
  • If you insert a row into T1 and then roll the operation back, the sequence of log records is as follows:
    1. Start of the out-of-row data log record
    2. Add LOB data log record (for column C3)
    3. Add LOB data log record (for column C4)
    4. DMS insert record log record (the DMS row data for the rest of the row)
    5. Undo insert log record
    6. Undo start of out-of-row data log record
      Note: There are no undo add LOB data log records
    7. Abort log record