CSL Manager log records

You can use the information from Common Storage Layer (CSL) manager log records to replicate XML columns in your replication solutions.

In Db2® 11.5.8 and later, CSL Manager log records are written for XML data. The XML serialized document log record is a CSL Manager log record and is written as propagatable for insert and update operations when the table has DATA CAPTURE CHANGES enabled and the DB2_DCC_XML_SERIALIZE registry variable is turned on.

XML log records written for update and insert operations 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, function ID 211, that marks the beginning of these out-of-row data log records. This record is also known as the "Start of out-of-row data log record". 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

When XML data is updated, the update is treated as a delete of the old XML data, followed by insert of the new XML data. However, the delete operation is not marked propagatable. One or more XML serialized log records can be written for an insert operation, or the insert portion of an update operation.

The CSL manager might write multiple XML serialized data log records for one insert or update operation. Each XML serialized data log record is associated with an XML column in the table, so more than one can be written for a single row insert or update operation. For a single row and single XML column, multiple XML serialized data log records can still be written, if the XML data being inserted is greater than 32KB in length. In this case the XML document is constructed by concatenating the data from all XML serialized data log records for a given column before encountering the associated DMS insert or update log record. The concatenated data represents the full serialized XML document for the column and row.

An XML serialized data log record is not written by the CSL manager if the result of the insert or update is NULL for the XML column in the record. You can determine whether the value is NULL by reading the insert or update Data manager log record.

All CSL manager log records begin with a header and CSL manager log record offsets start from the end of the log manager record header.

CSL manager log record header (CSLLogRecordHeader)

Table 1. CSL manager log record header (CSLLogRecordHeader)
Description	Type	Offset (Bytes)
Component identifier (=15)	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)
Object type	unsigned char	10 (1)
Internal	unsigned char	11 (1)

Note: The object type value for XML is 6, and the total length of the CSL manager log record header is 12 bytes.

CSL manager log record header operation type values and definitions

Table 2. CSL manager log record header operation type values and definitions
Value	Definition
114	XML serialized document record

The XML serialized document log record is a log record of type 0x0069, which is an informational log record only. Some informational log records are not followed by another header: they are only the size of the log manager log record header, which is 40 bytes for an informational log record. You must first validate that the length of the log record is larger than 40 bytes. This is indicated in the log manager record header. When you have validated the record length, check the component identifier byte, which directly follows the log manager record header and has an offset value of 0. If the component identifier is 15 then it is a CSL manager log record header.

Important: The XML serialized document log record is not written for databases that use circular logging.

Table 3. XML serialized document log record structure
Description	Type	Offset (Bytes)
Log header	CSLLogRecordHeader	0 (12)
Document length (in bytes)	sqluint32	12 (4)
Column identifier	unsigned short	16 (2)
Internal	Internal	18 (6)
XML data	char[]	24 (variable)

Important:

The Column identifier is the column number to which the log record is applied. The column number starts at 0. The document length in the log record is only for the portion of the document that fits into a single log record. For this reason, the document length can be smaller than the full document size. If the document is larger than what fits into a single XML serialized document log record, then multiple XML serialized document log records are written.
The serialized XML data written to the logs does not contain an XML declaration. Because Db2 only supports version 1.0, and the XML data in the log record is always UTF-8, the declaration is not needed.

The XML serialized document log record is written during the following operations:

Insert operations where the XML data is not null during the insert
Update operations where the XML data is not null as a result of the update, and the XML column is updated by the update statement.

It is possible to review the data manager log record to determine whether either of these types of operations occurred. Read Insert record, delete record, rollback delete record, rollback update record log records, to better understand where to find each field of the record and how to read each field. Each XML column is represented in the record and identified as to whether it is null. The process for determining null fields is the same for every column type. To identify updated XML columns within a record, first check whether the null or not null state of the field changed in the before and after images of the record.

Assuming the before and after states are both not null, further analysis of the XML field in the record is possible.

For an XML field in a data record for a table without VALUE COMPRESSION:

The XML field contains both a fixed and variable length portion of data. The length of the fixed portion is always 4 bytes.
The first 2 bytes (short) represent the offset from the beginning of the fixed length section, where the variable data is located.
The next 2 bytes (short) specify the length of the variable data referenced by the offset value.
Look at the 8 bytes starting at offset 16 (in bytes) from the start of variable data for the XML field:
- If these 8 bytes are the same between the before and after image of the record then the XML column was not updated.
- If the 8 bytes are different then the XML column was updated.

For an XML field in a data record for a table with VALUE COMPRESSION:

The XML field can be found by following the offset array.
When you find the XML column data by referencing the offset, look at the 8 bytes starting at offset 16 (in bytes) from the start of the XML column data:
- If these 8 bytes are the same between the before and after image of the record, then the XML column was not updated.
- If the 8 bytes are different, then the XML column was updated.

Note: Either method for determining whether the XML field was updated is valid, regardless of whether the XML column data is inlined.

Log flow examples

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

The following example uses a table DDL statement:

 CREATE TABLE T1 (C1 INT, C2 CHAR(30), C3 XML, C4 XML) DATA CAPTURE CHANGES

If you insert a row into T1 (assuming that each XML document is less than 32KB), the sequence of log records is as follows:

Start of the out-of-row data log record.
XML serialized document log record (for column C3).
XML serialized document log record (for column C4).
DMS insert record log record (the DMS row data for the rest of the row).
Commit log record.

If you insert a row into T1 and then roll the operation back, the sequence of log records is as follows:

Start of the out-of-row data log record.
XML serialized document log record (for column C3).
XML serialized document log record (for column C4).
DMS insert record log record (the DMS row data for the rest of the row).
Undo insert log record.
Undo start of out-of-row data log record. There are no undo XML serialized document log records.
Abort log record.

The following example uses another table DDL statement:

CREATE TABLE T2 (C1 INT, C2 XML INLINE LENGTH 4096, C3 CLOB, C4 XML, C5 XML) DATA CAPTURE CHANGES

If you insert a row into T2 where the XML document for C2 is less than 4096 in bytes and is inlined as a result, the XML doc for C4 is about 45KB in size, and NULL is inserted for C5.

The sequence of log records is as follows:

Start of the out-of-row data log record
XML serialized document log record (for column C2)
Add LOB data log record (for column C3)
XML serialized document log record (for column C4). The document length recorded in the XML serialized document log record is 32KB.
XML serialized document log record (for column C4). The document length recorded in the XML serialized document log record is 13KB.
DMS insert record log record (the DMS row data for the rest of the row)
Commit log record

Note:

In the example, the XML serialized document log record is written for C2 even though the document is smaller than the inline length. This is always the case, as the XML serialized document log record are logged regardless of whether the document is inlined or not within the data row.
The serialized XML document for C4 is split between two log records. The full serialized document can be attained by just concatenating the data from each log record for the column until the DMS log record for the row is seen.
There is no XML serialized document log record for C5 since NULL is inserted for that column.

Usage notes

Enabling this feature can increase the amount of log record data written to your system. The full serialized XML document is written to the logs for each document inserted or updated. The size of this write operation can be significantly larger than existing logging that is done during the insert or update when XML is involved. The amount of additional logging is more noticeable when compression is enabled on the table, since XML logging without this feature logs the document using an internal format that is encoded and compressed. The XML serialized document log record writes the document in plain, uncompressed text.
Updating your log stream database configuration parameters to handle the additional logging from insert and update transactions helps to maintain good performance. Updating these parameters can also minimize log-full situations. Parameters to consider include LOGBUFSZ, LOGFILSIZ, LOGPRIMARY, MAX_LOG, and NUM_LOG_SPAN.
Enabling this feature results in a slight performance decrease when running insert and update operations involving XML columns and data. This decrease is a result of the additional logic that is required to serialize the XML document and write this additional data to the log stream.