Space announcements

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Logical log data file

The logical log data file contains the actual database changes, such as update, delete, and insert actions, for that part of the DB2 log defined (directly or indirectly) by your selection criteria.

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).

    Note

    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, 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 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

Note

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

  • When TBOWNERLEN<= 8

    actual owner name of the table

  • When TBOWNERLEN > 8

    a truncated version of the owner name (the first 8 bytes), corresponds to the SHORTOWNER field of the table’s DTBI record in the logical log control file

    For more information about the shortened owner name, see TABLE NAME.

TABLENAME

40

28

CHAR(18)

Table name

  • If TBNAMELEN<= 18

    actual table name

  • If TBNAMELEN > 18

    a unique, shortened version of the table name that corresponds to the SHORTNAME field of the table’s DTBI record in the logical log control file

    For more information about the shortened owner name, see 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 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 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 update, contains before / after row image

  • For insert, contains after row image

  • For delete, contains before row image

  • For exchange, contains

    • 2-byte length of clone table owner

    • Owner of clone table

    • 2-byte length of clone table name

    • Name of clone table

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 and x'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.

    Example

    For a five-character field, not nullable:

    • DB2 log: x'0005'ABCDE

    • Logical log: x'0005'ABCDE

    For a five-character field, nullable (not set to null):

    • DB2 log: x'0006'x'00'ABCDE

    • Logical log: x'00'x'0005'ABCDE

    For a five-character field, nullable (set to null):

    • DB2 log: x'0001'x'FF'

    • Logical log: x'FF'x'0000'

Note

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.


Related topic




Was this page helpful? Yes No Submitting... Thank you

Comments