Appendix A. Data Model

What follows is the complete SymmetricDS data model. Note that all tables are prepended with a configurable prefix so that multiple instances of SymmetricDS may coexist in the same database. The default prefix is sym_.

SymmetricDS configuration is entered by the user into the data model to control the behavior of what data is synchronized to which nodes.

Configuration Data Model

Figure A.1. Configuration Data Model


At runtime, the configuration is used to capture data changes and route them to nodes. The data changes are placed together in a single unit called a batch that can be loaded by another node. Outgoing batches are delivered to nodes and acknowledged. Incoming batches are received and loaded. History is recorded for batch status changes and statistics.

Runtime Data Model

Figure A.2. Runtime Data Model


A.1. NODE

Representation of an instance of SymmetricDS that synchronizes data with one or more additional nodes. Each node has a unique identifier (nodeId) that is used when communicating, as well as a domain-specific identifier (externalId) that provides context within the local system.

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X A unique identifier for a node.
NODE_GROUP_ID VARCHAR (50)  X The node group that this node belongs to, such as 'store'.
EXTERNAL_ID VARCHAR (50)  X A domain-specific identifier for context within the local system. For example, the retail store number.
SYNC_ENABLED INTEGER (1) 0   Indicates whether this node should be sent synchronization. Disabled nodes are ignored by the triggers, so no entries are made in data_event for the node.
SYNC_URL VARCHAR (255)    The URL to contact the node for synchronization.
SCHEMA_VERSION VARCHAR (50)    The version of the database schema this node manages. Useful for specifying synchronization by version.
SYMMETRIC_VERSION VARCHAR (50)    The version of SymmetricDS running at this node.
DATABASE_TYPE VARCHAR (50)    The database product name at this node as reported by JDBC.
DATABASE_VERSION VARCHAR (50)    The database product version at this node as reported by JDBC.
HEARTBEAT_TIME TIMESTAMP     The last timestamp when the node sent a heartbeat, which is attempted every ten minutes by default.
TIMEZONE_OFFSET VARCHAR (6)    The timezone offset in RFC822 format at the time of the last heartbeat.
BATCH_TO_SEND_COUNT INTEGER 0   The number of outgoing batches that have not yet been sent. This field is updated as part of the heartbeat job.
BATCH_IN_ERROR_COUNT INTEGER 0   The number of outgoing batches that are in error at this node. This field is updated as part of the heartbeat job.
CREATED_AT_NODE_ID VARCHAR (50)    The node_id of the node where this node was created. This is typically filled automatically with the node_id found in node_identity where registration was opened for the node.
DEPLOYMENT_TYPE VARCHAR (50)    An indicator as to the type of SymmetricDS software that is running. Possible values are, but not limited to: engine, standalone, war, professional, mobile

Table A.1. NODE


A.2. NODE_SECURITY

Security features like node passwords and open registration flag are stored in the node_security table.

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X Unique identifier for a node.
NODE_PASSWORD VARCHAR (50)  X The password used by the node to prove its identity during synchronization.
REGISTRATION_ENABLED INTEGER (1) 0   Indicates whether registration is open for this node. Re-registration may be forced for a node if this is set back to '1' in a parent database for the node_id that should be re-registred.
REGISTRATION_TIME TIMESTAMP     The timestamp when this node was last registered.
INITIAL_LOAD_ENABLED INTEGER (1) 0   Indicates whether an initial load will be sent to this node.
INITIAL_LOAD_TIME TIMESTAMP     The timestamp when this node started the initial load.
CREATED_AT_NODE_ID VARCHAR (50)  X The node_id of the node where this node was created. This is typically filled automatically with the node_id found in node_identity where registration was opened for the node.

Table A.2. NODE_SECURITY


A.3. NODE_IDENTITY

After registration, this table will have one row representing the identity of the node. For a root node, the row is entered by the user.

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X Unique identifier for a node.

Table A.3. NODE_IDENTITY


A.4. NODE_GROUP

A category of Nodes that synchronizes data with one or more NodeGroups. A common use of NodeGroup is to describe a level in a hierarchy of data synchronization.

NameType / SizeDefaultPK FKnot nullDescription
NODE_GROUP_ID VARCHAR (50) PK X Unique identifier for a node group, usually named something meaningful, like 'store' or 'warehouse'.
DESCRIPTION VARCHAR (255)    A description of this node group.

Table A.4. NODE_GROUP


A.5. NODE_GROUP_LINK

A source node_group sends its data updates to a target NodeGroup using a pull, push, or custom technique.

NameType / SizeDefaultPK FKnot nullDescription
SOURCE_NODE_GROUP_ID VARCHAR (50) PK X The node group where data changes should be captured.
TARGET_NODE_GROUP_ID VARCHAR (50) PK X The node group where data changes will be sent.
DATA_EVENT_ACTION CHAR (1) W X The notification scheme used to send data changes to the target node group. (P = Push, W = Wait for Pull)

Table A.5. NODE_GROUP_LINK


A.6. NODE_HOST

Representation of an physical workstation or server that is hosting the SymmetricDS software. In a clustered environment there may be more than one entry per node in this table.

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X A unique identifier for a node.
HOST_NAME VARCHAR (255) PK X The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id
IP_ADDRESS VARCHAR (50)    The ip address for the host.
OS_USER VARCHAR (50)    The user SymmetricDS is running under
OS_NAME VARCHAR (50)    The name of the OS
OS_ARCH VARCHAR (50)    The hardware architecture of the OS
OS_VERSION VARCHAR (50)    The version of the OS
AVAILABLE_PROCESSORS INTEGER 0   The number of processors available to use.
FREE_MEMORY_BYTES BIGINT 0   The amount of free memory available to the JVM.
TOTAL_MEMORY_BYTES BIGINT 0   The amount of total memory available to the JVM.
MAX_MEMORY_BYTES BIGINT 0   The max amount of memory available to the JVM.
JAVA_VERSION VARCHAR (50)    The version of java that SymmetricDS is running as.
JAVA_VENDOR VARCHAR (255)    The vendor of java that SymmetricDS is running as.
SYMMETRIC_VERSION VARCHAR (50)    The version of SymmetricDS running at this node.
TIMEZONE_OFFSET VARCHAR (6)    The timezone offset in RFC822 format at the time of the last heartbeat.
HEARTBEAT_TIME TIMESTAMP     The last timestamp when the node sent a heartbeat, which is attempted every ten minutes by default.
LAST_RESTART_TIME TIMESTAMP   X Timestamp when this instance was last restarted.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.

Table A.6. NODE_HOST


A.7. NODE_HOST_CHANNEL_STATS

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X A unique identifier for a node.
HOST_NAME VARCHAR (255) PK X The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id
CHANNEL_ID VARCHAR (20) PK X The channel_id of the channel that data changes will flow through.
START_TIME TIMESTAMP  PK X
END_TIME TIMESTAMP  PK X
DATA_ROUTED BIGINT 0   Indicate the number of data rows that have been routed during this period.
DATA_UNROUTED BIGINT 0  
DATA_EVENT_INSERTED BIGINT 0   Indicate the number of data rows that have been routed during this period.
DATA_EXTRACTED BIGINT 0  
DATA_BYTES_EXTRACTED BIGINT 0  
DATA_EXTRACTED_ERRORS BIGINT 0  
DATA_BYTES_SENT BIGINT 0  
DATA_SENT BIGINT 0  
DATA_SENT_ERRORS BIGINT 0  
DATA_LOADED BIGINT 0  
DATA_BYTES_LOADED BIGINT 0  
DATA_LOADED_ERRORS BIGINT 0  

Table A.7. NODE_HOST_CHANNEL_STATS


A.8. NODE_HOST_STATS

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X A unique identifier for a node.
HOST_NAME VARCHAR (255) PK X The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id
START_TIME TIMESTAMP  PK X
END_TIME TIMESTAMP  PK X
RESTARTED BIGINT 0 X Indicate that a restart occurred during this period.
NODES_PULLED BIGINT 0  
TOTAL_NODES_PULL_TIME BIGINT 0  
NODES_PUSHED BIGINT 0  
TOTAL_NODES_PUSH_TIME BIGINT 0  
NODES_REJECTED BIGINT 0  
NODES_REGISTERED BIGINT 0  
NODES_LOADED BIGINT 0  
NODES_DISABLED BIGINT 0  
PURGED_DATA_ROWS BIGINT 0  
PURGED_DATA_EVENT_ROWS BIGINT 0  
PURGED_BATCH_OUTGOING_ROWS BIGINT 0  
PURGED_BATCH_INCOMING_ROWS BIGINT 0  
TRIGGERS_CREATED_COUNT BIGINT    
TRIGGERS_REBUILT_COUNT BIGINT    
TRIGGERS_REMOVED_COUNT BIGINT    

Table A.8. NODE_HOST_STATS


A.9. NODE_HOST_JOB_STATS

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X A unique identifier for a node.
HOST_NAME VARCHAR (255) PK X The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id
JOB_NAME VARCHAR (50) PK X
START_TIME TIMESTAMP  PK X
END_TIME TIMESTAMP  PK X
PROCESSED_COUNT BIGINT 0  

Table A.9. NODE_HOST_JOB_STATS


A.10. CHANNEL

This table represents a category of data that can be synchronized independently of other channels. Channels allow control over the type of data flowing and prevents one type of synchronization from contending with another.

NameType / SizeDefaultPK FKnot nullDescription
CHANNEL_ID VARCHAR (20) PK X A unique identifer, usually named something meaningful, like 'sales' or 'inventory'.
PROCESSING_ORDER INTEGER 1 X Order of sequence to process channel data.
MAX_BATCH_SIZE INTEGER 1000 X The maximum number of Data Events to process within a batch for this channel.
MAX_BATCH_TO_SEND INTEGER 60 X The maximum number of batches to send during a 'synchronization' between two nodes. A 'synchronization' is equivalent to a push or a pull. If there are 12 batches ready to be sent for a channel and max_batch_to_send is equal to 10, then only the first 10 batches will be sent.
MAX_DATA_TO_ROUTE INTEGER 100000 X The maximum number of data rows to route for a channel at a time.
EXTRACT_PERIOD_MILLIS INTEGER 0 X The minimum number of milliseconds allowed between attempts to extract data for targeted at a node_id.
ENABLED INTEGER (1) 1 X Indicates whether channel is enabled or not.
USE_OLD_DATA_TO_ROUTE INTEGER (1) 1 X Indicates whether to read the old data during routing.
USE_ROW_DATA_TO_ROUTE INTEGER (1) 1 X Indicates whether to read the row data during routing.
USE_PK_DATA_TO_ROUTE INTEGER (1) 1 X Indicates whether to read the pk data during routing.
CONTAINS_BIG_LOB INTEGER (1) 0 X Provides SymmetricDS a hint as to whether this channel will contain big lobs data. Some databases have shortcuts that SymmetricDS can take advantage of if it knows that the lob columns in sym_data aren't going to contain large lobs. The definition of how big a 'large' lob is will differ from database to database.
BATCH_ALGORITHM VARCHAR (50) default X The algorithm to use when batching data on this channel. Possible values are: 'default', 'transactional', and 'nontransactional'
DESCRIPTION VARCHAR (255)    Description on the type of data carried in this channel.

Table A.10. CHANNEL


A.11. NODE_CHANNEL_CTL

Used to ignore or suspend a channel. A channel that is ignored will have its data_events batched and they will immediately be marked as 'OK' without sending them. A channel that is suspended is skipped when batching data_events.

NameType / SizeDefaultPK FKnot nullDescription
NODE_ID VARCHAR (50) PK X Unique identifier for a node.
CHANNEL_ID VARCHAR (20) PK X The name of the channel_id that is being controlled.
SUSPEND_ENABLED INTEGER (1) 0   Indicates if this channel is suspended, which prevents its Data Events from being batched.
IGNORE_ENABLED INTEGER (1) 0   Indicates if this channel is ignored, which marks its Data Events as if they were actually processed.
LAST_EXTRACT_TIME TIMESTAMP     Record the last time data was extract for a node and a channel.

Table A.11. NODE_CHANNEL_CTL


A.12. NODE_GROUP_CHANNEL_WINDOW

An optional window of time for which a node group and channel will be active.

NameType / SizeDefaultPK FKnot nullDescription
NODE_GROUP_ID VARCHAR (50) PK X The node_group_id that this window applies to.
CHANNEL_ID VARCHAR (20) PK X The channel_id that this window applies to.
START_TIME TIME  PK X The start time for the active window.
END_TIME TIME  PK X The end time for the active window. Note that if the end_time is less than the start_time then the window crosses a day boundary.
ENABLED INTEGER (1) 0 X Enable this window. If this is set to '0' then this window is ignored.

Table A.12. NODE_GROUP_CHANNEL_WINDOW


A.13. TRIGGER

Configures database triggers that capture changes in the database. Configuration of which triggers are generated for which tables is stored here. Triggers are created in a node's database if the source_node_group_id of a router is mapped to a row in this table.

NameType / SizeDefaultPK FKnot nullDescription
TRIGGER_ID VARCHAR (50) PK X Unique identifier for a trigger.
SOURCE_CATALOG_NAME VARCHAR (50)    Optional name for the catalog the configured table is in.
SOURCE_SCHEMA_NAME VARCHAR (50)    Optional name for the schema a configured table is in.
SOURCE_TABLE_NAME VARCHAR (50)  X The name of the source table that will have a trigger installed to watch for data changes.
CHANNEL_ID VARCHAR (20)  X The channel_id of the channel that data changes will flow through.
SYNC_ON_UPDATE INTEGER (1) 1 X Whether or not to install an update trigger.
SYNC_ON_INSERT INTEGER (1) 1 X Whether or not to install an insert trigger.
SYNC_ON_DELETE INTEGER (1) 1 X Whether or not to install an delete trigger.
SYNC_ON_INCOMING_BATCH INTEGER (1) 0 X Whether or not an incoming batch that loads data into this table should cause the triggers to capture data_events. Be careful turning this on, because an update loop is possible.
NAME_FOR_UPDATE_TRIGGER VARCHAR (50)    Override the default generated name for the update trigger.
NAME_FOR_INSERT_TRIGGER VARCHAR (50)    Override the default generated name for the insert trigger.
NAME_FOR_DELETE_TRIGGER VARCHAR (50)    Override the default generated name for the delete trigger.
SYNC_ON_UPDATE_CONDITION LONGVARCHAR     Specify a condition for the update trigger firing using an expression specific to the database.
SYNC_ON_INSERT_CONDITION LONGVARCHAR     Specify a condition for the insert trigger firing using an expression specific to the database.
SYNC_ON_DELETE_CONDITION LONGVARCHAR     Specify a condition for the delete trigger firing using an expression specific to the database.
EXTERNAL_SELECT LONGVARCHAR     Specify a SQL select statement that returns a single result. It will be used in the generated database trigger to populate the EXTERNAL_DATA field on the data table.
TX_ID_EXPRESSION LONGVARCHAR     Override the default expression for the transaction identifier that groups the data changes that were committed together.
EXCLUDED_COLUMN_NAMES LONGVARCHAR     Specify a comma-delimited list of columns that should not be synchronized from this table. Note that if a primary key is found in this list, it will be ignored.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
LAST_UPDATE_BY VARCHAR (50)    The user who last updated this entry.
LAST_UPDATE_TIME TIMESTAMP   X Timestamp when a user last updated this entry.

Table A.13. TRIGGER


A.14. ROUTER

Configure a type of router from one node group to another. Note that routers are mapped to triggers through trigger_routers.

NameType / SizeDefaultPK FKnot nullDescription
ROUTER_ID VARCHAR (50) PK X Unique description of a specific router
TARGET_CATALOG_NAME VARCHAR (50)    Optional name for the catalog a target table is in. Only use this if the target table is not in the default catalog.
TARGET_SCHEMA_NAME VARCHAR (50)    Optional name of the schema a target table is in. On use this if the target table is not in the default schema.
TARGET_TABLE_NAME VARCHAR (50)    Optional name for a target table. Only use this if the target table name is different than the source.
SOURCE_NODE_GROUP_ID VARCHAR (50)  X Routers with this node_group_id will install triggers that are mapped to this router.
TARGET_NODE_GROUP_ID VARCHAR (50)  X The node_group_id for nodes to route data to. Note that routing can be further narrowed down by the configured router_type and router_expression.
ROUTER_TYPE VARCHAR (50)    The name of a specific type of router. Out of the box routers are 'default','column','bsh', and 'subselect.' Custom routers can be configured as extension points.
ROUTER_EXPRESSION LONGVARCHAR     An expression that is specific to the type of router that is configured in router_type. See the documentation for each router for more details.
SYNC_ON_UPDATE INTEGER (1) 1 X Flag that indicates that this router should route updates.
SYNC_ON_INSERT INTEGER (1) 1 X Flag that indicates that this router should route inserts.
SYNC_ON_DELETE INTEGER (1) 1 X Flag that indicates that this router should route deletes.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
LAST_UPDATE_BY VARCHAR (50)    The user who last updated this entry.
LAST_UPDATE_TIME TIMESTAMP   X Timestamp when a user last updated this entry.

Table A.14. ROUTER


A.15. TRIGGER_ROUTER

Map a trigger to a router.

NameType / SizeDefaultPK FKnot nullDescription
TRIGGER_ID VARCHAR (50) PK X The id of a trigger.
ROUTER_ID VARCHAR (50) PK X The id of a router.
INITIAL_LOAD_ORDER INTEGER 1 X Order sequence of this table when an initial load is sent to a node.
INITIAL_LOAD_SELECT LONGVARCHAR     Optional expression that can be used to pair down the data selected from a table during the initial load process.
PING_BACK_ENABLED INTEGER (1) 0 X When enabled, the node will route data that originated from a node back to that node. This attribute is only effective if sync_on_incoming_batch is set to 1.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
LAST_UPDATE_BY VARCHAR (50)    The user who last updated this entry.
LAST_UPDATE_TIME TIMESTAMP   X Timestamp when a user last updated this entry.

Table A.15. TRIGGER_ROUTER


A.16. PARAMETER

Provides a way to manage most SymmetricDS settings in the database.

NameType / SizeDefaultPK FKnot nullDescription
EXTERNAL_ID VARCHAR (50) PK X Target the parameter at a specific external id. To target all nodes, use the value of 'ALL.'
NODE_GROUP_ID VARCHAR (50) PK X Target the parameter at a specific node group id. To target all groups, use the value of 'ALL.'
PARAM_KEY VARCHAR (100) PK X The name of the parameter.
PARAM_VALUE LONGVARCHAR     The value of the parameter.

Table A.16. PARAMETER


A.17. REGISTRATION_REDIRECT

Provides a way for a centralized registration server to redirect registering nodes to their prospective parent node in a multi-tiered deployment.

NameType / SizeDefaultPK FKnot nullDescription
REGISTRANT_EXTERNAL_ID VARCHAR (50) PK X Maps the external id of a registration request to a different parent node.
REGISTRATION_NODE_ID VARCHAR (50)  X The node_id of the node that a registration request should be redirected to.

Table A.17. REGISTRATION_REDIRECT


A.18. REGISTRATION_REQUEST

Audits when a node registers or attempts to register.

NameType / SizeDefaultPK FKnot nullDescription
NODE_GROUP_ID VARCHAR (50)    The node group that this node belongs to, such as 'store'.
EXTERNAL_ID VARCHAR (50)    A domain-specific identifier for context within the local system. For example, the retail store number.
STATUS CHAR (2)  X The current status of the registration attempt. Valid statuses are NR (not registered), IG (ignored), OK (sucessful)
HOST_NAME VARCHAR (255)  X The host name of a workstation or server. If more than one instance of SymmetricDS runs on the same server, then this value can be a 'server id' specified by -Druntime.symmetric.cluster.server.id
IP_ADDRESS VARCHAR (50)  X The ip address for the host.
ATTEMPT_COUNT INTEGER 0   The number of registration attempts.
REGISTERED_NODE_ID VARCHAR (50)    A unique identifier for a node.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
LAST_UPDATE_BY VARCHAR (50)    The user who last updated this entry.
LAST_UPDATE_TIME TIMESTAMP   X Timestamp when a user last updated this entry.

Table A.18. REGISTRATION_REQUEST


A.19. TRIGGER_HIST

A history of a table's definition and the trigger used to capture data from the table. When a database trigger captures a data change, it references a trigger_hist entry so it is possible to know which columns the data represents. trigger_hist entries are made during the sync trigger process, which runs at each startup, each night in the syncTriggersJob, or any time the syncTriggers() JMX method is manually invoked. A new entry is made when a table definition or a trigger definition is changed, which causes a database trigger to be created or rebuilt.

NameType / SizeDefaultPK FKnot nullDescription
TRIGGER_HIST_ID INTEGER  PK X Unique identifier for a trigger_hist entry
TRIGGER_ID VARCHAR (50)  X Unique identifier for a trigger
SOURCE_TABLE_NAME VARCHAR (50) X The name of the source table that will have a trigger installed to watch for data changes.
SOURCE_CATALOG_NAME VARCHAR (50)    The catalog name where the source table resides.
SOURCE_SCHEMA_NAME VARCHAR (50)    The schema name where the source table resides.
NAME_FOR_UPDATE_TRIGGER VARCHAR (50)  X The name used when the insert trigger was created.
NAME_FOR_INSERT_TRIGGER VARCHAR (50)  X The name used when the update trigger was created.
NAME_FOR_DELETE_TRIGGER VARCHAR (50)  X The name used when the delete trigger was created.
TABLE_HASH BIGINT   X A hash of the table definition, used to detect changes in the definition.
TRIGGER_ROW_HASH BIGINT   X A hash of the trigger definition. If changes are detected to the values that affect a trigger definition, then the trigger will be regenerated.
COLUMN_NAMES LONGVARCHAR   X The column names defined on the table. The column names are stored in comma-separated values (CSV) format.
PK_COLUMN_NAMES LONGVARCHAR   X The primary key column names defined on the table. The column names are stored in comma-separated values (CSV) format.
LAST_TRIGGER_BUILD_REASON CHAR (1)  X The following reasons for a change are possible: New trigger that has not been created before (N); Schema changes in the table were detected (S); Configuration changes in Trigger (C); Trigger was missing (T).
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
INACTIVE_TIME TIMESTAMP     The date and time when a trigger was inactivated.

Table A.19. TRIGGER_HIST


A.20. DATA

The captured data change that occurred to a row in the database. Entries in data are created by database triggers.

NameType / SizeDefaultPK FKnot nullDescription
DATA_ID INTEGER  PK X Unique identifier for a data.
TABLE_NAME VARCHAR (50)  X The name of the table in which a change occurred that this entry records.
EVENT_TYPE CHAR (1)  X The type of event captured by this entry. For triggers, this is the change that occurred, which is 'I' for insert, 'U' for update, or 'D' for delete. Other events include: 'R' for reloading the entire table (or subset of the table) to the node; 'S' for running dynamic SQL at the node, which is used for adhoc administration.
ROW_DATA LONGVARCHAR     The captured data change from the synchronized table. The column values are stored in comma-separated values (CSV) format.
PK_DATA LONGVARCHAR     The primary key values of the captured data change from the synchronized table. This data is captured for updates and deletes. The primary key values are stored in comma-separated values (CSV) format.
OLD_DATA LONGVARCHAR     The captured data values prior to the update. The column values are stored in CSV format.
TRIGGER_HIST_ID INTEGER   X The foreign key to the trigger_hist entry that contains the primary key and column names for the table being synchronized.
CHANNEL_ID VARCHAR (20)    The channel that this data belongs to, such as 'prices'
TRANSACTION_ID VARCHAR (255)    An optional transaction identifier that links multiple data changes together as the same transaction.
SOURCE_NODE_ID VARCHAR (50)    If this data was capture
EXTERNAL_DATA VARCHAR (50)    A field that can be populated by a trigger that uses the EXTERNAL_SELECT
CREATE_TIME TIMESTAMP     Timestamp when this entry was created.

Table A.20. DATA


A.21. DATA_REF

Used only when routing.data.reader.type is set to 'ref.' Table that tracks the last known data_id that has been processed. This table is used so that joins to find unprocessed data can be better optimized.

NameType / SizeDefaultPK FKnot nullDescription
REF_DATA_ID INTEGER  PK X The data_id that can be used to limit the search of the data table to rows that are greater than this data_id value.
REF_TIME TIMESTAMP     The time when the ref_data_id was recorded. It is used as the base time to calculate timeouts for gaps in the data_ids.

Table A.21. DATA_REF


A.22. DATA_GAP

Used only when routing.data.reader.type is set to 'gap.' Table that tracks gaps in the data table so that they may be processed efficiently, if data shows up. Gaps can show up in the data table if a database transaction is rolled back.

NameType / SizeDefaultPK FKnot nullDescription
START_ID INTEGER  PK X The first missing data_id from the data table where a gap is detected. This could be the last data_id inserted plus one.
END_ID INTEGER  PK X The last missing data_id from the data table where a gap is detected. If the start_id is the last data_id inserted plus one, then this field is filled in with a -1.
STATUS CHAR (2)    GP, SK, or FL. GP means there is a detected gap. FL means that the gap has been filled. SK means that the gap has been skipped either because the gap expired or because no database transaction was detected which means that no data will be committed to fill in the gap.
CREATE_TIME TIMESTAMP   X Timestamp when this entry was created.
LAST_UPDATE_HOSTNAME VARCHAR (255)    The host who last updated this entry.
LAST_UPDATE_TIME TIMESTAMP   X Timestamp when a user last updated this entry.

Table A.22. DATA_GAP


A.23. DATA_EVENT

Represents routing of a data row to one or more nodes. Entries in data_event are created by database triggers.

NameType / SizeDefaultPK FKnot nullDescription
DATA_ID INTEGER  PK X Id of the data to be routed.
BATCH_ID INTEGER -1PK X The node_id of the node that is to receive the data.
ROUTER_ID VARCHAR (50) PK X The router_id of the router that routed this data_event.
CREATE_TIME TIMESTAMP     Timestamp when this entry was created.

Table A.23. DATA_EVENT


A.24. OUTGOING_BATCH

Used for tracking the sending a collection of data to a node in the system. A new outgoing_batch is created and given a status of 'NE'. After sending the outgoing_batch to its target node, the status becomes 'SE'. The node responds with either a success status of 'OK' or an error status of 'ER'. An error while sending to the node also results in an error status of 'ER' regardless of whether the node sends that acknowledgement.

NameType / SizeDefaultPK FKnot nullDescription
BATCH_ID INTEGER  PK X A unique id for the batch.
NODE_ID VARCHAR (50)    The node that this batch is targeted at.
CHANNEL_ID VARCHAR (20)    The channel that this batch is part of.
STATUS CHAR (2)    The current status of the Batch can be newly created (NE), sent to a Node (SE), acknowledged as successful (OK), and error (ER).
LOAD_FLAG INTEGER (1) 0   A flag that indicates that this batch is part of an initial load.
ERROR_FLAG INTEGER (1) 0   A flag that indicates that this batch was in error during the last synchornization attempt.
BYTE_COUNT BIGINT 0 X The number of bytes that were sent as part of this batch.
EXTRACT_COUNT BIGINT 0 X The number of times this an attempt to extract this batch occurred.
SENT_COUNT BIGINT 0 X The number of times this batch was sent. A batch can be sent multiple times if an ACK is not received.
LOAD_COUNT BIGINT 0 X The number of times an attempt to load this batch occurred.
DATA_EVENT_COUNT BIGINT 0 X The number of data_events that are part of this batch.
RELOAD_EVENT_COUNT BIGINT 0 X The number of reload events that are part of this batch.
INSERT_EVENT_COUNT BIGINT 0 X The number of insert events that are part of this batch.
UPDATE_EVENT_COUNT BIGINT 0 X The number of update events that are part of this batch.
DELETE_EVENT_COUNT BIGINT 0 X The number of delete events that are part of this batch.
OTHER_EVENT_COUNT BIGINT 0 X The number of other event types that are part of this batch. This includes any events types that are not a reload, insert, update or delete event type.
ROUTER_MILLIS BIGINT 0 X The number of milliseconds spent creating this batch.
NETWORK_MILLIS BIGINT 0 X The number of milliseconds spent transfering this batch across the network.
FILTER_MILLIS BIGINT 0 X The number of milliseconds spent in filters processing data.
LOAD_MILLIS BIGINT 0 X The number of milliseconds spent loading the data into the target database.
EXTRACT_MILLIS BIGINT 0 X The number of milliseconds spent extracting the data out of the source database.
SQL_STATE VARCHAR (10)    For a status of error (ER), this is the XOPEN or SQL 99 SQL State.
SQL_CODE INTEGER 0 X For a status of error (ER), this is the error code from the database that is specific to the vendor.
SQL_MESSAGE LONGVARCHAR     For a status of error (ER), this is the error message that describes the error.
FAILED_DATA_ID BIGINT 0 X For a status of error (ER), this is the data_id that was being processed when the batch failed.
LAST_UPDATE_HOSTNAME VARCHAR (255)    The host name of the process that last did work on this batch.
LAST_UPDATE_TIME TIMESTAMP     Timestamp when a process last updated this entry.
CREATE_TIME TIMESTAMP     Timestamp when this entry was created.

Table A.24. OUTGOING_BATCH


A.25. INCOMING_BATCH

The incoming_batch is used for tracking the status of loading an outgoing_batch from another node. Data is loaded and commited at the batch level. The status of the incoming_batch is either successful (OK) or error (ER).

NameType / SizeDefaultPK FKnot nullDescription
BATCH_ID INTEGER (50) PK X The id of the outgoing_batch that is being loaded.
NODE_ID VARCHAR (50) PK X The node_id of the source of the batch being loaded.
CHANNEL_ID VARCHAR (20)    The channel_id of the batch being loaded.
STATUS CHAR (2)    The current status of the batch can be loading (LD), successfully loaded (OK), in error (ER) or skipped (SK)
ERROR_FLAG INTEGER (1) 0   A flag that indicates that this batch was in error during the last synchornization attempt.
NETWORK_MILLIS BIGINT 0 X The number of milliseconds spent transfering this batch across the network.
FILTER_MILLIS BIGINT 0 X The number of milliseconds spent in filters processing data.
DATABASE_MILLIS BIGINT 0 X The number of milliseconds spent loading the data into the target database.
FAILED_ROW_NUMBER BIGINT 0 X For a status of error (ER), this is the data_id that was being processed when the batch failed.
BYTE_COUNT BIGINT 0 X The number of bytes that were sent as part of this batch.
STATEMENT_COUNT BIGINT 0 X The number of statements run to load this batch.
FALLBACK_INSERT_COUNT BIGINT 0 X The number of times an update was turned into an insert because the data was not already in the target database.
FALLBACK_UPDATE_COUNT BIGINT 0 X The number of times an insert was turned into an update because a data row already existed in the target database.
MISSING_DELETE_COUNT BIGINT 0 X THe number of times a delete did not effect the database because the row was already deleted.
SKIP_COUNT BIGINT 0 X The number of times a batch was sent and skipped because it had already been loaded according to incoming_batch
SQL_STATE VARCHAR (10)    For a status of error (ER), this is the XOPEN or SQL 99 SQL State.
SQL_CODE INTEGER 0 X For a status of error (ER), this is the error code from the database that is specific to the vendor.
SQL_MESSAGE LONGVARCHAR     For a status of error (ER), this is the error message that describes the error.
LAST_UPDATE_HOSTNAME VARCHAR (255)    The host name of the process that last did work on this batch.
LAST_UPDATE_TIME TIMESTAMP     Timestamp when a process last updated this entry.
CREATE_TIME TIMESTAMP     Timestamp when this entry was created.

Table A.25. INCOMING_BATCH


A.26. LOCK

Contains semaphores that are set when processes run, so that only one server can run a process at a time. Enable this feature by using the cluster.lock.during.xxxx parameters.

NameType / SizeDefaultPK FKnot nullDescription
LOCK_ACTION VARCHAR (50) PK X The process that needs a lock.
LOCKING_SERVER_ID VARCHAR (255)    The name of the server that currently has a lock. This is typically a host name, but it can be overridden using the -Druntime.symmetric.cluster.server.id=name System property.
LOCK_TIME TIMESTAMP     The time a lock is aquired. Use the cluster.lock.timeout.ms to specify a lock timeout period.
LAST_LOCK_TIME TIMESTAMP     Timestamp when a process last updated this entry.
LAST_LOCKING_SERVER_ID VARCHAR (255)    The server id of the process that last did work on this batch.

Table A.26. LOCK