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 87. 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   Tablespace identifier   Table identifier	unsigned short	2(2)
Total Length: 6 bytes
Definitions and Values: a Valid function identifier values SQLD_MIN_DP 100 MIN DBMS LOG FUNCTION ID SQLD_MAX_DP 149 MAX DBMS LOG FUNCTION ID ADDCOLUMNS_DP 102 Add columns via alter tbl CRNEWPG_DP 103 Create new page UNDOADDCOLUMNS_DP 104 Undo add columns ALTERPROP_DP 105 Alter prop flag DELREC_DP 106 Delete record on page UNDOALTERPROP_DP 107 Undo alter prop flag ALTERPENDING_DP 108 Alter check pending flag ALTERDEFAULTS_DP 109 Alter user defaults add flag UNDOADD_DP 110 Undo add a record UNDODEL_DP 111 Undo delete a record UNDOUPDT_DP 112 Undo update a record CRSYSPGR_DP 114 Initialize sys page DTR REORGPAGE_DP 117 Reorg page INSREC_DP 118 Insert record on page UPDREC_DP 120 Update record on UPDCHGONLY_DP 121 Log only updated CREATEPERM_DP 128 Initialize a DAT object UNDOALTERDEFAULTS_DP 131 Undo alter user default flag UNDOALTERPENDING_DP 132 Undo alter pending flag

Table 88. 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   Tablespace identifier   Object identifier	unsigned short	2(2)
Table identifiers   Tablespace identifier   Table identifier	unsigned short	6(2)
Object type	unsigned char	10(1)
Flags	unsigned char	11(1)
Total Length: 12 bytes
a For a list of valid function identifier values, see Table 87.

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 89. 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	unsigned long	16(4)
TDESC recid	long	20(4)
reserved	char	24(56)
flagsa	unsigned long	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
Note: 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 Details: column descriptor array (number of columns) * 8, where each element of the array contains: field typeb (unsigned short, 2 bytes) 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 flagc (unsigned short, 2 bytes) 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. LOB descriptor array (number of LOB, CLOB, and DBCLOB fields) * 12, where each element of the array contains: length (MAX LENGTH OF FIELD, unsigned long, 4 bytes) reserved (internal, unsigned long, 4 bytes) log flag (IS COLUMN LOGGED, unsigned long. 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. b field type SMALLINT 0x0000 INTEGER 0x0001 DECIMAL 0x0002 DOUBLE 0x0003 REAL 0x0004 CHAR 0x0100 VARCHAR 0x0101 LONG VARCHAR 0x0104 DATE 0x0105 TIME 0x0106 TIMESTAMP 0x0107 BLOB 0x0108 CLOB 0x0109 GRAPHIC 0x0200 VARGRAPH 0x0201 LONG VARG 0x0202 DBCLOB 0x0203 c null flag 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

Import Replace (Truncate)

The import replace (truncate) log record is written when an IMPORT REPLACE action is being executed. This record indicates the reinitialization 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 90. 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 91. Rollback Insert Log Record Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
RID	long	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 92. 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
Note: a If not 0, it is the index by which the reorg is clustered (clustering index). b If not 0, it is the 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 93. 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	unsigned long	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 94. 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 Propagation, Alter Check Pending, Rollback Propagation Change, Rollback Check Pending Change

The alter check pending log record is written when the state of a table is changed as a result of adding or validating constraints. A table is in CHECK PENDING STATE when the flag value for this PENDING state is 1 (TRUE = CHECK PENDING). Access to a table is restricted when it is in the CHECK PENDING state.

The alter propagation log record is written when the user changes the propagation state of a table with an ALTER TABLE statement. Valid flag values are:

• 0 (false = propagation off)
• 1 (true = propagation on).

Alter propagation and alter check pending are Normal log records. Rollback propagation change and rollback check pending change are Compensation log records.

Table 95. Alter Propagation, Alter Check Pending, Rollback Propagation Change, Rollback Check Pending Change Log Records Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
Old flag value	int	8(4)
New flag value	int	12(4)
Total Length: 16 bytes

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 the resulting columns (new columns equals resulting columns minus old columns) is logged.

• Column count elements represent the old number of columns and the new total number of columns (new or added columns equals new columns minus old columns).

• LOB count elements are used internally. They represent the number of BLOB, CLOB, and DBCLOB fields.

• VAR flag elements are used internally. They indicate whether any fields are of variable length.

• 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 96. 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 LOB count	int	16(4)
New LOB count	int	20(4)
Old LF count	int	24(4)
New LF count	int	28(4)
Old VAR flag value	int	32(4)
New VAR flag value	int	36(4)
Old parallel arraysa	variable	40(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: 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 89).

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 97. 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	long	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 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 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 seven field types that have variable length parts:

• VARCHAR
• LONG VARCHAR
• 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 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 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 98. 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 89.

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 99. 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 97.

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 100. Update Record Log Record Structure

Description	Type	Offset (Bytes)
Log header	DMSLogRecordHeader	0(6)
Padding	char[ ]	6(2)
RID	long	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	long	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

[ DB2 List of Books | Search the DB2 Books ]