Logical log data file
The format of the logical log data file does not define an order for the records that the file contains. Normally the records in the data file are not in the same order as the original database changes reflected in Db2 log records.
Application programs that process a logical log data file might need to sort the records in the file before processing. For example, to duplicate the original order for units of recovery and the order of the changes within each unit of recovery, use the UORCOMMITLRSN and ORDERVALUE fields as sort keys. (If your logical log file contains segmented logical log records, be sure to account for multiple segments by using the SEGNUM field of the logical log header. For more information, see Normal and segmented logical log record formats).
Data change record (LLDF)
The logical log contains one instance of the Data Change Record for every change represented in the log that was defined (directly or indirectly) by your selection criteria.
The basic layout of the Data Change Record is shown in Normal and segmented logical log record formats. The record contains the following parts:
Logical log header
The header contains unit of recovery information, object identifiers, timestamp, and other information about the data change represented in the record. The layout of the logical log header is described in the "Data change record (LLDF)" table. The logical log control file also defines the layout of the logical log header in a set of description records. For more information, see Data change header description record (XHDF).
Important
The length of the logical log header can change in different versions of the product. If you process output logical log files with a program other than Log Master, use the LENGTH field at the beginning of each logical log record to determine the length of the header. By using this technique, you can avoid changing your programs or processes when the length of the header changes.
Row image
The row image begins with a two-byte length value that defines the length of the succeeding row image. The content of the row image varies depending on the type of change that the Data Change Record represents:
Insert action (the content is an image of inserted row)
Delete action (the content is an image of deleted row)
Update action (the content is an image of the row before update is completed)
Within a row image, BMC AMI Log Master for Db2 places columns in order based on their logical position within the base table, regardless of their physical position within the table row. The column order is based on the COLNO column in the SYSIBM.SYSCOLUMNS table of the Db2 catalog.
Additional row image (when required)
If the Data Change Record represents an update action, the record includes another row image that contains the after image of the row. The additional row image starts with a two-byte length field that begins immediately after the update action’s before image.
Records in the logical log data file can be segmented, depending on the length of the data represented in the record and the system-determined block size of the device where BMC AMI Log Master for Db2 writes the logical log. For more information about how the product segments logical log records, see Normal and segmented logical log record formats.
Record status: Db2 specific
Important
The "Data change record (LLDF)" table is updated for Log Master version 11.1 and later. However, Log Master allows for processing of earlier versions (back to version 7.3.00) of the logical log with normalization performed, as needed, to work with the new format.
In addition, Log Master version 11.1 and later supports extended precision of timestamp values.
Data change record (LLDF)
Field name | Offset | Definition | Description | |
---|---|---|---|---|
Dec | Hex | |||
LENGTH | 00 | 00 | BIN(2) | Length of the header |
SYSTEMID | 02 | 02 | CHAR(4) | Originating Db2 system |
DBID | 06 | 06 | BIN(2) | Database ID of the object that the record is for |
PSID | 08 | 08 | BIN(2) | Table Space ID of the object that the record is for |
TBOBID | 10 | 0A | BIN(2) | Table OBID |
TBOWNERLEN | 12 | 0C | BIN(2) | Length of the TABLEOWNER field |
TBNAMELEN | 14 | 0E | BIN(2) | Length of the TABLENAME field |
DBNAME | 16 | 10 | CHAR(8) | Database name |
TSNAME | 24 | 18 | CHAR(8) | Table space name |
TABLEOWNER | 32 | 20 | CHAR(8) | Owner ID for the table
|
TABLENAME | 40 | 28 | CHAR(18) | Table name
|
PARTNUM | 58 | 3A | BIN(2) | Partition number (if table space) |
TIMESTAMP | 60 | 3C | BIN(17) 1 | Timestamp for this log record |
LOGLRSN | 77 | 4D | BIN(10) 1 | LRSN for this update |
LOGRBA | 87 | 57 | BIN(10) 1 | RBA for this update |
MEMBERID | 97 | 61 | BIN(2) | Data sharing member ID (zero if not data sharing) |
RID | 99 | 63 | BIN(5) | Row ID (page | row number) |
CHANGE TYPE | 104 | 68 | CHAR(2) | Type of update: 'UB' – update 'I ' – insert 'D ' – delete 'DM' – mass delete 'DT' – truncate 'DR' – truncate with reuse option 'IL' – insert due to load action 'CO' – catalog object (DDL information) 'E' – exchange 'CM' – Db2 command 'SC' - status change |
SQLTYPE | 106 | 6A | CHAR(1) | U – UNDO R – REDO ' ' (blank) – other |
LOGRECDISP | 107 | 6B | CHAR(1) | Disposition of log record (as opposed to disposition of URID): C – Record was committed A – Record was aborted (included in a ROLLBACK action) S – Record was included in a ROLLBACK TO SAVEPOINT action O – Record was reversed by Db2 (for example, in response in response to some negative SQL codes |
SQLSRCTYPE | 108 | 6C | CHAR(1) | R – Record generated by RI constraint T – Record generated by trigger activity B – Record generated by both RI constraint and trigger activity (RI takes precedence) H – Record generated by history activity on a system-maintained temporal table |
LOGBYTES | 109 | 6D | BIN(4) | Number of bytes contained in Db2 log record(s) for this transaction |
LOGDELTA | 113 | 71 | BIN(2) | Number of log bytes that would have been saved had DATA CAPTURE CHANGES been off. Or: The number of additional bytes that would have been logged had DATA CHANGES been on. |
ANOMALYROWID | 115 | 73 | BIN(1) | Anomaly Anchor ID from RID |
ANOMALYTYPE | 116 | 74 | CHAR(1) | Tells why this record is in the logical log – N for normal record selected by the filter |
ANOMALYRBA | 117 | 75 | BIN(10) 1 | RBA/LRSN of the anomaly anchor point |
UORTIMESTAMP | 127 | 7F | BIN(17) 1 | Timestamp for this unit of recovery |
UORCOMMITTIMESTAMP | 144 | 90 | BIN(17) 1 | Timestamp value for the commit record for this unit of recovery |
UORDISP | 161 | A1 | CHAR(1) | Disposition of unit of recovery: C – committed A – aborted |
UORIDLRSN | 162 | A2 | BIN(10) 1 | LRSN of unit of recovery record |
UORID | 172 | AC | BIN(10) 1 | RBA of unit of recovery record |
SEGLEN | 182 | B6 | BIN(2) | Length of segment |
TOTALSEGS | 184 | B8 | BIN(2) | Total number of segments |
SEGNUM | 186 | BA | BIN(2) | Sequence number of current segment |
188 | BC | BIN(4) | Reserved | |
UORCOMMITLRSN | 192 | C0 | BIN(10) 1 | LRSN of the commit record for this unit of recovery |
UORCOMMITPOINT | 202 | CA | BIN(10) 1 | RBA of the commit record for this unit of recovery |
CONNECTIONTYPE | 212 | D4 | CHAR(2) | Type of connection |
CONNECTID | 214 | D6 | CHAR(8) | Connection ID |
CORRELATIONID | 222 | DE | CHAR(12) | Correlation ID |
AUTHID | 234 | EA | CHAR(8) | Authorization ID |
PLAN | 242 | F2 | CHAR(8) | Plan name |
LUWNETWORKID | 250 | FA | CHAR(8) | Logical unit of work (LUW) network ID |
LUWNAME | 258 | 102 | CHAR(8) | LUW name |
LUWINSTANCENO | 266 | 10A | BIN(6) | LUW instance number |
LUWSEQUENCENO | 272 | 110 | BIN(2) | LUW commit sequence number |
INCOMPLETETRANS | 274 | 112 | CHAR(1) | Y - record is part of a transaction (URID) that contains at least one log record that BMC AMI Log Master for Db2 cannot complete |
INCOMPLETEDEP | 275 | 113 | CHAR(1) | Y - record is part of a transaction (URID) that might depend on another URID that contains at least one record that BMC AMI Log Master for Db2 cannot complete |
UORHASCOMP | 276 | 114 | CHAR(1) | Y - Part of a transaction (URID) that contains at least one compensation log record N - Part of a transaction that contains no compensation log records |
SQLRIRBA | 277 | 115 | BIN(10) 1 | Used for sort key for SQL sort |
PAGENUMFMT | 287 | 11F | CHAR(1) | A - Originating table space defined as PAGENUM ABSOLUTE, meaning that the page number in the RID field contains the embedded partition number R - Originating table space defined as PAGENUM RELATIVE, meaning that the page number in the RID field contains only the page number The PARTNUM field contains the partition number in both cases. |
DATA | 288 | 120 | Variable | Length: First 2 bytes are a halfword value that contains total length of the data segment (including 2-byte length) Content: After 2-byte length, content depends on update type:
For UB records (Update with both before and after data): before data is first; after data is next Column order: within a row image, column order based on logical position within base table, not physical position within table row, column order based on COLNO column in SYSIBM.SYSCOLUMNS catalog table. |
1 BIN(17) timestamps are of the format X'YYYYMMDDHHMMSSHHMMMMNNNPPPFFF00000' where:
YYYYMMDD is the date given as year, month, day
HHMMSS is the time given as hour, minute, seconds
HHMMMMNNNPPPFFF00000 represents fractional seconds and padding
BIN(10) LRSN is extended format LRSN.
Non-extended format LRSN, BIN(6) is placed at offset 1 (for example, X'CA670FBBF3D3' -> X'00CA670FBBF3D3000000').
BIN(10) RBA is extended format RBA.
Non-extended format RBA, BIN( 6) is placed at offset 4 (for example, X'123456789ABC' -> X'00000000123456789ABC').
The layout of the information in the DATA portion of the Data Change Record is similar to the layout of a Db2 log record, with the following exceptions:
All numeric fields (Db2 Types INT, SMALL INT, DEC, and FLOAT) are translated from the Db2 internal format to an external format. This translation enables the numeric value to be used in SAS or other application programs. However, if a column contains negative values, the translated value cannot be sorted correctly.
DATE, TIME, and TIMESTAMP fields are translated from the Db2 internal format to an external format (Db2, ISO, or SAS.) This translation means that more space is used to represent these fields in character format.
If a column (field) is nullable, the null byte precedes the data for that column.
Variable length fields contain a two-byte length attribute before the actual data. (This format is the same as in the Db2 log.)
For variable length fields that are nullable, the position of the null byte in the logical log is different than its position in the Db2 log. In the following example,
x'llll'
represents the two-byte field length information andx'nn'
represents the null byte.DB2 log:
x'llll'x'nn'
Logical log:
x'nn'x'llll'
In addition to the null byte order being reversed, the length value in the Db2 log includes the one-byte length of the null byte. The extra byte is not included in the length value in the logical log.
Important
If CHANGE TYPE is set to DM (mass delete), data segments might or might not exist. If the mass delete action is the result of a LOAD REPLACE of a specific partition, data segments will exist defining the limit key values.
Comments
Log in or register to comment.