Advanced Settings

Most service properties are configured using the Windows applications SQDR Configuration or Data Replicator Manager.  However, viewing and modifying the following optional settings are available only through the SQDR Service Properties application; we recommend using this tool with the assistance of Stelo Support.

The following parameters are described below:

notifyUserID & notifyPassword

Set these values to supply credentials to SMTP servers that require authentication.

notifyMailEncryption

Set this property to specify the type of encryption to use when sending email.

0 - no encryption (default)
1 - TLS & SSL
2 - TLS only

Use 2 (TLS only) when connecting to Office365.  For more information about using Office365, see Office365_Hints.

addUnsupportedDrivers

true/false; default is false

The default behavior of the Data Replicator Manager when creating sources or destinations is to limit the display of available ODBC data sources to those supported by SQDR. Set this value to true to display all data sources.

archiveIgnoreDeletes

true/false; default is false

This setting can be used to create Archived Subscriptions that ignore deletes.  

useCluster

true/false; default is false

This performance-related setting controls whether indexes on SQL Server destinations are defined as clustered or non-clustered; setting this value will add the CLUSTERED parameter to the CREATE INDEX statement.

The default value is false, as many typical environments will not benefit from a clustered index. For example, if no other indexes are defined and RRN is being used with incremental replication, inserts and updates may be faster if the index is not clustered.

subCacheLimit

default 250
set to -1 to turn off subscription caching

SQDR will cache subscription information in memory to improve performance. It updates the information about the subscription when the SQDR service is stopped and restarted. It will also obtain the latest subscription information if the subscription properties have been changed.

Set the subCacheLimit entry to -1 if you prefer that SQDR always retrieves new subscription information.

Set its value to a number larger than the default of 250 when you are working with large numbers of subscriptions.

useOrderBy
true/false; default is true

Set this entry to false to disable the use of the ORDER BY clause for baselines and snapshots. This may improve the performance of baselines and snapshots (reduced host processing) but it disables the use of checkpoint recovery.  This setting is relevant for Db2 for i and Oracle sources. This is a global setting and will impact all new or modified subscription.  To apply this setting to an existing subscription:

  1. Open the subscription (right-click and select properties)

  2. Click OK to save the subscription - you will get a warning dialog "Destination object already exists etc.."; click OK

ROWID/RRN settings

The following entries allow you to customize the default definitions of derived columns based on ROWID (Oracle source) and RRN (Relative Record Number - Db2 for i source). You can also use Modify Columns on the Define Destination Columns panel of the subscription wizard when creating a single subscription, but using these entries automates the process and allow you to define multiple subscriptions at one time.

rowidName
default value ROWID

rrnName
default value RRN

rrnPrecision
default value 19 (Db2 for i) or 18 (Oracle)

rrnType
default BIGINT (Db2 for i) or CHAR (Oracle)

Example:

When replicating a table without a primary key from an Oracle source, SQDR will use the ROWID in place of the primary key.  The default name of the derived column in the destination table will be ROWID; however, if the destination is also Oracle, the CREATE TABLE statement will fail with the following error because ROWID is a reserved keyword in Oracle:

SQLSTATE 42S22, native error 904
[Oracle][ODBC][Ora]ORA-00904: "ROWID": invalid identifier

The solution is to change rowidName to another value (e.g. RRN); future subscriptions will create a derived column named RRN rather than ROWID.

forceNonNullPK
Default 0 (never)

 Some Destination database systems (e.g. Microsoft SQL Server and MySQL)  do not support nullable columns as primary key columns.  When creating an I/R subscription which requests "unique index" creation, the creation of the primary key will fail, and instead will create a unique index based on the constrained columns will be created.  This options render the nullablility attribute value of the key columns to be non-Nullable, thus allowing the creation of the primary key to succeed.

Possible values are:

0 - never (default)
1- Always
2- Dependent on destination DBMS - applies to Microsoft SQL Server and MySQL destinations only

Using this option may permit the creation of primary keys automatically, which is important for SQL Server change data replication. However, there is a risk that the baseline of such a subscription may fail (if the columns in question contain NULL values), or a subsequent incremental Apply may fail for a similar reason.

Note that it is necessary to recreate or rebuild the subscription after changing this setting.

assumeStableKey
true/false; default false

Setting this option to true will instruct SQDR to create the primary key immediately after the baseline completes, before notifying Capture with COPYDONE.  This should attempt to create the primary key if no uniqueness problems exist.

suppressWideTypes
true/false; default false

Setting this option instructs the Subscription Wizard to suppress the use of Wide Character types (SQL_WCHAR; SQL_WVARCHAR & SQL_WLONGVARCHAR) for the destination. A typical example would be replication to a legacy Db2 for i destination, in which the use of Unicode types is not desired.

suppressAutoUnique
true/false; default true

When SQDR is replicating between similar database systems (e.g. SQL Server to SQL Server), the column panel of the Subscription Wizard will suggest a like-to-like mapping for most datatypes, except for AutoUnique types i.e. automatically generated types such as 'int identity' and 'timestamp' in SQL Server, since the database system expects to generate the data for these columns. Setting this value to false will cause SQDR to revert to its pre-4.90 behavior.

subMutexTimeout
default 30000 (30 seconds)

wait time for new subscription and replicator locks

Settings for "Group" defaults:

defaultUseSelectDML default 1

defaultApplyRetryTimer default 360000 (6 minutes)

defaultIrRowLimitDefault default 20000

defaultGroupHaltOnError default 2 (Ignore Errors & Warnings)

useXMLEncoding
1 - XML
0 - JSON (default)

By default, Change Data Processing output is in JSON format. Setting useXMLEncoding=1 will direct SQDR to send output in XML formatting.  The setting is global, affecting all incremental groups. To change the encoding for particular group, select JSON or XML options from the Encoding dropdown in the Change Data Logging section of the Incremental Group Advanced Tab.

specify "local.encoding=XML" or "local.encoding=JSON" in a group comment to override the default behavior for a particular group.

incrementalDebug
true/false/-1; default false

Set this value to true to instruct SQDR to treat constraint violation as a warning event, instead of a hard error on the subscriber group).

A value of -1 instructs SQDR to pause an incremental group and log an event message if it detects a condition that would increase the flagged count on an incremental subscription (this is normally a warning condition).

setOracleSemantics
0/1/2; default 0

This value instructs SQDR on how to decorate the declaration of CHAR and VARCHAR fields in the CREATE TABLE statement (whether to use CHAR or BYTE semantics, or neither) when using Oracle as a destination.

miniDumpType
default 0

If SQDR encounters a problem, it may produce a dump file in C:\Users\Public\Documents\StarQuest\SQDR\CrashDumps, named SQDRSVC-<PID>-<TID>-YYYYMMDD-HHMMSS.dmp, where <PID> is the Process ID and <TID> is the Thread ID.

This value controls the type of information that will be written to the dump file. The default is 0 (MiniDumpNormal = Include just the information necessary to capture stack traces for all existing threads in a process). Refer to the MSDN documentation for MINIDUMP_TYPE enumeration for the available values.

disableIndices
true/false; default false

If a subscription is defined as Use Existing, and the destination supports the functionality, setting this value to true will cause the existing non-unique indexes to be disabled prior to the baseline and will automatically re-enable the indexes after the baseline completes.

This feature is available only for SQL Server and Oracle destinations and may improve baseline performance.

defaultAllowCheckpointRestart
true/false; default true

Typically checkpoint restart has been used only for IR subscriptions. Setting this property to true allows SQDR to checkpoint snapshot subscriptions for purposes of restart as well. This is useful when an incremental subscription is using a "manual" type of snapshot in conjunction with a set of snapshot subscriptions designed to partition the source select. Whether or not a specific subscription is restarted from the checkpoint is subject to other considerations (i.e. such as a useful "ordering" relationship).

Apply Timers

applyMaxExceptionRetryInterval (long) - default 60 (seconds).  

This value determines the amount of time Apply will wait before retrying an operation that resulted in an ODBC exception.

applyMaxStartupRetryInterval (long) -default 60 (seconds).  

This value is used by apply when initializing the Apply manager at startup.

applyMaxAutoSnapshotInterval (long)- default 360 (seconds)

This value determines how often Apply checks to run any snapshots.

tolerateSourceCommitError
true/false; default false

On some source systems, a long-running baseline may encounter an error related to source commit. SQDR will treat Set this property to true to instruct SQDR to ignore this error. For example, the Oracle error ORA01555 may be raised for long running queries; this error may be ignored, since incremental replications will perform touchup.

MSSQL_IndexRebuildOption
string

This property instructs SQDR to append the specified text to the command for rebuilding non-unique Indexes on SQL Server. For example, specifying COMPRESSION(PAGE) will reduce the size of the resulting indexes.

maxMySQLParms
(long) - default 2000

This value defines the maximum number of bound parameter markers that may be used when using the Bulk Loader with a MySQL/MariaDB/Aurora or Redshift destination. This value affects the number of rows per insert statement.  For example, with the default setting of 2000, if a subscription has 20 columns, then 200 rows will be inserted per statement execution.  If a subscription has 200 columns then only 10 rows will be inserted per statement execution.

minNumBufferAllocation
(long) - default 2

Increasing this parameter allows SQDR to use more buffers during data transfer, taking advantage of the increased memory space of 64-bit processes, and may improve overall performance of multi-thread baselines.

deepSubValidation
true/false; default false

SQDR 5.20 introduced quick validation of new subscriptions. Setting this property to true instructs SQDR to perform the more extensive validation that was performed in SQDR prior to 5.20.

includeBeforeImage
true/false; default true
Setting this property to false will exclude "B" images from archive subscriptions.  Note that before images are only available on some DBMS systems, such as Oracle. For example, no before image is captured for a subscription from an IBM i source using RRN.

padVarchar
true/false; default false

Setting this property to true enables padding of SQL_VARCHAR columns on the destination for incremental changes. This setting is independent of the "Strip trailing blanks (RTRIM) for VARCHAR" setting displayed in Data Replicator Manager on the Replication panel of Service Properties (property rtrimVarchar), which controls snapshot (baseline) behavior.

Note that many DBMS systems have a setting (e.g. SQL Server SET ANSI_PADDING ON/OFF) that can also be used to achieve desired results.


Listener Settings

Note that you must restart the SQDR service after modifying the listener settings.

Be very cautious changing these settings, as an incorrect value can disrupt access to the SQDR service.

grpcADDRPORT
default: 0.0.0.0:7737
Listener port for persistent connections. Typically requires authentication.

grpcTransADDRPORT
default: 127.0.0.1:7738
Listener port transient connections.  Typically used by PowerShell and SQDR Service Properties, and does not require authentication.

You can specify multiple listeners separated with semicolons.  e.g. set grpcADDRPORT to 10.0.0.1:7737; 10.0.0.2:7739 listen on port 7737 on the first interface and port 7739 on the second interface.

Set this value to 0.0.0.0:7738 (and adjust the firewall if necessary) if to allow remote connections from PowerShell and SQDR Service Properties.

grpcKEEPALIVE_TIME_MS
default: 10000 (ms)

grpcKEEPALIVE_TIMEOUT_MS
default: 10000 (ms)

grpcHTTP2_BDP_PROBE
default: 1

grpcKEEPALIVE_PERMIT_WITHOUT_CALLS
default: 1

grpcHTTP2_MIN_RECV_PING_INTERVAL_WITHOUT_DATA_MS
default 5000 (ms)

grpcHTTP2_MIN_SENT_PING_INTERVAL_WITHOUT_DATA_MS
default 10000 (ms)

gRPC_maxSendLength
default 8MB
Send message size

gRPC_maxRecvLength
default 4MB
Receive message size

applyRetainMergeContent

default False
If set to True, SQDR Apply will retain the last merge work activity; i.e. do not truncate the merge file.

RBAC (Role Based Access Control & logging) settings

These parameters can be viewed and modified on the RBAC panel of SDQR Configuration. Initial setting is false. You may need to restart the SQDR service after modifying these values.

rbacEnabled - true/false

rbacLoggingEnabled - true/false

rbacLogDetails - true/false

rbacKeySeed - default is *DEFAULT

rbacRetentionPolicy - default 0 (days) - i.e. retain indefinitely

pruneFrequency
default 15 (minutes)
Frequency of pruning of statistics and RBAC logs (related to rbacRetentionPolicy)

ApplyCompressTier4
true = Run MERGE compression on Tier 4 (Destination)
false (default) = Run MERGE compression on Tier 2 (Staging agent)
Running MERGE compression on Tier 2 may result in extra CPU load on that system.

useMaxVARCHAR
(SnowFlake, Postgresql & Spark destinations only)
default true
Use maximum varchar allocation semantics when allowed.  This avoids an issue where UTF-8 storage of VARCHAR may exceed the character precision of the column.

useMultiRowApply
default true
Setting this value to false specifies bypass of the use of MultiRow Apply.

forceScheduleRecalculation
default false
Setting this value to true causes the scheduler to recalculate all enabled schedules upon startup of the SQDR service. This is useful if the system clock has changed.