Administrative API Reference

Data Manager Log Records

Data manager log records are the result of DDL, DML, or Utility activities.

There are two types of data manager log records:

• Data Management System (DMS) logs have a component identifier of 1 in their header.
• Data Object Manager (DOM) logs have a component identifier of 4 in their header.

Table 96. DMS Log Record Header Structure (DMSLogRecordHeader)

Description	Type	Offset (Bytes)
Component identifier (=1)	unsigned char	0(1)
Function identifiera	unsigned char	1(1)
Table identifiers   Table space identifier   Table identifier	unsigned short unsigned short	2(2) 4(2)
Total Length: 6 bytes
Values and Definitions a Valid function identifier values 102 Add columns to table 104 Undo add columns 106 Delete record 110 Undo insert record record 111 Undo delete record 112 Undo update record 113 Alter column length 115 Undo alter column length 118 Insert record 120 Update record 124 Alter table attribute 128 Initialize table

Table 97. DOM Log Record Header Structure (DOMLogRecordHeader)

Description	Type	Offset (Bytes)
Component identifier (=4)	unsigned char	0(1)
Function identifiera	unsigned char	1(1)
Object identifiers   Table space identifier   Object identifier	unsigned short unsigned short	2(2) 4(2)
Table identifiers   Table space identifier   Table identifier	unsigned short unsigned short	6(2) 8(2)
Object type	unsigned char	10(1)
Flags	unsigned char	11(1)
Total Length: 12 bytes
Values and Definitions a Valid function identifier values 2 Create index 3 Drop index 4 Drop table 11 Truncate table (import replace) 35 Reorg table 101 Create table 130 Undo create table

Note:

All data manager log record offsets are from the end of the log manager record header.

All log records whose function identifier short name begins with UNDO are log records written during the UNDO or ROLLBACK of the action in question.

The ROLLBACK can be a result of:

• The user issuing the ROLLBACK transaction statement
• A deadlock causing the ROLLBACK of a selected transaction
• The ROLLBACK of uncommitted transactions following a crash recovery
• The ROLLBACK of uncommitted transactions following a RESTORE and ROLLFORWARD of the logs.

Initialize Table

The initialize table log record is written when a new permanent table is being created; it signifies table initialization. This record appears after any log records that create the DATA storage object, and before any log records that create the LF and LOB storage objects. This is a Redo log record.

Table 98. Initialize Table Log Record Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
File create LSN	SQLU_LSN	6(6)
Table directory record	variable	12(72)
record type	unsigned char	12(1)
reserved	char	13(1)
index flag	unsigned short	14(2)
index root page	sqluint32	16(4)
TDESC recid	sqlint32	20(4)
reserved	char	24(56)
flagsa	sqluint32	80(4)
Table description length		84(4)
Table description record	variable	88(variable)
record type	unsigned char	88(1)
reserved	char	89(1)
number of columns	unsigned short	90(2)
array	variable long	92(variable)
Total Length: 88 bytes plus table description record length
a Bit 0x00000020 indicates that the table was created with the NOT LOGGED INITIALLY option, and that no DML activity on this table is logged until the transaction that created the table has been committed.
Table Description Record: column descriptor array (number of columns) * 8, where each element of the array contains: field type (unsigned short, 2 bytes) SMALLINT 0x0000 CHAR 0x0100 GRAPHIC 0x0200 INTEGER 0x0001 VARCHAR 0x0101 VARGRAPH 0x0201 DECIMAL 0x0002 LONG VARCHAR 0x0104 LONG VARG 0x0202 DOUBLE 0x0003 DATE 0x0105 DBCLOB 0x0203 REAL 0x0004 TIME 0x0106 BIGINT 0x0005 TIMESTAMP 0x0107 BLOB 0x0108 CLOB 0x0109 DATALINK 0x010E length (2 bytes) If BLOB, CLOB, or DBCLOB, this field is not used. For the maximum length of this field, see the array that follows the column descriptor array. If not DECIMAL, length is the maximum length of the field (short). If PACKED DECIMAL: Byte 1, unsigned char, precision (total length) Byte 2, unsigned char, scale (fraction digits). null flag (unsigned short, 2 bytes) mutually exclusive: allows nulls, or does not allow nulls valid options: no default, type default, or user default ISNULL 0x01 NONULLS 0x02 TYPE_DEFAULT 0x04 USER_DEFAULT 0x08 field offset (unsigned short, 2 bytes) This is the offset from the start of the formatted record to where the field's fixed value can be found.
Table Description Record: LOB descriptor array (number of LOB, CLOB, and DBCLOB fields) * 12, where each element of the array contains: length (MAX LENGTH OF FIELD, sqluint32, 4 bytes) reserved (internal, sqluint32, 4 bytes) log flag (IS COLUMN LOGGED, sqluint32. 4 bytes) The first LOB, CLOB, or DBCLOB encountered in the column descriptor array uses the first element in the LOB descriptor array. The second LOB, CLOB, or DBCLOB encountered in the column descriptor array uses the second element in the LOB descriptor array, and so on.

Import Replace (Truncate)

The import replace (truncate) log record is written when an IMPORT REPLACE action is being executed. This record indicates the re-initialization of the table (no user records, new life LSN). The second set of pool and object IDs in the log header identify the table being truncated (IMPORT REPLACE). This is a Redo log record.

Table 99. Import Replace (Truncate) Log Record Structure

Description	Type	Offset (Bytes)
Log header	DOMLogRecordHeader	0(12)
internal	variable	12(variable)
Total Length: 12 bytes plus variable length

Rollback Insert

The rollback insert log record is written when an insert row action (INSERT RECORD) is rolled back. This is a Compensation log record.

Table 100. Rollback Insert Log Record Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
RID	sqlint32	8(4)
Record length	unsigned short	12(2)
Free space	unsigned short	14(2)
Total Length: 16 bytes

Reorg Table

The reorg table log record is written when the REORG utility has committed to completing the reorganization of a table. This is a Normal log record.

Table 101. Reorg Table Log Record Structure

Description	Type	Offset (Bytes)
Log header	DOMLogRecordHeader	0(12)
Internal	variable	12(252)
Index tokena	unsigned short	2(264)
Temporary tablespace IDb	unsigned short	2(266)
Total Length: 268 bytes
a If not 0, it is the index by which the reorg is clustered (clustering index). b If not 0, it is the system temporary table space that was used to build the reorg.

Create Index, Drop Index

These log records are written when indexes are created or dropped. The two elements of the log record are:

• The index root page, which is an internal identifier
• The index token, which is equivalent to the IID column in SYSIBM.SYSINDEXES. If the value for this element is 0, the log record represents an action on an internal index, and is not related to any user index.

This is a Undo log record.

Table 102. Create Index, Drop Index Log Records Structure

Description	Type	Offset (Bytes)
Log header	DOMLogRecordHeader	0(12)
Padding	char[ ]	12(2)
Index token	unsigned short	14(2)
Index root page	sqluint32	16(4)
Total Length: 20 bytes

Create Table, Drop Table, Rollback Create Table, Rollback Drop Table

These log records are written when the DATA object for a permanent table is created or dropped. The DATA object is created during a CREATE TABLE, and prior to table initialization (Initialize Table). Create table and drop table are Normal log records. Rollback create table and rollback drop table are Compensation log records.

Table 103. Create Table, Drop Table, Rollback Create Table, Rollback Drop Table Log Records Structure

Description	Type	Offset (Bytes)
Log header	DOMLogRecordHeader	0(12)
Internal	variable	12(56)
Total Length: 68 bytes

Alter Table Attribute

The alter table attribute log record is written when the state of a table is changed VIA the ALTER TABLE statement or as a result of adding or validating constraints.

Table 104. Alter Table Attribute, Undo Alter Table Attribute

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
Alter bit (attribute) mask	int	8(4)
Alter bit (attribute) values	int	12(4)
Total Length: 16 bytes
Attribute Bits: 0x00000001 Propagation 0x00000002 Check Pending 0x00010000 Append Mode 0x00200000 LF Propagation 0x00400000 LOB Propagation If one of the bits above is present in the alter bit mask, then this attribute of the table is being altered. To determine the new value of the table attribute (0 = OFF and 1 = ON), check the corresponding bit in the alter bit value.

Alter Table Add Columns, Rollback Add Columns

The alter table add columns log record is written when the user is adding columns to an existing table using an ALTER TABLE statement. Complete information on the old columns and new columns is logged.

• Column count elements represent the old number of columns and the new total number of columns.
• The parallel arrays contain information about the columns defined in the table. The old parallel array defines the table prior to the ALTER TABLE statement, while the new parallel array defines the table resulting from ALTER TABLE statement.
• Each parallel array consists of:
  • An array equivalent to the column descriptor array in the table description record (see Initialize Table).
  • A second array equivalent to the LOB descriptor array in the table description record. However, since this array is parallel to the first, the only elements used are those whose corresponding element in the first array are of type BLOB, CLOB, or DBCLOB.

Alter table add columns is a Normal log record. Rollback add columns is a Compensation log record.

Table 105. Alter Table Add Columns, Rollback Add Columns Log Records Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordheader	0(6)
Padding	char[ ]	6(2)
Old column count	int	8(4)
New column count	int	12(4)
Old parallel arraysa	variable	16(variable)
New parallel arraysb	variable	variable
Total Length: 40 bytes plus 2 sets of parallel arrays; array size is (old/new column count) * 20.
Array Elements <\p> a Each element in this array is 8 bytes long. b Each element in this array is 12 bytes long. For information about the column descriptor array or the LOB descriptor array, see Table 98).

Insert Record, Delete Record, Rollback Delete Record, Rollback Update Record

These log records are written when rows are inserted into or deleted from a table. Insert record and delete record log records are generated during an update if the location of the record being updated must be changed to accommodate the modified record data. Insert record and delete record are Normal log records. Rollback delete record and rollback update record are Compensation log records.

Table 106. Insert Record, Delete Record, Rollback Delete Record, Rollback Update Record Log Records Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
RID	sqlint32	8(4)
Record length	unsigned short	12(2)
Free space	unsigned short	14(2)
Record offset	unsigned short	16(2)
Record header and data	variable	18(variable)
Total Length: 18 bytes plus Record length
Record Header and Data Details: Record header 4 bytes Record typea (unsigned char, 1 byte). Records are one of two classes: Updatable Special control A value of 0 or 4 indicates that the record can be viewed. Each class has three types: Normal Pointer Overflow Reserved (char, 1 byte) Record length (unsigned short, 2 bytes) Record variable Record type (unsigned char, 1 byte). Updatable records are one of two types: Internal control Formatted user data A value of 1 signifies a formatted user data record. Reserved (char, 1 byte) The rest of the record is dependent upon the record type and the table descriptor record defined for the table. If the record type is internal control, the data cannot be viewed. The following fields apply to user data records: Fixed length (unsigned short, 2 bytes). This is the length of all fixed portions of the data row. Formatted record (fixed and variable length). For more information about formatted records, see "Formatted User Data Record". a Record data can only be viewed if the record type (specified in the record header) is updatable (that is, not special control).

Formatted User Data Record

The formatted record can be a combination of fixed and variable length data. All fields contain a fixed length portion. In addition, there are eight field types that have variable length parts:

• VARCHAR
• LONG VARCHAR
• DATALINK
• BLOB
• CLOB
• VARGRAPHIC
• LONG VARG
• DBCLOB

Field Lengths

The length of the fixed portion of the different field types can be determined as follows:

• DECIMAL

  This field is a standard packed decimal in the form: nnnnnn...s. The length of the field is: (precision + 2)/2. The sign nibble (s) is xC for positive (+), and xD or xB for negative (-).

• SMALLINT INTEGER BIGINT DOUBLE REAL CHAR GRAPHIC

  The length field in the element for this column in the table descriptor record contains the fixed length size of the field.

• DATE

  This field is a 4-byte packed decimal in the form: yyyymmdd. For example, April 3, 1996 is represented as x'19960403'.

• TIME

  This field is a 3-byte packed decimal in the form: hhmmss. For example, 1:32PM is represented as x'133200'.

• TIMESTAMP

  This field is a 10-byte packed decimal in the form: yyyymmddhhmmssuuuuuu (DATE|TIME|microseconds).

• VARCHAR LONG VARCHAR DATALINK BLOB CLOB VARGRAPHIC LONG VARG DBCLOB

  The length of the fixed portion of all the variable length fields is 4.

For more detailed information about field types, see the SQL Reference.

The following sections describe the location of the fixed portion of each field within the formatted record.

Table Descriptor Record

The table descriptor record describes the column format of the table. It contains an array of column structures, whose elements represent field type, field length, null flag, and field offset. The latter is the offset from the beginning of the formatted record, where the fixed length portion of the field is located.

Table 107. Table Descriptor Record Structure

Table Descriptor Record
record type	number of columns	column structure field type length null flag field offset	LOB information
Note: For more information, see Table 98.

For columns that are nullable (as specified by the null flag), there is an additional byte following the fixed length portion of the field. This byte contains one of two values:

• NOT NULL (0x00)
• NULL (0x01)

If the null flag within the formatted record for a column that is nullable is set to 0x00, there is a valid value in the fixed length data portion of the record. If the null flag value is 0x01, the data field value is NULL.

The formatted user data record contains the table data that is visible to the user. It is formatted as a fixed length record, followed by a variable length section.

Table 108. Formatted User Data Record Structure

Formatted User Data Record
record type	length of fixed section	fixed length section	variable data section
Note: For more information, see Table 106.

All variable field types have a 4-byte fixed data portion in the fixed length section (plus a null flag, if the column is nullable). 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.

Update Record

The update record log record is written when a row is updated, and if its storage location does not change. There are two available log record formats; they are identical to the insert record and the delete record log records (see Insert Record, Delete Record, Rollback Delete Record, Rollback Update Record). One contains the pre-update image of the row being updated; the other contains the post-update image of the row being updated. This is a Normal log record.

Table 109. Update Record Log Record Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
RID	sqlint32	8(4)
New Record length	unsigned short	12(2)
Free space	unsigned short	14(2)
Record offset	unsigned short	16(2)
Old record header and data	variable	18(variable)
Log header	DMSLogRecordHeader	variable(6)
Padding	char[ ]	variable(2)
RID	sqlint32	variable(4)
Old record length	unsigned short	variable(2)
Free space	unsigned short	variable(2)
Record offset	unsigned short	variable(2)
New record header and data	variable	variable(variable)
Total Length: 36 bytes plus 2 Record lengths