Configuration Reference

The following properties are user-configurable:

Property Name

Default Value

Range

Description

Logging Parameters

 

 

 

logLevel

WARNING

OFF, SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST

Determines the amount and type of information that is recorded to log files.

maxLogFileSize

4096000

 

Specifies the maximum number of bytes of changes that the log file can contain.

maxNumLogFiles

10

 

Specifies the maximum number of log files that can be created to track changes.

Email Notification Parameters

 

 

 

notificationLevel

WARNING

OFF, CONFIG, INFO, WARNING, SEVERE.

Specifies the level of logging messages that are sent via email to the specified user account.

starquestNotification

 

SEVERE

 

Automatically send problem reports via email to StarQuest Support. To disable it, set starquestNotification=OFF in in the configuration or uncheck the checkbox when adding a database staging agent.

SmtpServer

 

mail.mydomain.com

To enable email notification. specify a server name or IP address of an SMTP server that can direct the email to the user account specified in the “to” parameter, and specify a notificationLevel value other than OFF.

from

 

server@domain.com

If the email notification feature is enabled (see smtpServer), the “from” parameter appears in the From field of the email. The “from” parameter can contain any string, such as the Capture Agent schema name and the computer on which it is located, to help identify the source of the email message.

to

 

user@domain.com

If the email notification feature is enabled (see smtpServer), the “to” parameter must contain a valid email user account in a format such as user@domain.com.

mailUser

 

 

Configure mailUser and mailPassword if the SMTP server requires authentication.  To discontinue using SMTP authentication, delete these properties from the configuration.

mailPassword

 

 

 

mailOptions

 

 

Additional options to pass to the SMTP server, represented as tuplets of javax.mail configuration properties. For instance, use mailOptions=mail.smtp.port\=8889 to specify an alternative port. Additional properties can be added with a semicolon separator.

Change Data Notification Parameters

 

 

 

broadcastAddress

none

 

Specifies the default IP address that Capture Agent will send notification to when there is change data available for a subscription that has notifications enabled but does not specify a different IP address to send them to.

minimumNotificationDelay

5000

 

Determines the minimal time interval  in milliseconds that must pass before the Capture Agent sends notification that change data is available for subscriptions that have been enabled to receive notifications. This delay is related to the time between subsequent UDP notifications, not the initial notification.

udpPort

2728

 

The port that Capture Agent uses to send notification that change data is available for subscriptions that have been specified to receive notifications.

Journal/Log Management on Source System

 

 

 

archivedLogPath

 

 

DB2 for LUW source only: If managedLogs is set to True, the archivedLogPath value specifies the directory in which archive log files are saved when they are no longer needed by SQDR Plus if the deleteLogsWithoutSave parameter is set to false.

autoJournal

true

true

false

DB2 for i source only: Enable this option to have the Capture Agent automatically add journaling for physical files that are not already being journaled. Files must be journaled if they are used by subscriptions that perform incremental replication, and an error is returned if there is no journal and the autoJournal option is disabled. See Autojournaling for details.

collectLogDetail

false

true

false

Instructs the Staging Agent to collection the journal information ("job name", "journal type", "journal sequence number" and "rrn").  This data is written by SQDR to the ir_keylog column "journal_entry_detail" if key logging is enabled.

deleteLogsWithoutSave

true

true

false

DB2 for LUW source only: If managedLogs is set to True the deleteLogsWithoutSave value determines whether archive log files are deleted when SQDR Plus no longer needs them (true), or the archive log files are saved to a directory specified by the archivedLogPath parameter (false).

filterAtSource

true

true

false

If false, all journal entries are forwarded to Tier 2. If true and the number of subscribed tables for a journal is less than or equal to 300, only "matched" names are forwarded to Tier 2.  Filtering at the source reduces the amount of traffic on the network, at the expense of increased processing on Tier 1.

managedLogs

 

true

false

DB2 for i source: Specifies whether SQDR Plus manages all system-managed journal receivers that are associated with subscriptions (true) or just SQDR journal receivers that are used for automatic journaling (false).

DB2 for LUW source: Specifies whether SQDR Plus manages the log files (true), or the database instance manages the log files (false).

receiverLibrary

SQDR

 

DB2 for i source only: This value designates the library for storing journal receivers if autojournal is enabled. The default is to use the sourceDbSchema (specified during creating of the staging agent).  If you prefer to use a different receiver library, create the library and specify its value here.

Space Monitor Parameters

 

 

 

smSQL

 

 

A SQL statement used to monitor staging activity; the result should be a single integer or bigint value that reflects usage. The default is a SQL statement that results in the number of pending transactions in the transactions table. See Monitoring Backlogs with the Storage Monitor

smInterval

30

 

Frequency of monitor in minutes.  0 means disabled. See Monitoring Backlogs with the Storage Monitor.

smThrottleLevel

15000000

 

If the result of the smSQL statement is above this value, generate a SEVERE error notification/log message and pause all log readers (replication workers). See Monitoring Backlogs with the Storage Monitor.

smWarningLevel

10000000

 

If the result of the smSQL statement is above this value, generate a SEVERE error notification/log message. See Monitoring Backlogs with the Storage Monitor.

HIGH AVAILABILITY

 

 

 

 

 

 

Refer to the technical document SQDR Plus High Availability for details.

failOverSchema

<sourceDbSchema>

SQDR

Source database schema of the active agent. This parameter should be configured on both the active and passive agents. If not set, the default is the sourceDbSchema of the agent being configured.

usingFailover

false

true

false

Enable Failover. This parameter should be configured on both the active and passive agents.

OTHER PARAMETERS

 

 

 

alwaysUseRowId

 

false

true

false

DB2 for i and Oracle source only: Use RRN (ROWID)-based replication only.

For DB2 for i, enable this option to subscribe to tables whose journaling is configured with Record Images=*AFTER. For best results, we recommend that tables be journaled for Record Images=*BOTH (the system writes both before and after images to the journal for changes to records in the table), but this option is available if an existing journaling configuration cannot be changed.

For Oracle, select this option to force the use of a specific key (ROWID) consistently.

autoGrant

true

true

false

DB2 for i and Oracle source only: Set this option to false to disable routine grants when adding subscriptions.

batch

true

true

false

DB2 for i and SQL Server source only: Use Block insert when updating staging tables. The SQDR Capture Agent uses block insert to improve staging performance. This feature results in significant performance improvements when many rows are updated/inserted/deleted in a single table or a small number of tables. However, it may result in poorer performance if a single row is touched in many hundreds of tables; in this case, you may choose to disable the feature by setting batch=false.

batchSize

100

 

DB2 for i and SQL Server source only: the number of rows in a single block insert

clientDeleteGrace

1 (day)

 

When SQDR Plus detects an inactive subscription, it sends a notification email (type SEVERE) after seven days of inactivity, then automatically deletes the subscription and subscriber after a grace period of one additional day expires, allowing purging of staged data. Setting this value extends the grace period. A value of 0 disables the automatic deletion of subscriptions and subscribers. Note: You can recover deleted subscriptions by using the Reset I/R Group function from the SQDR Data Replication Manager; a new baseline will be performed.

clientMonitoringInterval

1440 (minutes)

 

The default behavior of SQDR Plus is to check for inactive clients every twenty four hours (1440 minutes). You can change this interval by setting the clientMonitoringInterval property in the configuration; the value is specified in minutes.

compareChangeData

true for supported systems

false otherwise

true

false

DB2 for i, DB2 LUW, Oracle, MySQL, and SQL Server (Change Data Capture) source only: Instructs the Agent to compare before and after images of an update and then minimize staged data.
See related properties minimizeChangeData and supplementalLogging (Oracle).

clientODBCString

 

 

SQDR Plus uses a table SQDR.SQ_PROPERTIES on the source system to convey information to the SQDR client system, including how to connect to the SQDR Plus control database on the tier 2 system. Use this optional parameter to customize the connection string.

Example: This connection string instructs the SQDR client to connect to the DB2 LUW control database on the tier 2 system on port 50001 rather than the default of 50000, and to use a package collection of STARSQL6:

DRIVER={StarSQL 32}; HostName=myTier2Sys;
Port=50001; Server=SQDRP0;
PkgColId=STARSQL6

congestedThreshold

5 (minutes)

minimum 2; must be larger than uncongestedThreshold

If the processing log records by SQDR Plus falls behind, then the state of worker changes to Congested and warnings are issued, which may result in log entries and email notifications.

filterJobName

 

 

DB2 for i source only: Specifies that the Capture Agent should bypass staging transactions associated with the specified Job Name(s). Multiple values may be specified with a comma-separated list.  

With the property in place, the Capture Agent examine each commit record; if the Source Job Name (&JOB) in the Journal Entry Detail in the commit matches any of the values of the "filterJobName", the commit is handled like a rollback, and any staged data is removed.

filterProgramName

 

 

DB2 for i source only: Specifies that the Capture Agent should bypass staging transactions associated with the specified Program Name(s). Multiple values may be specified with a comma-separated list.

With the property in place, the Capture Agent examine each commit record; if the Source Program Name in the Journal Entry Detail in the commit matches any of the values of the "filterProgramName", the commit is handled like a rollback, and any staged data is removed.

filterUserID

 

 

Specifies that the Capture Agent should bypass staging transactions associated with the specified userID. The property can have a value of a single userID.

With the property in place, the Capture Agent examine each commit record; if the user-id in the commit matches the "filterUserId", the commit is handled like a rollback, and any staged data is removed.

grantees

 

 

Grants the specified users access to the Capture Agent control tables. By default the Capture Agent grants SELECT privileges to PUBLIC. Specify other users, separated by a comma, if you want to limit who can view the Capture Agent control tables and see what subscriptions are active. Note that changing the grantees does not change grants for existing control tables.

jdbcProperties

 

 

Oracle source only:A string containing semicolon separated key=value pairs that is forwarded to the JDBC driver.

Example: to configure network compression (for WAN connections) in Oracle 12c, specify

oracle.net.networkCompression=on;

oracle.net.networkCompressionThreshold=1024

maintenanceWindow

 

 

Specifies the time of day during which local maintenance (pruning) is allowed to run. If this value is not set, or an invalid value is provided, pruning may occur at any time, based on the pruneInterval parameter.

Exception: pruning may also occur if the Space Monitor has detected a storage condition  i.e. if the smWarningLevel value has been exceeded.

Times are specified using a 24 hour clock, and can be just hour HH, or hour and minutes HH:mm. Multiple windows can be specified using comma separators. Start/stop times are separated by a hyphen.

Example: maintenanceWindow=13:41-14,15-16,20-22:30,4:25-7:48

minimizeChangeData

false

true

false

DB2 for i, DB2 LUW, Oracle, and SQL Server (Change Data Capture) source only: This option is used in conjunction with compareChangeData. If this option is set to true, the agent will not record the entire "after image" data in the staging database. This permits the use of replay even if compareChangeData is true.

multiMember

false

true

false

DB2 for i source only: Support multimember files using aliases. See Multimember Support.

name

 

 

This optional property is used to change the name of an agent as displayed in the SQDR Control Center.  This name must be unique; do not use the name of another existing agent. After creating the property, saving the configuration, and restarting the agent, you may need to refresh the browser.

pollSleepTime

5000
For Oracle source: 1

 

Specifies the interval (in milliseconds) at which the Journal Reader should poll the journal for changes.

pruneInterval

30

 

Specifies the interval (in minutes) for examining the Capture Agent control tables to determine what change data can safely be deleted. See Configuring Pruning of Control Tables.

pruneRetention

0

 

The minimum time (specified in hours) that data should be retained in the staging database. Normally, staged data may be pruned as soon as it is applied to the destination database. However, set this parameter to a non-zero value to use the Replay function of SQDR; see Data Replication Manager help (drmgr.chm) for details of the Replay function.

Note that this is the minimum time that data is retained; it may be retained longer if an SQDR client is not actively retrieving updates.

requirePublication

false

true (Oracle agents created with SQDR Plus 4.90 & later)

true

false

Specifies whether all the database tables can be subscribed to for replication (false), or only tables that have been published can be subscribed to (true).
A value is true is recommended for Oracle agents, working in conjunction with the oracleWhereClause property.

retryPublishLock

false

true

false

Do not publish a table until an exclusive lock can be obtained. See Publishing.

requireUniqueIndex

false

true

false

Prevents the creation of subscriptions to tables that lack a unique index or primary key.

For DB2 for i and Oracle sources, this prevents the use of RRN (ROWID) and avoids new baselines for tables that are reorganized frequently.

For DB2 for LUW sources, this prevents creating subscriptions to such tables, since any update or delete operations will cause a new baseline; only insert operations can be performed on such tables without requiring a new baseline.

rollbackLookAhead

true

 

true

false

Setting this property to false disables the default look-ahead behavior. This may be desirable if there are there are many small (single row) rollbacks in the application usage pattern rather than the more typical pattern of large rollbacks.

sourceMaintenanceWindow

   

Specifies the time of day during which maintenance is performed on the source database system. SQDR Plus will not attempt to connect to the source to check for changes during this window.

Times are specified using a 24 hour clock, and can be just hour HH, or hour and minutes HH:mm. Multiple windows can be specified using comma separators. Start/stop times are separated by a hyphen.

Example: sourceMaintenanceWindow=13:41-14,15-16,20-22:30,4:25-7:48

supplementalLogging

ALL

ALL
DYNAMIC
RELAXED

Oracle source only:This property controls how SQDR Plus interacts with supplemental logging on a table.

  • The default behavior (ALL) requires SUPPLEMENTAL LOGGING (ALL) COLUMNS for all subscribed tables; the agent will ALTER the table automatically when publishing or subscribing if the Agent userID has sufficient authority.

  • With a value of DYNAMIC:

  • If the subscription uses a WHERE clause, SUPPLEMENTAL LOGGING (ALL) COLUMNS is still required.

  • If the subscription does not use a WHERE clause, the Agent will turn on or verify existence of SUPPLEMENTAL LOGGING (PRIMARY KEY) COLUMNS for tables with primary keys.

  • If a table has a unique constraint, SUPPLEMENTAL logging for both PRIMARY KEY and UNIQUE columns is required.

  • With a value of RELAXED, the Agent does not verify or try to add supplemental logging at all, but will simply stage whatever column data it obtains from LogMiner. If the subscription is using ROWID as its key, the subscription should be functional.

When this property is set to DYNAMIC or RELAXED, the compareChangeData property should also be set to true.

udbAuthentication

SERVER

SERVER

SERVER_ENCRYPT

 

DB2 for LUW source only: Specify this value (if necessary) to match the AUTHENTICATION value of the DBM configuration on the source system.

uncongestedThreshold

1 (minute)

1 (minimum); must be less than congestedThreshold

see congestedThreshold

userSpaceSize

16000000

16000000 maximum

DB2 for i source only: SQDR Plus creates a User Space (*USRSPC) object in the SQDR schema on the source system for each journal that is being monitored. This is used for communication between the log reader service program and the SQDR Plus agent. This parameter specifies the size of the *USRSPC object. If you have a large number of journals, decreasing this value will reduce disk usage at the expense of performance.

useTxSequence

false

true

false

Specifies that the Capture Agent should maintain the order of transactions. See Maintaining the Order of Transactions.

validateMetadata

false

true

false

Forces the Staging Agent to refresh the metadata for each table at startup.  The default behavior is to update metadata only when a change is detected in the schema or a subscription is added.

The following properties are created by the SQDR Manager at the time of creation of a database connection and should not be changed except under direction of StarQuest Support:

Property Name

Default Value

Range

Description

id

 

 

Identifier of parent agent for Oracle 12c pluggable database (appears in the parent agent configuration)

parent

 

 

Identifier of parent agent for Oracle 12c pluggable database (appears in subagent configuration)

pluggableDatabase

 

 

Name of the Oracle 12c pluggable database

port

 

50006 + n (where n is the Agent number)

SQDR Plus internal communcation. This port does not need to be visible to any remote computer.

SOURCE DATABASE:

 

 

 

sourceType

 

 

DB2400, UDB

 

sourceDbHost

 

 

 

Hostname or IP address of the source system.

sourceDbPort

50000

 

DRDA port of the source system (DB2 LUW source only)

sourceDbSchema

 

SQDR

 

Name of the schema (library or collection) in the remote (source system) control database that contains the control tables that are used by a particular instance of the SQDR Plus Staging Agent.

sourceDbDriver

 

com.ibm.as400.access.AS400JDBCDriver

com.ibm.db2.jcc.DB2Driver

 

sourceDbName

 

 

 

sourceDbUrl

 

jdbc\:as400\://as400

jdbc\:as400\://as400;prompt=false;secure=true

jdbc\:db2\://luwhost\:luwport/LUWRDB\:driverType\=4;deferPrepares\=false

 

useSourceAuthentication

true

 

 

sourceUserId

SQDR

 

 

sourcePassword

   

Encrypted

useSSL

false

true

false

DB2 for i source only: Instructs the Agent and Control Center to use a secure (SSL) connection to the source system. In addition, the sourceDbUrl should contain the property secure=true.

LOCAL CONTROL DATABASE:

 

 

 

controlDbType

 

UDB, Derby

 

controlDbDriver

 

 

com.ibm.db2.jcc.DB2Driver

 

controlDbPort

 

50000

1527

 

controlDbSchema

 

SQDRPn or SQDRPLUSn (where n is the nth Staging Agent)

Name of the schema (library or collection) in the local SQDR Plus control database that contains the control tables that are used by a particular instance of the SQDR Plus Staging Agent.

controlDbUrl

 

jdbc\:db2\://localhost\:50000/SQDRP0\:driverType\=4;deferPrepares\=false;

jdbc\:db2\://127.0.0.1\:1527/SQDRPLUS0\:driverType\=4;deferPrepares

\=false;

 

useAuthentication

true

 

 

userId

SQDR

 

 

password

 

 

encrypted password

TABLEINFO

false

 

A stored procedure that SQDR uses to find indexes.

The following advanced properties should not be changed except under direction of StarQuest Support:

Property Name

Default Value

Range

Description

alterOption

2

0
1
2

DB2 for i, DB2 for LUW, and Oracle sources only:

This property instructs SQDR Plus on how to handle an alter indication.

0 - always treat an "alter indication" as an alter even if no metadata change is detected.

1 - treat an alter where the metadata appears to not have changed as a reason to flag that a new baseline is needed.

2 (default) - ignore an alter where the metadata appears to not have changed.

For other sources, an Alter is always treated and reported as an Alter.

clientPassword

clientUser

 

 

Use these properties to override the credentials that the SQDR client uses to connect to the DB2 LUW staging database, rather than the default userID SQDR. Note that authorities to the staging database may need to be granted to this user.

forceDBCS

false

true

false

 

inflation

100 (percent)

100 (double)
200 (triple)
0 (no inflation)

This property advises SQDR Plus on the size of CHAR and VARCHAR columns when creating the DB2 LUW staging tables. For example, in a typical scenario involving a DB2 for i source (CCSID 37), some characters of a single or mixed byte column may result in 2 bytes when converted to UTF-8, so the default behavior of SQDR Plus is to double the size (100% inflation) of the CHAR and VARCHAR columns in the DB2 LUW staging tables.  

This is sufficient in most cases; however on rare occasions (e.g. a SQL Server source containing the x8D character, which converts to a 3 byte sequence in UTF-8) 300% inflation may be necessary to avoid a data out of range error (SQLCODE=-302, SQLSTATE=22001).

This property does not apply to the double-byte data types (GRAPHIC, VARGRAPHIC, NCHAR, NVARCHAR), as these are staged as UTF-16, and inflation is rarely needed.

maxNonTxTables

10

1-10

DB2 for i source only:Controls the maximum number of tables that will be grouped into a manufactured transaction when the DB2 for i source operations were not performed under source control. In most cases, the default value should be used; setting it to 1 will result in slower performance but source ordering accuracy.

msSqlChangeTracking

true

true
false

SQL Server source only: This value is set to false when you create a SQL Server agent specifying Change Data Capture rather than Change Tracking.

numControlDbConnectionsPerVm

50

minimum 5

Specifies the maximum number of database connections that are allowed from a Capture Agent process/job.

openWindow

false

true
false

Deprecated. This property causes the SQDR client to remain in openWindow processing (i.e. the state where the black dot in the green circle never goes away). This can now be configured on the SQDR client itself, and this property may be removed from SQDR Plus in the future.

oracleFetchSize

5 (rows)

 

Oracle source only: This property configures query blocking and may affect performance and latency when retrieving change data information from the Oracle Log Miner view.  Using a larger value may improve performance, but only if there is data waiting to be fetched; otherwise SQDR Plus may not receive updates in a timely fashion.  In practice, we have not seen a performance improvement beyond a value of 10.

See also oracleWhereClause, which limits results to change rows of interest.

oracleWhereClause

 

 

Oracle source only: SQDR Plus obtains changes from an Oracle source database by examining log history information obtained from Log Miner, which contains change activity for the entire Oracle database. SQDR Plus uses a general purpose WHERE clause to limit the amount of information returned from Log Miner and thus reduce the amount of log records that need to be examined for changes.  In some cases, performance can be improved by modifying the WHERE clause to further restrict the view to specific schemas and tables or other restrictions; contact StarQuest support for advice.

singleWorkerDefault

false

true
false

DB2 for i source only: This property instructs SQDR Plus to enforce a policy that an incremental group can contain subscriptions for only one journal,  ensuring that all received data is transactionally consistent.

Email Notification Parameters

 

 

 

starquestFrom

 

 

 

starquestMailOptions

 

 

Additional options to pass to the SMTP server, represented as tuplets of javax.mail configuration properties. For instance, use mailOptions=mail.smtp.port\=8889 to specify an alternative port. Additional properties can be added with a semicolon separator.

starquestMailPassword

 

 

 

starquestMailUser

 

 

 

starquestSmtp

mail.sqven.com

 

 

starquestTo

 

 

 

Pruning Parameters

 

 

 

alwaysPruneChangeData

 

 

 

maxPruneRows

 

 

 

maxPruneThreads

2

 

 

maxPruneTx

 

 

 

Launch Agent Configuration

The configuration for the Launch Agent is created when SQDR Plus is installed and in most cases does not need any changes.  Only some of the above parameters are applicable. In addition, the following parameters are used only by the Launch Agent; these are created when SQDR Plus is installed and should only be changed under direction of StarQuest Support.

Property Name

Default Value

Range

Description

DB2iSAVF

     

DB2iTemplateDB

     

DB2iTemplateDB_DB2

     

MSSQLTemplateDB_DB2

   

If this value is not found, the value of DB2iTemplateDB_DB2 is used instead.

OracleTemplateDB_DB2

   

If this value is not found, the value of DB2iTemplateDB_DB2 is used instead.

UDBRestoreOption

     

UDBTemplateDB

     

UDBTemplateDB_DB2

     

usingShutdownHook

true

true

false

Handles the special case where the Launch Agent and the Java processes it starts run with the same sessionId as a logged-in user. For example, on Windows 2003 these processes may be using the same sessionId as a user logged on to the console. Set this property to false to prevent the JVM's from terminating when the user logs off.