SQL Guide : Database virtual tables : SYS_LOG
  
SYS_LOG
The SYS_LOG table is a virtual table: when the Log Reader receives an SQL request for the SYS_LOG table, the appropriate result set is generated dynamically from the internal log structures.
Column name
Data type
Description
RECID
INTEGER
Record identifier
See Record identifier descriptions for a detailed description.
RECNAME
VARCHAR
Descriptive name for log record. This is a more human readable format for the RECID field.
TRXID
INTEGER
Transaction identifier
STMTTRXID
INTEGER
Statement identifier in the transaction
RELID
INTEGER
Table identifier as stored into the SYS_TABLES table.
FLAGS
INTEGER
Flags field, this field has the following meaning:
▪0 NOP
No data is available, in this case all other column values are NULL.
▪Bit 0 set DATA
Data is available.
▪Bit 1 set SHUTDOWN
Server shutdown started.
▪Bit 6 set CAPTURE_OFF
If set in the log record as DBE_LOGREADER_LOG_REC_TRX_START, then operations in this transaction should not be propagated.
LOGADDR
VARBINARY
This is the log address of current log record.
The LOGADDR can be used as a starting point for the read from SYS_LOG.
The length of the log address is 20 bytes.
The log addresses can be compared using binary compare to see which log address comes first.
DATA
VARBINARY
The actual user data in the table.
TEXTDATA
LONG VARCHAR
Not used, always NULL. (Intended for user readable text format for DATA.)
Record identifier descriptions
RECNAME
RECID
Description
DBE_LOGREADER_LOG_REC_EMPTY
0
Empty record, no data available.
DBE_LOGREADER_LOG_REC_INSERT
1
Row insert.
DBE_LOGREADER_LOG_REC_UPDATE
3
After-image for update. If primary key is changed, it is not shown as an update. Instead it shows as insert and delete.
DBE_LOGREADER_LOG_REC_DELETE_FULL
4
Row delete where all column values stored into log.
DBE_LOGREADER_LOG_REC_UPDATE_BEFOREIMAGE
5
Before-image for update. If primary key is changed, it is not shown as an update. Instead it shows as insert and delete.
DBE_LOGREADER_LOG_REC_TRX_START
7
Transaction start. See FLAGS field how to detect unpropagated deletes.
DBE_LOGREADER_LOG_REC_COMMIT
12
Transaction commit.
DBE_LOGREADER_LOG_REC_DDL
13
DDL operation.
DBE_LOGREADER_LOG_REC_SQL
6
SQL string of DDL operation.
DATA column format for row data
This section describes the DATA column format for rows where the value of the RECNAME field is the following:
▪DBE_LOGREADER_LOG_REC_INSERT
▪DBE_LOGREADER_LOG_REC_UPDATE
▪DBE_LOGREADER_LOG_REC_UPDATE_BEFOREIMAGE
▪DBE_LOGREADER_LOG_REC_DELETE_FULL
DATA column is formatted so that for each user column in a table, the DATA column contains a <length><data> pair in binary format. Columns are ordered in the order they are defined in the table.
The format for integer values is so called Most Significant Byte (MSB) first format. This means, for example, that bytes in a four byte hexadecimal integer 0x12345678 is stored as bytes 12 34 56 78.
The <length> field is always a four byte integer in the MSB first format.
NULL values are stored as <length> field of -1.
BLOB values are stored as <length> field of -2, followed by 8 byte integer in MSB first format in <data> field.
The 8 byte integer is a unique BLOB ID. Whenever a new BLOB values is stored or old value is changed, it will be allocated a unique BLOB ID.
Name
Description
SQL data type
BINARY
UNICODE
CHAR
Stored as raw data bytes, the same format as stored into column.
CHAR
VARCHAR
LONG VARCHAR
WCHAR
WVARCHAR
LONG WVARCHAR
BINARY
VARBINARY
LONG VARBINARY
DOUBLE FLOAT
Stored as 8 byte IEEE floating point number in MSB first format.
FLOAT
REAL
DOUBLE PRECISION
INTEGER
Stored as 4 byte integer in MSB first format.
BIT TINYINT
BIGINT
Stored as 8 byte integer in MSB first format.
BIGINT
DATE
Stored as 11 byte raw data format. The bytes have the following meaning:
▪0-1 Year, 2 bytes in MSB first format
▪2 Month, 1 byte
▪3 Day of month, 1 byte
▪4 Hours, 1 byte
▪5 Minutes, 1 byte
▪6 Seconds, 1 byte
▪7 Fractions of second, 4 bytes in MSB first format
DATE TIME TIMESTAMP
DFLOAT
Decimal floating point in string format.
NUMERIC DECIMAL
DATA column format for DDL data
For rows where RECNAME field has value DBE_LOGREADER_LOG_REC_DDL, the DATA column format is the following:
▪DATA column is formatted so that it contains a <length><logrecid><length><info> values.
For rows where RECNAME field has value DBE_LOGREADER_LOG_REC_SQL, the DATA column format is the following:
▪DATA column is formatted so that it contains a <length><info> values. The value of <info> field is the SQL string.
The format for integer values is so called Most Significant Byte (MSB) first format. It means that bytes in a four byte hexadecimal integer 0x12345678 is stored as bytes 12 34 56 78.
The <length> field is always a four byte integer in MSB-first format.
The possible <logrecid> field descriptions and <info> values are described in the table below.
LOGRECID
Description
INFO
45
Create table
Fully qualified table name
17
Drop table
Table name
47
Rename table
Fully qualified table name
22
Alter table
Table name
73
Truncate table
Table name
16
Create index
Index name
18
Drop index
Index name
46
Create view
Fully qualified view name
20
Drop view
View name
28
Create sequence
Sequence name
30
Drop sequence
Sequence name
27
Create counter
Counter name
29
Drop counter
Counter name
See also
Database virtual tables