SymmetricDS Pro 3.10 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). Parameter values can be substituted using $(name) syntax. See Variables.

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). Parameter values can be substituted using $(name) syntax. See Variables.

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.

Determines a queue that the channel will sync in. Channels with the same queue name are processed synchronously (one at a time) and channels on different queues are processed asynchronously (in parallel).

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.

For Oracle, Tibero, Firebird, and Interbase, this setting can be enabled when change data capture exceeds the character limit. Oracle and Tibero have a character limit of 4000, while Firebird and Interbase have a character limit of 20000 for changes and 1000 for primary key values. Change data capture is first attempted to extract as character data for better performance, then it will automatically fall back to extract as a large object (LOB). Enable this setting when most changes captured on the channel need extracted as LOB or when the extraction is receiving a truncation error.

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. Parameter values can be substituted using $(name) syntax. See Variables.

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. Parameter values can be substituted using $(name) syntax. See Variables.

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. Parameter values can be substituted using $(name) syntax. See Variables.

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 changes. Changes are never sent back to the source node, but enabling this setting makes it possible to create a never-ending loop through intermediary nodes, such as A to B to C to A.

For Oracle, Tibero, and DB2, row data that exceeds the character limit needs converted into a large object (LOB) for capture. Oracle and Tibero have a character limit of 4000, while DB2 has a character limit of 32767. The trigger will try to automatically capture the row correctly, so only enable this setting if you are getting truncation errors during trigger creation or when changing data.

Captures an empty placeholder for large object (LOB) data types when a row is changed, then queries for the LOB value later when the batch is extracted. If normal capturing of LOBs is not working, enabling this setting may work instead. When very large LOB data is involved, this setting can reduce the overhead of making changes in the database, but it usually results in worse performance of synchronization since it queries each row during extraction.

Captures only the primary key values when the trigger fires, which can reduce overhead for tables with wide data or many columns. The data will be queried using the PK values when the batch is extracted. This results in worse performance of synchronization, but it can be used when triggers for all columns won’t install or when contention from triggers is too high.

Indicates whether this trigger should capture and send the old data, which is the previous state of the row before the change. Enable this option if you need to access old data in custom trigger text, routing expression, or transform expression. Otherwise, disable this option for better performance.

For SQL-Server and Sybase, enable this setting to capture changes to the primary key. The trigger needs to do some additional work to handle changes to the primary key, so this setting is normally disabled.

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 variable "$(schemaName)" can be used, which is replaced with the source schema of the table. See Variables.

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.

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.

For each configured conflict, you also have the ability to control if and how much "resolved" data is sent back to the node whose 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. The detect_expression is also used to exclude certain column names from being used. For example, to exclude column1 and column2, the expression is "excluded_column_names=column1,column2".

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.

Unique identifier of a specific transform.

The group link defining which direction the transform will process.

Where this transform will occur. The options include:

Indicates whether unspecified columns are passed thru or if all columns must be explicitly defined. The options include:

Name of the catalog of the configured source table. This should only be set if Use Source Catalog/Schema or Target Catalog are set on the Router.

Name of the schema for the configured source table. This should only be set if Use Source Catalog/Schema or Target Schema are set on the Router.

The name of the source table that will be transformed.

Optional name for the catalog a target target table is in. Only use this if the target table is not in the default catalog.

Optional name of the schema a target target table is in. Only use this if the target table is not in the default schema."

The name of the target table.

This option overrides the default behavior for an Insert operation. Instead of attempting the Insert first, SymmetricDS will always perform an Update first and then fall back to an Insert if that fails. Note that, by default, fall back logic always applies for Insert and Updates. Here, all you a specifying is whether to always do an Update first, which can have performance benefits under certain situations you may run into (see Operation Change).

An action to take upon delete of a row.

An action to take upon update of a row.

For a single source operation that is mapped to a transformation, there could be more than one target operation that takes place. You may control the order in which the target operations are applied through a configuration parameter defined for each source-target table combination. This might be important, for example, if the foreign key relationships on the target tables require you to execute the transformations in a particular order.