SymmetricDS Pro 3.8 User Guide

Change Data Capture (CDC) for tables uses database triggers that fire and record changes as comma-separated values into a runtime table called DATA. For file sync, a similar mechanism is used, except changes to the metadata about files are captured. The changes are recorded as insert, update, and delete event types. The subsystem installs and maintains triggers on tables based on the configuration provided by the user, and it can automatically detect schema changes on tables and regenerate triggers.

Routers run across new changes to determine which target nodes will receive the data. The user configures which routers to use and what criteria is used to match data, creating subsets of rows if needed. Changes are grouped into batches and assigned to target nodes in the DATA_EVENT and OUTGOING_BATCH tables.

Changes are extracted from the runtime tables and prepared to be sent as an outgoing batch. If large objects are configured for streaming instead of capture, they are queried from the table. Special event types like "reload" for Initial Loads are also processed.

If transformations are configured, they operate on the change data either during the extract phase at the source node or the load phase at the target node. The node’s database can be queried to enhance the data. Data is transformed into the tables, rows, columns, and values needed for either the outgoing or incoming batch.

The synchronization sends batches to target nodes to be loaded. Multiple batches can be configured to send during a single synchronization. The status of the batch is updated on the OUTGOING_BATCH table as it processes. An acknowledgment is received from target nodes and recorded on the batch.

The synchronization receives batches from remote nodes and the data is loaded. The status of the batch is updated on the INCOMING_BATCH table as it processes. The resulting status of the batch is returned to the source node in an acknowledgment.

This is an arbitrary name that is used to access a specific engine using an HTTP URL. Each node configured in the engines directory must have a unique engine name. The engine name is also used for the domain name of registered JMX beans.

The node group that this node is a member of. Synchronization is specified between node groups, which means you only need to specify it once for multiple nodes in the same group.

The external id for this node has meaning to the user and provides integration into the system where it is deployed. For example, it might be a retail store number or a region number. The external id can be used in expressions for conditional and subset data synchronization. Behind the scenes, each node has a unique sequence number for tracking synchronization events. That makes it possible to assign the same external id to multiple nodes, if desired.

The URL where this node can be contacted for synchronization. At startup and during each heartbeat, the node updates its entry in the database with this URL. The sync url is of the format: http://{hostname}:{port}/{webcontext}/sync/{engine.name}

The URL where this node can connect for registration to receive its configuration. The registration server is part of SymmetricDS and is enabled as part of the deployment. This is typically equal to the value of the sync.url of the registration server.

The class name of the JDBC driver.

The JDBC URL used to connect to the database.

The database username, which is used to login, create, and update SymmetricDS tables.

The password for the database user.

Unique identifier for the group.

Description of the group that is available through the console.

The source group of the communication link.

Defines how the source and target groups will communicate.

The target group of the communication link.

Determines if configuration is also sent through this group link. By default this is checked and configuration will communicate on this path. There are configurations that might cause configuration to continuously loop through the network as a result this might need to be unchecked for some links.

Allows the communication link to send in the reverse direction if specified on the channel. A push link can be overridden to pull and a pull link can be overridden to push using a setting on the channel.

Unique description of a specific router

The group link used for the source and target node groups of this router

The type of router. Standard router types are listed below. Custom routers can be configured as extension points.

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.

If set then the source catalog and source schema are sent to the target to be used to find the target table.

Optional name of catalog where a target table is located. If this field is unspecified, the catalog will be either the default catalog at the target node or the "source catalog name" from the table trigger, depending on how "use source catalog schema" is set for the router. Variables are substituted for $(sourceNodeId), $(sourceExternalId), $(sourceNodeGroupId), $(targetNodeId), $(targetExternalId), $(targetNodeGroupId), and $(none).

Optional name of schema where a target table is located. If this field is unspecified, the schema will be either the default schema at the target node or the "source schema name" from the table trigger, depending on how "use source catalog schema" is set for the router. Variables are substituted for $(sourceNodeId), $(sourceExternalId), $(sourceNodeGroupId), $(targetNodeId), $(targetExternalId), $(targetNodeGroupId), and $(none).

Flag that indicates that this router should send updated rows from nodes in the source node group to nodes in the target node group.

Flag that indicates that this router should send inserted rows from nodes in the source node group to nodes in the target node group.

Flag that indicates that this router should send deleted rows from nodes in the source node group to nodes in the target node group.

Optional name for a target table. Only use this if the target table name is different than the source.

Identifier used through the system to identify a given channel.

Numeric value to determine the order in which a channel will be processed. Channels will be processed in ascending order.

Batching is the grouping of data, by channel, to be transferred and committed at the client together.

Specifies the maximum number of data events to process within a batch for this channel.

Specifies the maximum number of batches to send for a given channel during a 'synchronization' between two nodes. A 'synchronization' is equivalent to a push or a pull. For example, 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 even though 12 batches are ready.

Specifies the maximum number of data rows to route for a channel at a time.

Specifies the maximum network transfer rate in kilobytes per second. Use zero to indicate unlimited. When throttling the channel, make sure the channel is on its own queue or within a queue of channels that are throttled at the same rate. This is currently only implemented when staging is enabled.

Determines how data will be loaded into the target tables. These are used during an initial load or a reverse initial load. Data loaders do not always have to load into the target relational database. They can write to a file, a web service, or any other type of non-relational data source. Data loaders can also use other techniques to increase performance of data loads into the target relation database.

For a node group link that is reversible, the channel can specify either "push" or "pull" to override the default group link communication. If this field is empty, the default group link communication is used.

Indicates whether the channel is enabled or disabled. If a channel is disabled, data is still captured for changes that occur on the source system, but it will not be routed and sent to the target until the channel is re-enabled.

Indicates whether a channel is available for initial loads and reverse initial loads.

Indicates whether a channel is available for file synchronization.

Indicates if the old data will be included for routing. Routing can then use this data for processing. Defaults to true.

Indicates if the current data will be included for routing. Routing can then use this data for processing. Defaults to true.

Indicates if the primary key data will be include for routing. For example maybe a store ID is needed to apply logic on before sending to the appropriate target nodes. Defaults to true.

Indicates whether the channel contains big lobs. 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 large a 'big' lob is varies from database to database.

Unique identifier for a trigger.

Optional name for the catalog the configured table is in. If the name includes * then a wildcard match on the table name will be attempted. \ Wildcard names can include a list of names that are comma separated. The ! symbol may be used to indicate a NOT match condition.

Optional name for the schema a configured table is in. If the name includes * then a wildcard match on the table name will be attempted. Wildcard names can include a list of names that are comma separated. The ! symbol may be used to indicate a NOT match condition.

The name of the source table that will have a trigger installed to watch for data changes. See Trigger Wildcards for using wildcards to specify multiple source tables.

The channel_id of the channel that data changes will flow through.

Determines if changes will be captured for inserts.

Determines if changes will be captured for updates.

Determines if changes will be captured for deletes.

The channel_id of the channel that will be used for initial loads.

Specify a condition for the insert trigger firing using an expression specific to the database. On most platforms, it is added to an "IF" statement in the trigger text. On SQL-Server it is added to the "WHERE" clause of a query for inserted/deleted logical tables. See Sync Condition Example.

Specify a condition for the update trigger firing using an expression specific to the database. On most platforms, it is added to an "IF" statement in the trigger text. On SQL-Server it is added to the "WHERE" clause of a query for inserted/deleted logical tables. See Sync Condition Example.

Specify a condition for the delete trigger firing using an expression specific to the database. On most platforms, it is added to an "IF" statement in the trigger text. On SQL-Server it is added to the "WHERE" clause of a query for inserted/deleted logical tables. See Sync Condition Example.

Sync Conditions can access both old values and new values of a field/column using "old_" and "new_" respectively. For example, if your column is id and your condition checks the value coming in to be 'test', your condition will be:

 new_id = 'test'

Specify insert trigger text (SQL) to execute after the SymmetricDS trigger fires. This field is not applicable for H2, HSQLDB 1.x or Apache Derby.

Specify update trigger text (SQL) to execute after the SymmetricDS trigger fires. This field is not applicable for H2, HSQLDB 1.x or Apache Derby.

Specify delete trigger text (SQL) to execute after the SymmetricDS trigger fires. This field is not applicable for H2, HSQLDB 1.x or Apache Derby.

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.

Specifies whether to capture lob data as the trigger is firing or to stream lob columns from the source tables using callbacks during extraction. A value of 1 indicates to stream from the source via callback; a value of 0, lob data is captured by the trigger.

Provides a hint as to whether this trigger will capture big lobs data. If set to 1 every effort will be made during data capture in trigger and during data selection for initial load to use lob facilities to extract and store data in the database. On Oracle, this may need to be set to 1 to get around 4k concatenation errors during data capture and during initial load.

Indicates whether this trigger should capture and send the old data (previous state of the row before the change).

Captures only the primary key when the trigger fires which can reduce the overhead of the trigger on tables with lots of columns. The data will then be queried using the PK values captured when the batch is ready for extraction.

Specify a SQL select statement that returns a single row, single column result. It will be used in the generated database trigger to populate the EXTERNAL_DATA field on the data table. See

Specify a comma-delimited list of columns that should not be synchronized from this table.

Specify a comma-delimited list of columns only should be synchronized from this table.

Specify a comma-delimited list of columns that should be used as the key for synchronization operations. By default, if not specified, then the primary key of the table will be used.

An expression that will be used to capture the channel id in the trigger. This expression will only be used if the channel_id is set to 'dynamic.'

The table trigger to link.

The router to link.

SQL used as part of the WHERE clause on a SQL statement during the initial load process to extract data from the source node of the router. If blank all rows will be selected. If you want no rows to load during initial load you can set the expression to 1=0 or set Initial Load Order to a negative number.

SQL used as part of the WHERE clause on a SQL statement during the initial load process to delete data on the target node of the router.

initial.load.delete.first=true

Order sequence of this table when an initial load is sent to a node. If this value is the same for multiple tables, then SymmetricDS will attempt to order the tables according to FK constraints. If this value is set to a negative number, then the table will be excluded from an initial load.

Only applicable if the initial load extract job is enabled. The number of batches to split an initial load of a table across. If 0 then a select count(*) will be used to dynamically determine the number of batches based on the max_batch_size of the reload channel.

Each individual trigger-router combination can be disabled or enabled if needed. By default, a trigger router is enabled, but if you have a reason you wish to define a trigger router combination prior to it being active, you can uncheck the enabled box. This will cause the trigger-router mapping to be sent to all nodes, but the trigger-router mapping will not be considered active or enabled for the purposes of capturing data changes or routing.

SymmetricDS, by default, avoids circular data changes. When a trigger fires as a result of SymmetricDS itself (such as the case when sync on incoming batch is set), it records the originating source node of the data change in source_node_id. During routing, if routing results in sending the data back to the originating source node, the data is not routed by default. If instead you wish to route the data back to the originating node, you can check the ping back enabled column. This will cause the router to "ping" the data back to the originating node when it usually would not.

Unique identifier for a trigger.

The channel_id of the channel that data changes will flow through.

The channel_id of the channel that will be used for reloads.

The base directory on the source node that files will be synchronized from.

Whether to synchronize child directories.

Wildcard-enabled (*), comma-separated list of file to include in synchronization.

Wildcard-enabled (*), comma-separated list of file to exclude from synchronization.

Whether to capture and send files when they are created.

Whether to capture and send files when they are modified.

Whether to capture and send files when they are deleted.

Combined with sync_on_create, determines whether to capture and send files when a matching control file exists. The control file is a file of the same name with a '.ctl' extension appended to the end.

Determines whether to delete the file after it has synced successfully.

A beanshell script that is run at the target node right before the file copy to it’s destination directory.

A beanshell script that is run at the target node right after the file copy to it’s destination directory.

The file trigger to link.

The router to link.

The base directory on the target node that files will be synchronized to.

The strategy to employ when a file has been modified at both the client and the server.

Indicates whether this file trigger should be initial loaded.

Indicates whether this file trigger router is enabled or not.

Unique identifier for a specific conflict detection setting.

References a node group link.

Indicates the strategy to use for detecting conflicts during a dml action.

The choice of how to resolve a detected conflict is configured via the resolve type. Depending on the setting, two additional boolean settings may also be configured, namely "resolve row only" and "resolve changes only".

For each configured conflict, you also have the ability to control if and how much "resolved" data is sent back to the node who’s data change is in conflict. This "ping back" behavior is specified by the following options.

An expression that provides additional information about the detection mechanism. If the detection mechanism is use_timestamp or use_version then this expression will be the name of the timestamp or version column.

Indicates that when applying changes during an update that only data that has changed should be applied. Otherwise, all the columns will be updated. This really only applies to updates.

When 'resolve row only' is set to true, the system will ignore only the rows in conflict. When 'resolve row only' is set to false, the system will ignore the entire batch. This applies to a resolve type of 'ignore'.

Optional channel that this setting will be applied to.

Optional database catalog that the target table belongs to. Only use this if the target table is not in the default catalog.

Optional database schema that the target table belongs to. Only use this if the target table is not in the default schema.

Optional database table that this setting will apply to. If left blank, the setting will be for any table in the channel (if set) and in the specified node group link.