solidDB Help : solidDB reference : SQL: Database system tables and system views : System tables : SYS_LOG
  
SYS_LOG
The SYS_LOG table is a virtual table which enables access to log records for a replication partition in a source database.
When Logreader receives an SQL request for the SYS_LOG table, the appropriate result set is generated dynamically from the internal log structures.
The columns in the SYS_LOG system table are described in the following table:
 
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, the values have 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 a log record that has the log identifier 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
The following table lists the record identifier names and 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_SQL
6
SQL string of DDL operation.
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.
DATA column format for row data
This section describes the DATA column format for rows where the RECNAME field has one of the following values:
▪ 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 is formatted so that it contains values in the format: length logrecid length info.
For rows where RECNAME field has value DBE_LOGREADER_LOG_REC_SQL, the DATA column is formatted so that it contains values in the format: length info. The value of info 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 following table:
 
LOGRECID
Description
INFO
16
Create index
Index name
17
Drop table
Table name
18
Drop index
Index name
20
Drop view
View name
22
Alter table
Table name
27
Create counter
Counter name
28
Create sequence
Sequence name
29
Drop counter
Counter name
30
Drop sequence
Sequence name
45
Create table
Fully qualified table name
46
Create view
Fully qualified view name
47
Rename table
Fully qualified table name
73
Truncate table
Table name
Go up to
System tables