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.
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.
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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 (2000) | 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. |
Table A.1. NODE
Security features like node passwords and open registration flag are stored in the node_security table.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| NODE_ID | VARCHAR (50) | PK | X | Unique identifier for a node. |
Table A.3. NODE_IDENTITY
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| NODE_GROUP_ID | VARCHAR (50) | PK | X | Unique identifier for a node group, usually named something meaningful, like 'store' or 'warehouse'. | |
| DESCRIPTION | VARCHAR (1000) | A description of this node group. |
Table A.4. NODE_GROUP
A source node_group sends its data updates to a target NodeGroup using a pull, push, or custom technique.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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. | |
| 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 (1000) | Description on the type of data carried in this channel. |
Table A.7. CHANNEL
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.8. NODE_CHANNEL_CTL
An optional window of time for which a node group and channel will be active.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.9. NODE_GROUP_CHANNEL_WINDOW
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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 | VARCHAR (500) | 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. | |||
| 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.10. TRIGGER
Configure a type of router from one node group to another. Note that routers are mapped to triggers through trigger_routers.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.11. ROUTER
Map a trigger to a router.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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. | |||
| 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.12. TRIGGER_ROUTER
Provides a way to manage most SymmetricDS settings in the database.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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 | VARCHAR (1000) | The value of the parameter. |
Table A.13. PARAMETER
Provides a way for a centralized registration server to redirect registering nodes to their prospective parent node in a multi-tiered deployment.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.14. REGISTRATION_REDIRECT
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.15. TRIGGER_HIST
The captured data change that occurred to a row in the database. Entries in data are created by database triggers.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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 (1000) | 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.16. DATA
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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.17. DATA_REF
Represents routing of a data row to one or more nodes. Entries in data_event are created by database triggers.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| DATA_ID | INTEGER | PK | X | Id of the data to be routed. | |
| BATCH_ID | INTEGER | -1 | PK | 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.18. DATA_EVENT
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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). | |||
| BYTE_COUNT | INTEGER | The number of bytes that were sent as part of this batch. | |||
| SENT_COUNT | INTEGER | The number of times this batch was sent. A batch can be sent multiple times if an ACK is not received. | |||
| DATA_EVENT_COUNT | INTEGER | The number of data_events that are part of this batch. | |||
| ROUTER_MILLIS | INTEGER | The number of milliseconds spent creating this batch. | |||
| NETWORK_MILLIS | INTEGER | The number of milliseconds spent transfering this batch across the network. | |||
| FILTER_MILLIS | INTEGER | The number of milliseconds spent in filters processing data. | |||
| LOAD_MILLIS | INTEGER | The number of milliseconds spent loading the data into the target database. | |||
| EXTRACT_MILLIS | INTEGER | 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 | For a status of error (ER), this is the error code from the database that is specific to the vendor. | |||
| SQL_MESSAGE | VARCHAR (1000) | For a status of error (ER), this is the error message that describes the error. | |||
| FAILED_DATA_ID | INTEGER | For a status of error (ER), this is the data_id that was being processed when the batch failed. | |||
| LAST_UPDATE_HOSTNAME | VARCHAR (50) | 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.19. OUTGOING_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).
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| 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 successfully loaded (OK) or error (ER). | |||
| NETWORK_MILLIS | INTEGER | The number of milliseconds spent transfering this batch across the network. | |||
| FILTER_MILLIS | INTEGER | The number of milliseconds spent in filters processing data. | |||
| DATABASE_MILLIS | INTEGER | The number of milliseconds spent loading the data into the target database. | |||
| FAILED_ROW_NUMBER | INTEGER | For a status of error (ER), this is the data_id that was being processed when the batch failed. | |||
| BYTE_COUNT | INTEGER | The number of bytes that were sent as part of this batch. | |||
| STATEMENT_COUNT | INTEGER | The number of statements run to load this batch. | |||
| FALLBACK_INSERT_COUNT | INTEGER | The number of times an update was turned into an insert because the data was not already in the target database. | |||
| FALLBACK_UPDATE_COUNT | INTEGER | The number of times an insert was turned into an update because a data row already existed in the target database. | |||
| MISSING_DELETE_COUNT | INTEGER | THe number of times a delete did not effect the database because the row was already deleted. | |||
| SKIP_COUNT | INTEGER | 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 | For a status of error (ER), this is the error code from the database that is specific to the vendor. | |||
| SQL_MESSAGE | VARCHAR (1000) | For a status of error (ER), this is the error message that describes the error. | |||
| LAST_UPDATE_HOSTNAME | VARCHAR (50) | 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.20. INCOMING_BATCH
Record statistics for the attached SymmetricDS process.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| NODE_ID | VARCHAR (50) | ||||
| HOST_NAME | VARCHAR (50) | ||||
| STATISTIC_NAME | VARCHAR (50) | ||||
| STATISTIC_TOTAL | DECIMAL (18) | ||||
| STATISTIC_COUNT | BIGINT | ||||
| CAPTURE_START_TIME | TIMESTAMP | ||||
| CAPTURE_END_TIME | TIMESTAMP |
Table A.21. STATISTIC
Controls the statistic thresholds at which JMX notifications will be fired.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| STATISTIC_NAME | VARCHAR (50) | PK | X | ||
| THRESHOLD_TOTAL_MAX | DECIMAL (18) | ||||
| THRESHOLD_AVG_MAX | DECIMAL (18) | ||||
| THRESHOLD_COUNT_MAX | BIGINT | ||||
| THRESHOLD_TOTAL_MIN | DECIMAL (18) | ||||
| THRESHOLD_COUNT_MIN | BIGINT | ||||
| THRESHOLD_AVG_MIN | DECIMAL (18) |
Table A.22. STATISTIC_ALERT
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.
| Name | Type / Size | Default | PK FK | not null | Description |
|---|---|---|---|---|---|
| LOCK_ID | VARCHAR (50) | PK | X | An id for the process that is running. Many times this will be a value of 'common.' It can also be a specfic node_id. | |
| LOCK_ACTION | VARCHAR (50) | PK | X | The process that needs a lock. | |
| LOCKING_SERVER_ID | VARCHAR (100) | 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. |
Table A.23. LOCK
