Connection Properties Dialog

This dialog reports and allows you to edit connection properties for source and target connections. Many of these properties are typically set initially in the Add Source Connection wizard or Add Target Connection wizard when setting up a replication.

NOTE: The Management Center window contains a View Properties pane. If you select a source or target connection in the Metadata Explorer, connection properties are displayed in the View Properties pane. However, to edit properties, you must open the Connection Properties dialog using the Show Properties icon in the View Properties toolbar.

Connection Name

The name of the connection that you provided when creating the connection.

Connection

This field displays the current data provider. Click on the ellipsis icon , to open a dialog showing the connection string details as previously defined in the Source Connection wizard or Target Connection wizard. The database, data provider and connection string parameters can be changed in this dialog.

Default Fetch Size

Default value is 1000000. The default read block size for mirroring and refresh replications with connections using Oracle .NET Data Provider. This value can be overridden in the the Replication Properties for each replication.

Relative Time Difference

Shows the time difference between the Syniti DR Server Agent system and the database server. A negative value means the Server Agent system time is behind the database server time.

Source and Target Connection Properties

Dynamic Properties

Max Number of Concurrent Connections
Restricts the number of connections that Syniti DR can make to the database server. This is a safeguard in situations where you have many replications using the same database server, but do not want to overwhelm the database server with concurrent connections from Syniti DR, possibly affecting connections from other applications using the database server.
Command Timeout
Indicates the amount of time before a command created from this connection times out while waiting for a response from the database server. A default value of -1 indicates that no time has been set and the default timeout determined by the .NET data provider is used. To set a different timeout value, enter a value in seconds.
Disconnected Mode
Indicates if the connection has to be considered in disconnected mode when used within the wizards. If this property value is set to true, every time it is necessary to list connection objects (catalogs, schemas, tables), only objects already belonging to the metadata will be shown.
LOBs Cache Size (Kb)
Defines the minimum size of a LOB field, retrieved using this connection as reader, in order to buffer its value on a temporary file instead of loading it in memory in the record queue . Set this property to 0 to diable LOB caching functionality.
CREATE TABLE Prefix (Target Connections Only)
Allows you to define a CREATE TABLE statement to be used instead of the default. Set the value as a string, and include "CREATE TABLE" . If no value is defined, the default is used.

Example: "ALTER session SET nls_length_semantics=CHAR; CREATE TABLE"
CREATE TABLE Postfix (Target Connections Only)
Allows you to define a statement to be executed after creating the table. Set the value as a string.
CREATE INDEX Postfix (Target Connections Only)
Allows you to define a statement to be executed after creating an index. Set the value as a string.
CREATE TABLE Custom Rule (Target Connections Only)
Allows you to select a rule from a previously created list of rules to use in creating tables. The default is Automatic, meaning that no custom rule will be used.

CREATE TABLE Keep Identity (Target Connections Only)
Works in conjunction with the Identity field in the Create Target Table wizard to allow creation of Identity columns on the target when set to True for the following target databases: Microsoft SQL Server, Oracle, IBM Db2 for i, MySQL, SAP Hana, MariaDB, Informix, PostgreSQL, SAP ASE, SAP SQL Anywhere. When set to False, the Identity marker will not be added in the target table.
Staging Schema (Oracle Target Connections Only)
If using bulk mirroring, staging tables are required on the target database to hold data temporarily. By default, these tables are created in the target connection schema. However, you can set the schema to use in this field. CREATE TABLE permissions are needed if bulk mirroring is enabled.
Get User Name from Session (Oracle Target Connections Only)
Retrieves the user name from the session if Syniti DR detects the user as UNKNOWN from the log miner. Note: when set to True, this property executes an additional query which can impact performance.
Convert CCSID 65535 (IBM Db2 for i/iSeries/AS400 only)
False by default. When True, automatically converts binary field values to varchar type so that they can be processed as strings. See also the Database Specific Settings dialog.
CCSID for Conversion (IBM Db2 for i/iSeries/AS400 only)
The language code to use in converting binary fields to varchar fields.
Service Name (Oracle only)
A read-only property that provides the service name of the system where Oracle is installed.
Max Size Mirroring Block (all databases that support transactional replications via triggers)
IBM Db2 for i/iSeries/AS400
Set to 10000 by default. This field is useful if you expect a huge number of transactions between mirrorings. Syniti DR reads the journal and stores records that need to be replicated in a temporary file. If the file contains a large number of records or a smaller number of very large records, it can impact performance. In this case, you can set a limit on the number of records to handle at one time. For example, if you set the value of the Block Size field to 250, only 250 records will be replicated at a time.
SQL Server
Set to 10000 by default. This field is useful if you expect a huge number of transactions between mirrorings. Syniti DR reads the log and stores records that need to be replicated in a temporary file. If the file contains a large number of records or a smaller number of very large records, it can impact performance. In this case, you can set a limit on the number of records to handle at one time. For example, if you set the value of the Block Size field to 250, only 250 records will be replicated at a time.
Oracle
Set to 0 (disabled) by default. This field is useful if you expect a huge number of transactions between mirrorings. Syniti DR reads the log and stores records that need to be replicated in a temporary file. If the file contains a large number of records or a smaller number of very large records, it can impact performance. In this case, you can set a limit on the number of records to handle at one time. For example, if you set the value of the Block Size field to 250, only 250 records will be replicated at a time.
Connections for Trigger-based Replications
For trigger-based replications, this property instructs the Syniti DR engine to break the query that reads the transactions into multiple queries, each one returning not more than the specified number of records. For example, if you have 1000 transactions to retrieve and the property is set to 250, Syniti DR runs 4 queries to retrieve all the transactions. This approach reduces the load on the database server and reduces the locks on the log tables which are also used by the triggers. The default value for this property is 1000.
Max Number of Log Records in Queue
Default value is 200. For transactional replications, defines the maximum number of log records loaded in memory waiting to be committed. Once this number is reached, new log records will be cached to file.
Enable Transactional Objects
For transactional replications, this property allows you to enable or disable the creation and validation of any transactional objects required by Syniti DR in the source database. For example, triggers and log tables are created for trigger-based replications, and articles and publications are created for Microsoft SQL Server log-based replications. By default, the property is set to true and Syniti DR will attempt to create objects required for transactional replications in the database as needed. In some cases, the Syniti DR user does not have enough permissions to make changes in the DBMS, so the property can be set to false to allow external creation of the needed objects. When the property is set to false, Syniti DR creates the replication definition without altering properties in the database. This property works in conjunction with the Validate Replications feature where it is possible to check if the replication has been set up correctly.
Temporary Files Library
The default value is DBMOTOLIB. You can modify this value if you prefer to keep temporary files associated with Syniti DR in a different library. Temporary files are automatically created and deleted by Syniti DR. You do not need to maintain these files.
Use Primary Key Extended (IBM Db2 for i/iSeries/AS400 only)
When set to True, retrieves keys for tables that support this feature such as DDS tables.
Verifier Sort Sequence Table (IBM Db2 for i/iSeries/AS400 only)
Designate a table that contains ordering rules for the data in the result set to be used by the Verifier to sort character values.
Default Commit Mode: Default is AutoCommit where each statement is replicated as a standalone transaction. This means that only statements that fail will not be replicated. The CommitmentControl option applies all the statements associated with a transaction as a whole to the target. CommitmentControl is supported on Oracle (Log Reader and Log Server Agent), Microsoft SQL Server, IBM Db2 for i, IBM Db2 LUW, IBM Informix.
Filter By Member Name (IBM Db2 for i/iSeries/AS400 only)
For use when replicating with multi-member tables. If set to True, when reading the Journal a filter will be used with the default member of the table. This will prevent "journal entries not found" errors caused by operations executed to non default members of the table. Remember to update the table schema when the default member is changed.

Refresh Options (Target Connection Properties Only)

Default Insert Mode
Allows you to set a default insert mode for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. The options are:
- SingleInsert--inserts record by record using SQL INSERT statements
- BulkInsert--inserts blocks of records. Block size is determined by the Block Size option. Choose this option for increased performance if using one of the data access providers which currently support bulk inserts:
  Syniti Ritmo/i (IBM Db2 for i) .NET Provider
  SAP HANA ODBC driver
  SAP HANA .NET Provider
  SAP Sybase ASE .NET Provider
  SAP Sybase IQ .NET Data Provider
  Microsoft SQL Server .NET Provider (using SQL Server's SqlBulkCopy)
  MySQL .NET Data Provider (MySQL AllowLoadLocalInfile property must be set to True in the connection string for bulk mirroring with MySQL Connector 8.0 and above)
  PostgreSQL .NET Data Provider
  Oracle .NET provider.
  For SQL Server, note that BulkInsert is the default option.
  Record details are not reported in the log when BulkInsert is set.
- SimulatedBulk--For databases that do not support bulk insertion, Syniti DR simulates the effect by grouping records into blocks before performing an insert. This is a way to optimize performance.

Default Bulk Type
Allows you to set a default bulk type for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. Depending on the database type and provider type, some of the following options may not be available.
- ArrayBinding--inserts multiple rows at a time using an array of parameters
- Native--uses the native functionality of the provider, if available
- FTP--bulk insert implemented through FTP
Default Block Size
Allows you to set a default block size for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. Set the value for the number of rows to insert in a single operation using the Bulk option above.

Default Isolation Level
Allows you to set a default isolation level for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. This option allows you to choose a specific isolation level on the Refresh operation. Choose from the following options available through your .NET Framework data provider:

Unspecified	No level is determined.
Chaos	The pending changes from more highly isolated transactions cannot be overwritten.
ReadUncommitted	A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
ReadCommitted	Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
RepeatableRead	Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
Serializable	A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

Use DELETE instead of TRUNCATE
When running a refresh, Syniti Data Replication by default initially truncates the target table before replicating new values from the source. Set this field to True to change the default behavior to delete target table values (and retain details of deleted data in the log) before replicating fresh data from the source.

Mirroring Options (Target Connection Properties Only)

Default Mirroring Insert Mode
Allows you to set a default insert mode for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. The options are
- SingleInsert--inserts record by record using SQL INSERT statements
- BulkInsert--inserts blocks of records. Block size is determined by the Block Size option. Choose this option for increased performance if available
Default Mirroring Block Size
Allows you to set a default block size for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. Set the value for the number of rows to insert in a single operation when using the BulkInsert option above.

Default Commit Isolation Level
Allows you to set a default isolation level when the Commit Mode is set to CommitmentControl for all replications using this target connection. The default setting can be overridden using the Replication Properties for a specific replication. Choose from the following options.

Unspecified	No level is determined.
Chaos	The pending changes from more highly isolated transactions cannot be overwritten.
ReadUncommitted	A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
ReadCommitted	Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
RepeatableRead	Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
Serializable	A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
Snapshot	No locks on underlying data in a snapshot transaction, so other transactions can execute.

Allows you to set a default mapping rule for all replications using this target connection. The default setting can be overridden when setting the mapping for a specific replication.

Transactional Support Properties

These properties are relevant when performing transactional replications (mirroring or synchronization). Properties differ depending on the type of database used in the connection. To modify properties in this section, click the value for the Transaction Log Type property and then click to open the Setup Info dialog.

Transaction Log Type
Always set to either the database name, where the database log is used to detect new transactions, or Triggers, where triggers are used to identify transactions in the database. Click the value for the Transaction Log Type property and then click to open the Setup Info dialog.

SQL Server Source and Target Connections Only

Server Name
Always set to the name of the server that you specified in the Source/Target connection. Select this field and click to create the distributor required for mirroring and synchronization replications, or modify the user ID and password.
Database Name
Set to <None> if no distributor has been created. A distributor is required for mirroring and synchronization replications that use the transaction log.
Set to DBRS_DISTRIBUTION if the distributor has been created. To create the distributor, click in the Server Name field and then click to open the Distributor Settings dialog.
Use Trusted Connection
When False, you need to supply a user ID and password for SQL Server. When False, the connection uses your Windows user ID and password. Set this field to True using the Distributor Settings dialog available by clicking in the Server Name field.
User ID
Blank when no distributor has been created. Set this field using the Distributor Settings dialog available by clicking in the Server Name field. The user ID should have system administrator privileges to create the distributor and access the transaction log.
Password
Blank when no distributor has been created. Set this field using the Distributor Settings dialog available by clicking in the Server Name field.
Log Options
This property allows the user to add options to the query that Syniti DR uses to open the SQL Server Log Reader, and thereby optimize query execution. Use the syntax specified for the SQL Server OPTION clause. For instance, for the following query:
SELECT …
FROM …
WHERE …
OPTION (HASH GROUP, FAST 10);
put the value “HASH GROUP, FAST 10” in the Log Options property.

IBM Db2 for i/iSeries/AS400 Source and Target Connections Only

Library Name
By default, this is set to DBMOTOLIB when this has been installed. Click in the field and then click to open the Install i5/AS400 Library dialog and install the library. This library is required when performing mirroring and synchronization replications that access the journal.
Version
Read-only property. The version of the DBMOTOLIB library installed on the IBM Db2 for i (iSeries/AS400) system. This value is generated by Syniti DR.
Apply Journal Order By
(Only for use in special circumstances.) If enabled, when reading the journal, an ORDER BY JOSEQN (Journal entry sequence number) clause will be added to the SELECT statement to enforce the sorting, whenever it is not performed by default.

Oracle Source and Target Connections Only

Use Online Dictionary
Set to True if you are planning to run mirroring or synchronization replications for Oracle versions 9i or higher. Set to False for Oracle versions 8.1.7 up to 9i. You must also specify a dictionary file name if you set this value to false.
Dictionary File
If you are using mirroring or synchronization and set Use Online Dictionary to False, type the path and file name for an Oracle dictionary file. The Oracle dictionary file should be defined in your Oracle environment. See Setup for Different Oracle Versions for more information.
Max Number of Log Files in Mirroring Block
This property is used only when Use CONTINUOUS_MINE Option is disabled. It sets the number of log files that can be loaded into memory per transaction-log reading session (default is 5). In cases where there is a long list of log files to load, processing them all at the same time would significantly increase system memory consumption. Controlling the number of files in memory reduces the impact on system performance.
Default Fetch Size
The default read block size for Oracle sources in both refresh and mirroring. The default value is 1000000. However, for replications using the Log Reader, it can be overridden in the Fetch Size field of the Replication Properties dialog. For replications using the Log Server Agent, the value should typically be much higher for best performance.

SAP HANA (ODBC connection) and SAP Sybase IQ Target Connection Only

In the Dynamic Properties section, you need to set FTP/SFTP properties to transfer data from the source database to the SAP HANA server. With FTP/SFTP file transfer information, Syniti DR can use BulkInsert mode for replication speed and efficiency.

FTP Server, FTP Port, FTP User, FTP Password
The FTP/SFTP connection details for the server where SAP HANA is running.
Import Path
The complete path for the file location on the target machine where SAP HANA is installed.

Netezza/PureData Target Connection Only

Max Commit Interval
Indicates the maximum waiting time (in seconds) to reach the block size before sending records processed so far to the target, even if records to fill the block size have not been collected yet. Works in conjunctions with the Default Block Size property. The default value is 0, indicating that there is no maximum limit on the time to wait before sending records to the target--only the block size value will be used.
When processing a mirroring, the records changed are sent to the target in blocks as indicated in the Default Block Size field. If the time to process a block reaches the specified value, the records that have been processed so far are sent to the target even if the records count is less than the specified block size.

Trigger-based Log Properties (Source and Target Connections for Db2 UDB, Db2 z/OS or Gupta SQL Base Only)

Use Transactional Replication

Check this option if you are planning to define a mirroring or synchronization replication.

Master Table
Either specify an existing qualified table name, or click Change to create a new table to hold general information about replication transactions including user name, timestamp, table name for each transaction.
There are two tables associated with each replication: a Master table, common to all replications using that connection, and a Log table for each replication source table. The Master table keeps track of all the transactions affecting the source tables and it records general transactional information.
Master and Log tables are created in the schema specified when you set the Master table name. You can choose a Master table name, or use the default _DBM__MASTERLOG. Log tables are automatically generated by Syniti DR and the names are _DBM__LOG_#, where # is a number. The selected schema for the Master and Log tables must not contain other non-Syniti DR tables with names _DBM__LOG_# . It is recommended that you create a new schema to use specifically for the Syniti DR Master and Log tables.
Tablespace
It is recommended that you assign a tablespace for the Master table and Log Tables so that it is easier to control log table sizes. Your system administrator should be able to provide you with the appropriate value for this field.
Retention Time
The amount of time in hours that a transaction is kept in the log tables. The default value is 72 hours. When the amount of time a transaction resides in the log exceeds the retention time, the transaction is permanently removed from the log tables. Tuning the retention time provides control over the size of the log tables.
Delete Block Size
Based on the retention time, Syniti DR deletes items from the log. This field specifies the maximum number of records to delete from the Syniti DR log tables with a single SQL statement. The default value is 10,000 records. You do not typically need to edit this value.

Opening the Connection Properties Dialog

In the Management Center Metadata Explorer, expand the tree to show a source or target connection. Select the connection. From the right mouse button menu, choose Connection Properties.