Copyright Information

StarQuest Data Replicator
Version 5.2
StarQuest Ventures, Inc.

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, people and events depicted herein are fictitious and no association with any real company, organization, product, person or event is intended or should be inferred.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of StarQuest Ventures, Inc.

StarQuest products contain certain trade secrets and confidential and proprietary information of StarQuest Ventures, Inc. StarQuest may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from StarQuest, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. Additionally, any public network IDs or addresses referenced in this document are for demonstration/example purposes only. The inclusion of such IDs and addresses in this document does not grant you any express or implied right to test or otherwise use or access any such network ID or address.

StarQuest, StarSQL, StarQuest Data Replicator, SQDR, and SQDR Plus are trademarks of StarQuest Ventures, Inc.
Java and MySQL are registered trademarks or trademarks of Oracle and/or its affiliates.
Microsoft, Windows, Windows Vista, and Windows XP are registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Db2, Informix, AS/400, iSeries are registered trademarks or trademarks of IBM.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Additional Copyright Information for Third Party Libraries

©Copyright 1997-2022. StarQuest Ventures, Inc.. All rights reserved.

02.02.2022 edition


Overview of the StarQuest Data Replicator

The StarQuest Data Replicator (SQDR) software allows you to replicate data between IBM Db2, Oracle, Microsoft SQL Server, Informix, MySQL, and other database environments. The Data Replicator provides efficient bi-directional replication of data, which can help integrate the data necessary for Internet and intranet publishing and electronic commerce, data warehousing, and data mining.

SQDR provides bi-directional data replication

You can replicate any table, portion of a table, or view, and you can schedule replications to take place at specified intervals or run them on demand. Replicating data from one database management system to another allows you to:

  • make the same data available to users of different types of Database Management Systems (DBMS)

  • make the same data available to multiple sites

  • balance network and database server loads by making the same data available from two or more database servers

  • create a backup copy of the data

The base StarQuest Data Replicator product provides full refresh replication, which copies all the source data that you specify for replication, regardless of when that data was last replicated. You can replicate views, aliases, or synonyms in addition to tables, and you can replicate indexes and referential integrity constraints that are placed on the source data. This provides a "snapshot" of the specified source data at the time of replication. With the addition of SQDR Plus on the source host you can replicate data incrementally, updating the target destination only with changed data.

The Data Replicator features allow powerful manipulation of the data as it is replicated, such as the ability to perform the following operations during replication:

  • perform horizontal partitioning, specifying which source rows to replicate

  • perform vertical partitioning, specifying which source columns to replicate

  • use SQL statements to modify data on the source or destination system, before or after each replication.

  • create derived columns in the destination table that contain values obtained by performing calculations on source data

Review the information in the Readme.html file before you install the Data Replicator software as it provides important information about any known issues that may affect how you isntall and use software. If you are upgrading from a previous version also be sure to review the information in Upgrading from a Prior Release of SQDR. After you install the software, refer to the online help for information about configuring and using Data Replicator. Press F1 or click the Help button in a dialog or on the toolbar to display the online help.


Data Replicator Components

The main components of the base StarQuest Data Replicator product are the Replicator Manager application and the Replicator Service. You use the Replicator Manager application to define sources, destinations, and subscriptions. Sources and destinations define the source and target data; subscriptions describe the data to be replicated for a particular operation. The source, destination, and subscription definitions are stored in a control database, which the Replicator Service communicates with to run the replications.

Installing the Data Replicator installs both the Replicator Manager and the Service, as illustrated in the following diagram. Each Replicator Service requires one control database, which can be created by any of the editions of SQL Server or Db2 LUW that are listed in System Requirements. If you do not already have SQL Server installed, Installing SQL Server for a Control Database describes how to obtain a version of SQL Server that Microsoft provides free of charge.

components of the base Data Replicator software

The Replicator Service uses the SQL Server service to communicate with the control database. Each Replicator Service typically manages multiple sources and destinations, which are defined as ODBC datasources (DSNs) that connect to a specific type of database. The subscriptions that you specify using the Replicator Manager define what data is replicated, how it is replicated, and when it is replicated.

If SQDR Plus is installed and configured for the source, you also can define subscriptions that incrementally update the destination data, rather than performing a full refresh of the data. Subscriptions to a host that has the SQDR Plus Capture Agent configured can perform either snapshot replication or incremental replication operations, as depicted in the illustration above.

See Also

Configuring ODBC Data Sources

Configuring the Replicator Service

Controlling SQL Server Services

Starting the Replicator Service


System Requirements

You can install and run the StarQuest Data Replicator (SQDR) on:

  • Windows Server Server 2012, Server 2012R2, Server 2016, Server 2019, Server 2022

  • Windows 8, 8.1, 10 or 11 64-bit edition

A minimum screen resolution of 1024 x 768 is recommended when using Data Replicator Manager.

If you are currently running a 32-bit version of SQDR on a 64-bit operating system, we recommend upgrading to the 64-bit version of SQDR; see the technical document Upgrading to 64-bit SQDR for upgrade instructions or contact StarQuest Support.

The SQDR Service Properties utility requires .NET Framework 4.6 or later.

The Data Replicator requires a control database in which to store definitions for sources, destinations, and subscriptions. You can use any of the following editions of IBM Db2 for Linux, Unix Windows (LUW) or Microsoft SQL Server for the SQDR control database. We recommend using the version of Db2 LUW provided by StarQuest (installed with SQDR Plus to enable incremental support). If you prefer to use SQL Server and do not already have a SQL Server installation, you can obtain the SQL Server Express free of charge from www.microsoft.com/downloads to use as a control database for the Data Replicator. Similarly, you can obtain Db2 Developer Community Edition free of charge from IBM. StarQuest Support can assist in selecting, obtaining, and installing the DBMS software for your control database.

  • IBM Db2 for LUW 11.5.5fp1. Existing installations using Db2 10.5, 11.1, and 11.5.0 will continue to function, but we recommend updating to 11.5.5fp1.

  • SQL Server 2008/2008R2/2012/2014/2016/2017/2019 (2008R2sp1 or later recommended)

The control database can be either local or remote. For a remote database, install the appropriate ODBC driver. For Db2 for LUW, use StarSQL (included in the SQDR installer). For SQL Server, use Microsoft ODBC Driver 17. See Using_a_Remote Control Database for details.

You can replicate data to and from any of the following database systems:

  • Db2 for z/OS v8.1 and later
  • Db2 for i (formerly known as DB2/400, DB2 UDB for iSeries, DB2 for i5/OS) OS/400 v5r4 and later
  • Db2 Universal Database for Linux, UNIX and Windows (Db2 for LUW) v8.2 and later
  • SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, 2019
  • Oracle 10g or later
  • Kognitio WX2 7.1 or later
  • MySQL 4.1 or later
  • Apache Derby 10.3 or later
  • TmaxSoft Tibero 6 or later
  • PostgreSQL
  • EDB Postgres Advanced Server
  • Actian Pervasive PSQL
  • Amazon Redshift
  • Salesforce
  • Snowflake
  • Exasol
  • Text files, including CSV format
  • Informix: any version of Informix supported by v4.70 or later of the IBM Informix ODBC driver:

  • IBM Informix Dynamic Server Enterprise or Express Edition 10.0, 11.10, 11.50, 11.70, 12.10, 14.10

  • IBM Informix Extended Parallel Server 8.50 and higher

  • IBM Informix SE 7.25

  • IBM Informix OnLine 5.20 and higher

Incremental replication is supported from the following platforms using the separately-licensed SQDR Plus product:

  • Db2 for i v5r4 and later

  • Db2 for Linux, UNIX, and Windows v9.7 and later

  • SQL Server 2012 and later

  • Oracle 11.2 or later

  • Informix 11.70 or later

  • MySQL 5.6 or later

  • MariaDB

  • Amazon Web Services Aurora

  • Amazon Relational Database Service (RDS) for MySQL 5.6 or later

  • Google Cloud SQL for MySQL

Access to Db2 databases requires the StarSQL ODBC/DRDA driver (v6.20 or later is recommended and is included in the SQDR installer), IBM Db2 Connect, IBM i Access for Windows, or IBM i Access Client Solutions.

If you use IBM i Access for Windows for accessing a Db2 for i database, install iAccess 7.1 (5770-XE1) with the SI64724 (June 2017) service pack (or later). If you use IBM i Access Client Solutions (5733XJ1), the Windows Application Package must be at the June 2017 level (or later).

For access to SQL Server, use the Microsoft ODBC 17 driver.

Access to Apache Derby (JavaDB) databases requires StarSQL 6.20 or later.

Oracle considerations:

  • Access to Oracle databases requires one of the following ODBC drivers:

  • SQDR bundled ODBC 8 driver for Oracle (recommended).

  • Oracle Instant Client (12.1.0.2 or later)

  • Full Oracle Client

  • Progress DataDirect Connect or Connect64 for ODBC (Oracle Database Wire Protocol Driver)

  • EasySoft Oracle ODBC driver.

  • When using Oracle as a source or destination, we recommend setting the system environment variable NLS_LANG in the system control panel to ensure proper character translation of character data to the local ANSI page of the SQDR system. For example, if you are using a US English Windows system, set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252. This is especially important if you disable the Bind Character types as Wide (Unicode) option in the SQDR service properties dialog.

For more information about the NLS_LANG environment variable, see:
Oracle 11g Appendix C Configuring Oracle Database Globalization Support
Oracle 12c Appendix C Configuring Oracle Database Globalization Support

  • When using the SQDR bundled ODBC driver for Oracle, make the following changes on the Advanced panel: supply the string ColumnSizeAsCharacter=1; ColumnsAsChar=1 in the Extended Options field.

MySQL considerations:

  • We recommend using Connector/ODBC 5.1.8 or later; do not use Connector/ODBC 5.1.6. You can also use the SQDR bundled ODBC driver or DataDirect Connect for ODBC if you are using MySQL Enterprise.

  • We recommend using the Unicode (w) version rather than the ANSI (a) version of the MySQL ODBC driver when SQDR is configured for Bind Character Types as Wide (Unicode), which is the default.

  • If MySQL is the destination for an incremental subscription, specify the option Return matched rows instead of affected rows in the Cursors/Results panel of the MySQL ODBC configuration or specify FOUND_ROWS=1 in a connection string. This is particularly important if the MySQL database is configured as case-insensitive and the source database is not.

  • Enable Prepare statements on the client in the Details/Misc panel of the MySQL ODBC configuration or specify NO_SSPS=1 in a connection string to avoid an error condition in which the server exhausts its prepared statement limit.

Informix considerations:

  • You can use the IBM Informix ODBC driver from Client SDK v4.10FC6X2 or later (latest version recommended), the IBM Db2 ODBC driver, the SQDR bundled ODBC driver, or the DataDirect Connect for ODBC driver to connect to Informix. Some drivers may have limitations; for example, the IBM Db2 ODBC driver cannot handle variable UDT (CLOB) fields.

  • The SQDR bundled ODBC driver cannot connect to an Informix 14.10 server; we recommend using the latest IBM Informix ODBC driver.

PostgreSQL considerations:

When using the psqlODBC driver for PostgreSQL as a destination, we recommend configuring the Advanced panel as follows:

  • Max Varchar Size: 32767

  • Max Long Varchar Size: 214748364 (2GB)

A connection string would like this:

Servername=myhost;Database=mydatabase;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364

When using the SQDR bundled ODBC driver for PostgreSQL as a destination, we recommend configuring the Advanced panel as follows:

  • Enable the checkbox for Fetch TSWTZ as Timestamp (the default is to treat this type as CHAR(27))

  • Max char Size: 255

  • Max Varchar Size: 32767

  • Max Long Varchar Size: 214748364 (2GB)

A connection string would like this:

HostName=myhost;Database=mydatabase;PortNumber=5432;FetchTSWTZasTimestamp=1;MaxCharSize=255;MaxVarcharSize=32767;MaxLongVarcharSize=214748364

The SQDR bundled ODBC driver for Postgres cannot connect to a Postgres server with a user configured for the new password encryption method scram-sha-256 that was introduced in Postgres 10. Either use the psqlODBC driver, or configure the userID to use the older password encryption method.

Snowflake considerations:

  • Issue the following command for the user being used by SQDR for the Snowflake destination:

alter user myuser set TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ'

  • Snowflake does not support INDEX create/drop, but does support primary key/unique index constraints.

Other Drivers

In addition, you can use the Data Replicator with other databases if they can be accessed through an ODBC driver that complies with Level 2 of the ODBC 3.51 specification.

If you use an ODBC driver other than StarSQL there may be additional configuration requirements to use the driver for replications with SQDR. The SQDR Release Notes and the Customer Support area of the StarQuest Web site provide details about known issues for using SQDR with other products.


What's New in This Release

  • Bugfixes and Enhancements


Upgrading from a Prior Release of SQDR

Customers of versions prior to 5.10 should be aware of the following changes in functionality:

  • The 32-bit version of SQDR is is no longer supported. If you are currently running a 32-bit version of SQDR on a 64-bit operating system, you can upgrade to the 64-bit version of SQDR; see the technical document Upgrading to 64-bit SQDR. You will need to save your configuration and uninstall the 32-bit version of SQDR before installing the 64-bit version and restoring your configuration.

Customers of versions prior to 4.50 should be aware of the following changes in functionality:

  • Windows XP. Vista, Server 2003, and Server 2008 (non-R2) are no longer supported. You can use this release of SQDR with any of the versions of the Windows operating listed in System Requirements.

Control Database Update

SQDR 5.10 introduces Control Database Schema version 5.10; previous versions are 4.90, 4.50 4.02. Contact StarQuest Support for assistance if you are updating from SQDR prior to v4.90.

If you are updating from SQDR 4.90 through 5.07, run the Data Replicator Configuration to update your existing control database to the 5.10 format. Select the checkbox to run the Configuration on the final screen of the installation wizard, or select Data Replicator Configuration from the Program Group, and choose "Use an existing control database and tables".

  • If you are upgrading from any version of StarQuest Data Replicator earlier than 4.90, you must run the Data Replicator Configuration (while at v4.90 level) to migrate your settings from the registry to the Control database.

  • If you are updating from SQDR 4.50 through 4.76, you must first upgrade the control database to 4.90 format, and then update it to the 5.10 format.

  • If you are updating from SQDR 4.0x or 4.1x, you must first upgrade the control database to 4.50 format, then 4.90 format, before updating to the 5.10 format.

  • If you are updating from SQDR 3x,, you must first upgrade the control database to 4.02 format, then 4.50 format, then 4.90 format, before updating to the 5.10 format.

Refer to the Upgrade Considerations of the Release Notes (readme.html) for additional considerations.

Before you update the Data Replicator software, we recommend that you back up your existing SQDR control database and the following key from the Windows registry. This is especially important when updating from SQDR 3.6 and earlier, which performs a major update (the installation program for Data Replicator automatically checks for a prior version of Data Replicator software and prompts for approval to remove it), or when updating from 32-bit SQDR to 64-bit SQDR. Although you have an option of preserving the service properties and control database when you uninstall SQDR, it is a good idea to create a backup copy.

  • HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC (for 32-bit SQDR on a 32-bit system or 64-bit SQDR on a 64-bit system)

  • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\StarQuest\SQDRSVC (for 32-bit SQDR - e.g. SQDR v4.1x & earlier - on a 64-bit system)

SQDR Plus information If you are upgrading the SQDR Plus software, pause or stop groups that incrementally replicate data to or from that host to avoid receiving error conditions in the Replicator Manager while the Capture Agent is not available.


Installing SQL Server for a Control Database

Installing Db2 for LUW or SQL Server for a Control Database

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. You can use any of the editions of Db2 for Linux UNIX & Windows (Db2 for LUW) or Microsoft SQL Server listed in System Requirements for the Data Replicator control database.

Connection to the control database can be configured using a DSN-less connection string; you do not need to create an ODBC data source.

Using Db2 for LUW

If you are using SQDR on the same machine as SQDR Plus, we recommend using the local instance of Db2 for LUW installed for use with SQDR Plus when possible. In this case, the SQDR Plus installer creates the database (SQDRC).

If you are installing SQDR on another machine, you can either

  • Use the instance of Db2 for LUW installed with SQDR Plus remotely (see Using_a_Remote Control Database below).

  • Install Db2 LUW on the machine running SQDR using the download provided by StarQuest.

  • Obtain Db2 Community Edition free of charge from IBM.

If Db2 is installed but the database SQDRC does not already exist, create the database by issuing the command db2 create database SQDRC. Once the database exists, use the SQDR Configuration utility to create and populate the control tables.

IBM Data Studio (which is downloaded and installed separately) can be used as a GUI administrative tool.

Using Microsoft SQL Server

If you prefer to use SQL Server for the control database and do not already have a SQL Server installation, you can obtain the Microsoft SQL Server Express free of charge from www.microsoft.com/downloads to use as a control database for the Data Replicator.

SQL Server Management Studio (which is downloaded and installed separately) can be used as a GUI administrative tool.

Create the control database e.g. controlDB with SSMS or by issuing the SQL command create database controlDB. Once the database exists, use the SQDR Configuration utility to create and populate the control tables

Using a Remote Control Database

Typically, a local instance of Db2 LUW or SQL Server is used for the control database.

However, you can use a remote instance of Db2 LUW using the StarSQL ODBC driver included in the SQDR installer. The database SQDRC must be created in advanced as described above.

Similarly, you can also use a remote instance of SQL Server by installing the Microsoft ODBC Driver for SQL Server (17 or later) and configuring a connection to the remote SQL Server system and connecting (while running SQDR Configuration) with a user with sufficient authority to create populate tables in an existing database (e.g. controlDB). Alternatively, the database can be created and populated (with SQDR Configuration) in advance by the SQL Server database administrator.


Installing the Data Replicator

Installing the Data Replicator Software

Before you install the Data Replicator software, make sure the pre-requisite hardware and software is available. If you are upgrading from a prior release of the Data Replicator, be sure to review Upgrading from a Prior Release of SQDR.

To install the StarQuest Data Replicator software, execute the Setup.exe program from the distributed software image. The InstallShield Wizard presents a series of dialogs to lead you through the installation. You can install the Data Replicator components to a location you specify.

The last Wizard panel provides an opportunity to start the ODBC Administrator and/or the SQDR Configuration after you click Finish. Using a DSN-less connection string to define connections to the control database and source and destination databases is recommended, but if you prefer to use ODBC data sources, use the ODBC Administrator to configure Data Source Names (DSNs) that define how to access a specific instance of data in a DBMS, including a DSN for the Data Replicator control database.

Use the StarQuest License Configuration dialog to enter a license key, validate an existing license key, or request a new license key using the Internet. You must have a valid Data Replicator license key before you can run replications.

The SQDR Configuration application must be run to create control tables, configure registry entries, and configure the SQDRSVC service before using Data Replicator Manager to define subscriptions.


Getting Started

After you install the Data Replicator software, you need to perform the following steps to configure the environment necessary for replicating data.

  1. We recommend using a DSN-less connection string to connect to the control database. However, if you prefer to use an ODBC data source and it does not already exist, configure an ODBC DSN for the local Replicator Service control database.

  2. Ensure that the Replicator Service has permission to access its control database, and that the DBMS systems that you want to replicate to and from have appropriate database permissions set for the source and destination tables. See Configuring Database Permissions for more information.

  3. If it is not already running, start the DB2 for LUW or SQL Server service.

  4. If you are using Db2 for LUW for a control database, ensure that the database SQDRC has been created. This is automatically created by the installer for SQDR Plus (4.50 & later). Use the command db2 list database directory to see a list of existing DB2 for LUW databases; use db2 create database SQDRC to create the database if necessary.

  5. Use the Data Replicator Configuration utility to configure the Replicator Service control database.

  6. Configure a license for using the StarQuest Data Replicator. You can define sources, destinations, and subscriptions without a license, but you must configure a valid license key before you can replicate data.

  7. Start the Replicator Manager.

When you start the Replicator Manager, four folders appear to show the Sources, Destinations, Connections, and Groups the service is managing. Refer to Using the Replicator Manager for information about the main Replicator Manager window. The Replication Process Overview topic describes the general steps for setting up and running a replication operation.


Uninstalling the Data Replicator

Use the Windows Control Panel Add or Remove Programs tool to remove the Data Replicator.

When you remove a previous version of Data Replicator, a prompt appears asking whether you want to remove the SQDR service properties and the control tables (definitions of sources, destinations, and subscriptions). Select No in response to the question about removing these if you plan to re-install SQDR and want to preserve the properties and control tables that have been defined.


Using the Replicator Manager

You use the Data Replicator Manager to set up and administer replication operations. The topics in this section describe how to start the Replicator Manager, navigate among the replication objects, understand the status of subscriptions and groups by their icon status, and provides an overview of the replication process.

If you need information as you use the Replicator Manager, click the Help button in a dialog, the Help button on Toolbar button on the Toolbar, or press F1 with an object or command selected to display context-sensitive information. If the help topic that appears does not answer your question, use the Contents, Index, or Search tabs to find other relevant topics.

SQDR Plus specific information Some options in the Replicator Manager apply only to incremental replication subscriptions, which require that SQDR Plus be installed at the source. Information that is specific to performing incremental replication is marked with the SQDR Plus specific information icon in this help system to distinguish it from information that applies to the base Data Replicator software. Topics that apply only to SQDR Plus appear in the Contents with a red star (*) in the upper left corner of the page icon.


Starting the Replicator Manager

If the Replicator Manager is not already running, start it by selecting Data Replicator Manager from the StarQuest Data Replicator program group.

starting the Data Replicator Manager

The Data Replicator Manager automatically starts and connects to the Data Replicator Service on the local computer. The Data Replicator Service is represented by the name of the computer in the left pane of the Replicator Manager window. The following illustration shows the Data Replicator Service running on a computer named HAMMERHEAD.

main Data Replicator Manager window

The service connected icon icon next to the service indicates that the service is running with a green up arrow. Stopping the service changes the icon to SQDR Service is stopped, with a red down arrow.


Navigating the Main Window

The left pane of the Replicator Manager contains folders for sources, subscriptions (beneath each source), destinations, connections, and groups. To expand an item in the left pane of the main window, click the plus (+) sign to the left of the item. To collapse an item, click the minus (–) sign.

When you click an item in the left pane, the right pane either displays more information about the item selected in the left pane, or displays the items available at the next lower level in the hierarchy. The format of the information varies according to which command is enabled in the View menu, showing either icons of various sizes and layout or detailed information for the selected object.

Items in the left pane are organized as follows:

  • At the top level, the hierarchy shows the Replicator Service on the local computer.

  • Below the computer name that represents the local Replicator Service, there are folders to organize Sources that provide the source data for replication, Destinations that are targets to receive data from a source, database Connections that are currently open by the Replication Service, and Groups of subscriptions.

  • Below each source, the hierarchical diagram lists any Subscriptions that replicate data from that source.

When you define a subscription, it appears under the Subscriptions folder of the source it is associated with and, if applicable, also under the group to which it belongs. For example, notice the Customers subscription listed under the Northwind Sample source and under the Members folder of the Northwind Subscriptions group in the following illustration. Within a group the subscription is named with the source_name/subscription_name to help identify the origin of the subscription.

how subscriptions appear in the hierarchy

The Icon Legend topic describes how the icons reflect the status and history of the subscriptions and groups. You also can filter the list of subscriptions in a group to show only those with particular status values, as described in Filtering the Subscription List.


Icon Legend

The Data Replicator Manager uses graphics that vary in both color and shape to help quickly distinguish the subscription type and the status.

 | Subscription | Group | Status
Snapshot replications | Snapshot subscription | group of snapshot subscriptions | Idle
 | Snapshot subscription running |  | Running
 | subscription is busy (running elsewhere) |  | Running elsewhere
Incremental replications | Snapshot subscription running , subscription is busy (running elsewhere) |  | Baseline replication is running or running elsewhere
 | Incremental subscription active | group of active Incremental subscriptions | Actively monitoring and applying change data
 | baseline is being synchronized | baselines are being synchronized | Synchronizing the baseline(s)
 | Incremental subscription with warnings | Group of Incremental subscriptions with warnings | Warning of a potential problem
 | Incremental subscription with error | Group of Incremental subscriptions with error | Error in one or more subscriptions

When you select a subscription or group, the status, history, and statistics for the replication operations appear in the right pane with the following icons.

Icon | Represents
replication successful | replication ran successfully
 | incremental replication was replayed successfully
 | replication is suspended
replication ran with warning(s) | replication ran with warnings
replication failed | replication failed due to errors
history for incremental subscription | history for incremental subscription
active progress | active incremental subscription
flagged for potential problem | incremental subscription that has warnings
incremental subscription is paused | incremental subscription that is paused
incremental subscription has errors | incremental subscription that has errors

SQDR Plus information For incremental subscriptions the status of the subscription and group icons reflects the most severe status of any subscription in the group. After the baseline replication completes and the subscription is polling the source for changes in a normal manner, the statistics icon is an active pencil active progress, the subscription icon is an active page Incremental subscription active, and the group icon is a group of active pages group of active Incremental subscriptions. There may be a period of time that the page and group icon appear with a black center in the green circle, such as synchronizing the baseline, until SQDR confirms that the baseline is synchronized with any changes that occurred while the baseline was being performed.

If the subscription encounters errors that may resolve without intervention, such as INSERT, UPDATE, or DELETE errors on the target table, the icon for the subscription's statistics changes to flagged for potential problem and the subscription and group icons change to the warning status Incremental subscription with warnings and Group of Incremental subscriptions with warnings. Similarly, if an incremental replication operation encounters a serious error, such as the source table being dropped, the statistics icon changes to the error pencil incremental subscription has errors and the subscription and group icons change to error page(s) Incremental subscription with error.


Overview of Replication Process

Replication Process Overview

Replication operations are controlled by a subscription that is associated with the source data. You create a subscription for each object (table, view, alias, or synonym) that you want to replicate. You also can create multiple subscriptions simultaneously to define several replication operations that use the same source, or a group of replication operations that use the same or different source data. If the source is enabled with an SQDR Plus Capture Agent, you can define subscriptions that monitor the source data for changes and incrementally apply the changes to the target destination.

The following steps summarize how to use the Replicator Manager to define and run a replication operation.

  1. Select an existing source or add a source for the data you want to replicate.

  2. Select or add a subscription that defines the replication operation you want to run.

The Data Replicator checks the subscription properties for validity when you save the subscription, but some characteristics, such as having proper authority for destination tables, must be tested by running the subscription.

  3. Execute the Run Subscription command and review the destination table to verify that the subscription creates the desired data.

As described in Scheduling Replications, you can have snapshot subscriptions run automatically at a specific future date or on a recurring basis. Subscriptions that perform incremental replication first perform a baseline replication and then monitor the source for changes, performing another baseline only as necessary to re-synchronize the target data according to the schedule defined for the group of incremental subscriptions.

For a subscription to run automatically, the appropriate network connections must be functioning at the scheduled time, and the Replicator Service must be able to access the control database and the source and destination databases.


Configuration Tasks

Before you can use the StarQuest Data Replicator to replicate data, you need to:

  • configure ODBC data sources, which define how to access an instance of data in a database management system

  • configure database permissions

  • configure the Data Replicator control database

  • configure a license for using the Data Replicator


Configuring ODBC Data Sources

ODBC is a standard interface for accessing data in relational databases. An ODBC data source name (DSN) defines the information that a driver needs to access a specific instance of data in a DBMS.

In older versions of SQDR, it was necessary to create an ODBC DSN for the SQL Server database used as a control database, plus an ODBC DSN for each host DBMS that you want to replicate to or from. This is no longer required; we recommend using connection strings for all database connections - see Using Connection Strings for details.

The following information is provided for historical reasons and for users who prefer to use ODBC data sources rather than connection strings.

Using ODBC Data Sources

Define an ODBC DSN for the Db2 for LUW or SQL Server database that the Data Replicator Service will use as a control database, and an ODBC DSN for each host DBMS that you want to replicate to or from. For convenience, the StarQuest Data Replicator program group contains a shortcut for starting the Windows ODBC Data Source Administrator.

Important Information

If you are using the StarSQL ODBC driver to perform incremental replications, change the isolation level to Read Committed. You can change the isolationLevel value from the Expert Page of the StarSQL Data Source Wizard.

If the StarSQL-based DSN is for a SQL Server target database that will receive incremental changes, also be sure that the collation sequence of the database is Case Sensitive. Setting the collation sequence to Case Sensitive helps to ensure there are unique fields for tracking the change data. You can use the SQL Server Studio to set the collation for a new database. Use the ALTER DATABASE (Transact-SQL) command with the COLLATE clause to change the collation for an existing database. A case-sensitive collation has the characters "CS" appended to the Collation Designator, such as SQL_Latin1_General_CP1_CS_AS for U.S. English systems.

Selecting the Data Source Type

The ODBC Data Source Administrator allows you to create User DSNs and System DSNs. A System data source is visible to all users on a computer, including Windows services such as the Data Replicator. The default configuration is to have the Data Replicator service log on as a System Account and use System DSNs. If you want the Data Replicator service to use a User data source, which is visible only when the user who created it is logged in, you must configure the Data Replicator service to log on as the user account that created the DSN.

Selecting the ODBC Driver

When you create a new data source, the Data Source Administrator prompts you to select a driver for the data source.

Refer to the technical document SQDR: Recommend ODBC Drivers for details.

Selecting the Authentication Method

A SQL Server DSN can be configured to use Windows authentication or SQL Server authentication. The authentication mode that the DSN uses must be the same mode that the SQL Server is configured to use. If the DSN and SQL Server are configured to use Windows authentication, the DSN uses a trusted connection and all connections are validated using the Windows network login ID. If the DSN and SQL Server are configured to use SQL Server authentication, the DSN uses a non-trusted connection and all connections through the DSN are validated using the SQL Server authentication. See SQL Server Authentication for more information about the methods SQL Server uses to authenticate connections.

Specifying the Default Database for the Control Database DSN

If you are creating a DSN to SQL Server for the SQDR control database, you may need to set the control database to be the default database. For example, in the following pane of the ODBC Data Source Configuration Wizard the default database is changed to SQDRControlDB, which is the SQDR control database for this example DSN.

change the default database for the SQDR Control Database DSN


Configuring Database Permissions

In addition to the account that a user needs to be authenticated for logging in to a database, database systems validate the permissions that a user has for accessing and working with individual databases. The activities a user is allowed to perform are determined by the permissions assigned to the user account from which they gained access to the database.

The StarQuest Data Replicator fully supports the host's data access security. It accesses host data through an ODBC data source, and each data source is configured with a host user name and password. The host database administrator manages the access rights for the host database accounts, so the Data Replicator can perform only the database operations that are permitted by the host account.

Regardless of which SQL Server authentication mode you use to connect to an instance of SQL Server, make sure that each login that Data Replicator will use is assigned to the database you want that login to use. In addition, make sure that the login account has permissions that allow it to perform the necessary tasks, as specified below:

  • Source connections require permission to SELECT any source table columns or views that will be replicated.

  • Destination connections require appropriate permissions to work with the data and execute procedures, including the following permissions:

    • SELECT table

    • INSERT table

    • UPDATE table

    • DELETE table

    • CREATE table

Depending on the destination options that are defined for the subscription, the user ID also may need permission to DROP the table. If the table has an index, the user ID also may need permission to alter tables and drop and create indexes. To alter source data either before or after replication, the user ID needs any privileges required by the SQL processing statements defined for the subscription.

  • Control database connections require all of the permissions listed above for destination connections, plus permission to DROP and UPDATE tables and indexes.


SQL Server Authentication

The Replicator Service interacts with SQL Server security for these purposes:

  • To read from or write to Data Replicator control tables

  • To read from a SQL Server object that is a replication source

  • To write to a SQL Server object that is a replication destination

In each of these situations, the Replicator Service must log in to SQL Server using a valid login and password. The Security properties of the SQL Server and of the DSN affect which login and password will be used and how they will be validated. SQL Server can operate in one of two security modes: Windows Only authentication and SQL Server and Windows (Mixed Mode) authentication.

Be sure the authentication method selected for a SQL Server DSN matches how the SQL Server is configured. Data Replicator uses the DSN to determine which authentication method is required by a particular SQL Server.

Windows Only Authentication Mode

When the Replicator Service attempts to log in to a SQL Server that is using Windows Only authentication, SQL Server uses the current Windows login. If the Replicator Service is set to log on as System Account, the SQL Server uses the built-in sa SQL Server account. As with all SQL Server logins, any Windows login used for the Replicator Service under the Windows Only authentication mode must have appropriate permissions for the operations the Replicator Service will perform.

SQL Server and Windows Authentication Mode

When Data Replicator attempts to log in to a SQL Server that is using SQL Server and Windows authentication, also known as Mixed Mode, the user ID and password that the SQL Server checks depends on the kind of connection being established and on the authentication mode assigned to the user ID. Data Replicator supplies different user IDs for different types of connections:

  • For a control database connection, Data Replicator supplies the login and password set by the Data Replicator Configuration program.

  • For a source connection, Data Replicator supplies the login and password set in the Replicator Manager Source Properties dialog for the source associated with the current subscription.

  • For a destination connection, Data Replicator supplies the login and password set in the Replicator Manager Destination Properties dialog for the destination associated with the current subscription.

Refer to your SQL Server documentation for more information about managing security levels and authentication modes.


Configuring the Replicator Service

The Data Replicator service requires a control database in which to store information about sources, destinations, and replication subscriptions.

You can use either IBM Db2 for LUW or SQL Server for the control database, and access it using either a connection string or an ODBC DSN.

We recommend using a connection string, as described in the Quick Start Guides for SQDR and SQDR Plus. The choice of Db2 vs SQL Server is dependent on your environment. If SQDR is installed on the same system as SQDR Plus, we recommend using the local Db2 LUW that is installed for use by SQDR Plus.

After configuring a connection to a control database with the SQDR Configuration program, there are additional properties that you can configure from the Data Replicator Manager to control the interval at which data is committed, how connections and logging are handled, and whether notifications about the status of groups are sent to an email address. After you start the Replicator Manager, select the service and edit the properties, as described in the topic Changing Replicator Service Properties.

The following information is provided for historical reasons and for users who prefer to use ODBC data sources and SQL Server.

Using SQL Server and ODBC DSN for a control database

For the control database you must have an ODBC data source defined that connects to SQL Server 2005 or later. The control database can use the default or a named instance of SQL Server. If you do not already have SQL Server installed, Installing DB2 for LUW or SQL Server for a Control Database describes how to obtain a version of SQL Server that Microsoft provides free of charge.

After you define an ODBC data source for the Data Replicator control database, use the Data Replicator Configuration Wizard to configure the control database. The Data Replicator Configuration shortcut of the StarQuest Data Replicator program group starts the configuration wizard, which provides details about the configuration options.

To configure the Data Replicator service, you must log in to SQL Server using a valid SQL Server administrative (sa) account. The Data Replicator Configuration Wizard prompts for the sa user ID if SQL Server is configured to use a trusted connection using Windows authentication, or the sa user ID and password if SQL Server is configured to use Windows and SQL Server authentication.

Each user in the SQL Server administrator group has a default object schema, or owner, of "dbo." The Data Replicator Configuration Wizard allows you to specify a different schema name for the Data Replicator control tables if desired. If you specify a schema name, it must be the default schema for the associated user ID.

Important Information

If you change the SQL Server logon account properties after you configure the Data Replicator service, run the Data Replicator Configuration Wizard again to update the Replicator Service configuration to match the new logon account information.


Configuring a License

After you install the StarQuest Data Replicator software, a License Configuration dialog appears so you can enter a license key, validate an existing license key, or request a new license key using the Internet.

You must configure a valid license key before you can run any SQDR subscriptions to replicate data. SQDR Plus requires a separate license to perform incremental replication from a particular source to a particular destination, although multiple groups of incremental subscriptions can use the same license to replicate from the same source and destination pair.

The License Configuration dialog allows you to:

  • configure a Data Replicator license

  • use the Internet to request a license, or to obtain information about existing licenses

If the License Configuration dialog does not appear automatically, you can display it by selecting the License Configuration shortcut from the StarQuest Data Replicator program menu. Click the Help button in the License Configuration dialog for details about using the License Configuration utility.

To use the online licensing feature of the License Configuration utility you must have a Web browser installed and be able to connect to the Internet. Select the License Online tab of the License Configuration dialog to request a new license or retrieve existing license information.

If you are not able to license the product online, you can request a license key via email as described in Contacting StarQuest.


Working with the Replicator Service

The Data Replicator Service runs as a Windows service. As such, you can control it from the Windows Services Control Panel or from the Service menu of the Data Replicator Manager. The Data Replicator Service is represented by the name of the computer in the left pane of the Replicator Manager window.

The SQL Server service must be running for the Replicator Service to access its control database, and the Replicator Service must be running to replicate data. The topics in this section describe how to:

  • control the SQL Server services

  • start and stop the Replicator Service

  • change the Replicator Service properties

  • manage connections to the Replicator control database

  • Managing Remote Servers


Controlling SQL Server Services

Controlling Services for SQDR, Db2 for LUW and SQL Server

SQDR Service

SQDR runs as a Windows service. You can review the status and manage the Data Replicator service from the Windows Services Control Panel or from the Data Replicator Manager application. The Data Replicator service name is StarQuest Data Replicator (SQDRSVC).

When you start the Data Replicator Manager it automatically starts the Data Replicator Service. Closing the Replicator Manager does not automatically stop the Replicator Service or any Db2 or SQL Server services. This allows any replication operations that have been scheduled to run at their scheduled time. When the time comes to run the job (a subscription that has been scheduled from the Replicator Manager), the Replicator Service must be running.

Db2 for LUW

If you using Db2 for LUW for the SQDR control database, the Db2 service must be running for the Replicator Service to access its control database.

You can review the status and manage the Db2 service from the Windows Services Control Panel, from the Db2 system tray icon, or from a Db2 command line environment.

SQL Server

Similarly, if you are using SQL Server for the SQDR control database, the SQL Server service must be running for the Replicator Service to access its control database. You can start an instance of SQL Server automatically, manually, or from the command prompt. Both the automatic and manual methods start an instance of SQL Server as a Windows service.

Using Windows Services Control Panel

You can review the status and manage the SQL Server services from the Windows Services Control Panel. The default SQL Server service is named MSSQLSERVER. Named instances of SQL Server appear as MSSQL$instance_name.

Windows Services Control Panel

Refer to the SQL Server Books Online for detailed information about starting, stopping, and pausing the SQL Server components.


Starting the Replicator Service

Starting and Stopping the Replicator Service

Starting the Replicator Manager automatically starts and connects to the Replicator Service on the local computer. The service appears in the left pane of the Replicator Manager window as the computer name, such as MYCOMPUTER. Closing the Replicator Manager does not automatically stop the Replicator Service, as the Replicator Service must be running for it to monitor and run scheduled replications and, if SQDR Plus is deployed, poll the source databases for incremental updates.

Should you need to stop the Replicator service, select the Stop Service command, after which you can select the Start Service command when you are ready to re-start the service again. The Stop Service and Start Service commands are available from the Service menu of the Replicator Manager, or by selecting the computer name and right-clicking to display a context menu.

When you select Stop Service, a dialog appears that allows you to specify a wait time for completion. Default is 30 (seconds); maximum is 300. A value of 0 specifies an immediate shutdown.

You also can start and stop the Data Replicator service from the Windows Services Control Panel.

The Replicator Service must be running to manage the sources, destinations, and subscriptions that appear in the Replication Manager. Stopping the Replicator Service, either from the Replicator Manager or the Windows Services Control Panel, cancels any snapshot replication operations that are in progress, and pauses incremental replication operations.

To run replications at a scheduled time, the Replicator Service must be running at the time the scheduled replications are to begin.

The icon next to the service indicates whether the service is running or stopped, as shown in the following table.

Service Icon | Service Status
SQDR Service is stopped | service is stopped
SQDR Service is running | service is running


Changing Replicator Service Properties

The Data Replicator Service has properties that determine how it handles connections and subscription logs. You can review and change the service properties by selecting the service from the Data Replicator Manager and selecting the Properties command. The Data Replicator Service is represented by the name of the computer in the left pane of the Replicator Manager window.

SQDR Service properties

  1. In the left pane of the Replicator Manager main window, select the computer name, right-click, and select the Properties command to display the Service Properties dialog.

  2. Change the service settings as desired, clicking the Help button to display information about the properties on each tab in the dialog.

  3. Click OK to save the properties and return to the main window.

See Also

Managing Control Database Connections


Managing Control Database Connections

The default Data Replicator configuration allows each Replicator Service to establish up to 100 simultaneous connections to its control database. You can change the connection limits via the ControlDB tab of the Service Properties dialog.

When the Replicator Manager connects to the Service and its associated control database, two connections are reserved for use by the Replicator Manager session. These connections are not counted toward the total number of connections governed by the Maximum Number of Connections setting.

To identify reserved connections, select the Connections folder in the left pane of the Replicator Manager main window, and then select the Details command from the View menu. The connection that is reserved for the Replicator Service to communicate with its control database has a name of ControlDB in the Type column of the right pane.

If the number of currently active connections is greater than or equal to the value for Maximum Number of Connections, you cannot establish a new connection or obtain access to a previously pooled connection. If you start an operation that requires such a connection, Replicator Manager may enter a waiting state until a connection is available or a specific timeout period has passed. Similarly, replication operations may fail, showing error messages such as "Could not add connection to list." If an operation fails in this manner, the Data Replicator attempts to re-run the subscription at its next scheduled replication time. To avoid replication failures due to connection limits, increase Maximum Number of Connections to a higher value, decrease the group concurrency settings, or change the replication schedules to reduce the number of subscriptions that run simultaneously.

To stop incremental subscriptions from communicating with a host, such as when the host needs to be taken offline for maintenance, pause the subscriptions that use the host and then delete the connections that are established to it. When the host is again available you can resume the subscriptions, which re-establishes the connections.


Managing Remote Servers

You can manage SQDR running on a remote machine by selecting Add Server from the Service menu and entering the hostname of the remote system.

You can remove servers from the Data Replicator Manager by selecting the server and either selecting Remove Server from the Service menu, or select the server, right-click, and choose Remove Server.

After adding a remote server, the icon will display the status of the service. Connect to it by double-clicking on the service icon or by choosing the icon and selecting Connect to Service from the Service menu or the context-sensitive drop-down menu when you right-click on the service icon. You will now see the full details of the remote SQDR system.

You can examine the product version of the service being managed in the Service Tab of Service Properties.

To reduce network traffic and system load when using the Data Replicator Manager to manage many active systems, select Disconnect Service for systems that you are not currently actively managing.

To manage remote SQDR systems from a workstation that is not running the SQDR service, StarQuest supplies a special installer that contains only the Data Replicator Manager application and its dependencies. Do not use this installer on a system where the full SQDR product is installed.

Requirements

  • You must have network connectivity to the remote system.

  • We recommend that the remote server use a static IP address.

  • If the Windows Firewall is enabled on the remote system, create a rule to allow access to the SQDR service application (C:\Program Files\StarQuest\SQDR\sqdrsvc.exe) and enable the three pre-defined rules for Remote Service Management (NP-In, RPC, and RPC-EPMAP).

  • Log on to your local workstation (or run the drmgr application) as a domain user; this user does not need to be a member of the local Administrators group on the local workstation. On the remote system, add the user to the local groups Administrators and Distributed COM Users. Alternatively, you can use a domain administrator account.

Limitations

Selecting Start Service for a remote server may not function. However, Connect to Service will both start the service and connect to it, assuming that the user has DCOM activation privileges.


Working with Sources

A source provides the data that you want to replicate. It is identified by a particular Data Source Name (DSN). You can use the same source object with different destinations for various purposes. A source object can be a table, view, alias, or a synonym that serves as a nickname to a relational table.

When you select the Sources folder in the left pane of the main window and select Details from the View menu, the right pane displays the following information for each source:

  • The name Data Replicator uses to identify the source

  • The ODBC Data Source Name (DSN) associated with the source

  • The User ID that Data Replicator uses when obtaining data from the source

To sort the list of sources, click any right pane column header to sort according to the values in that column. Click the same column header again to change between ascending and descending sort order.

When you select a specific source in the left pane of the main window and have Details selected for the View menu options, the right pane displays the following:

  • The name that Data Replicator uses to identify the source

  • The ODBC Data Source Name (DSN) associated with the source

  • The User ID that SQDR uses when obtaining data from the source (for a SQL Server source, this is the SQL Server login)

  • The number of connections to the source that are currently active

  • The number of connections to the source that are being held open in the connection pool

Select a source in the right pane and right-click to display a context menu from which you can delete the source (which also deletes any subscriptions to that source) or display the source properties.


Adding a Replication Source

Adding a Source

To add a source from which data will be replicated, you select the source DSN and provide the user account information that is required to access the source object.

  1. From the Insert menu, select the Source command.

The Insert->Source command displays the Source Properties dialog.

  2. EITHER Configure a source using a DSN-less connection string by performing the following steps:

  1. Select the ODBC Driver radio button.

  2. Select an ODBC driver from the list. For instance, select ODBC Driver 17 for SQL Server for SQL Server sources and StarSQL 32 or StarSQL (64-bit) if you are using StarSQL to access Db2 sources.

  3. The ODBC driver name will appear in the Source or Destination Name field above the DSN list. Be sure to modify this name to clearly identify the database; the name cannot be changed after it is saved.

  4. Enter a valid connection string (maximus 254 characters) e.g.

StarSQL: Server=MYRDB;HostName=myhostip;Port=446;IsolationLevel=2;PkgColID=SQDR

SQL Server: Server=MYHOST\SQLEXPRESS

See Using Connection Strings for more examples.

OR Create a source using an ODBC data source by performing the following steps:

  1. Select a source server DSN from the list. The source DSN name automatically appears in the Source Name field above the DSN list. The driver column of the DSN list displays ODBC Driver 17 for SQL Server for SQL Server sources and StarSQL 32 or StarSQL (64-bit) if you are using StarSQL to access Db2 sources. ODBC driver names for Oracle sources vary depending on manufacturer and version. When using a StarSQL or iAccess DSN for access to a Db2 for i source being used for incremental replication, make sure that IsolationLevel=2 (StarSQL) or CommitMode=1 (iAccess) is configured.

  2. When creating a source you can modify the Source Name field to better identify the source database; the source name cannot be changed after it is saved.

The default behavior of the Data Replicator Manager is to limit the display of available ODBC data sources to drivers supported by SQDR. To display all data sources, set the advanced setting addUnsupportedDrivers to true.

  3. Enter the User ID and Password of an account that has permission to obtain data from the selected source server. The Data Replicator enforces the security setting configured for the selected DSN. If the DSN is SQL Server that uses Windows authentication you do not need to enter the user ID and password; you can select the Use Integrated Security radio button to disable the user and password fields.

  4. Click the Advanced tab if you want to modify the connection limits for the source or configure it for incremental replication operations (SQDR Plus is required for incremental replication.)

  5. Click OK to save the source properties and return to the Replicator Manager window.

Replicator Manager displays the new source name in the left pane, under the Sources folder. The Replicator Manager automatically adds a Subscriptions folder below each source. Subscriptions are associated with a particular source, regardless of whether you create the subscription using the Subscription Wizard or as part of a Replication Group.


Configuring a Source for Incremental Replication

SQDR Plus information Configuring a Source for Incremental Replication

If SQDR Plus is installed and configured, you can configure a source in the Replicator Manager that supports defining subscriptions that perform incremental updates to the destination. You also must have an SQDR Plus license configured to perform incremental replication between a particular source and destination.

The default Replication Type for a subscription that uses a source that has an SQDR Plus Capture Agent available is Incremental replication, but you can change the Replication Type to Snapshot to perform only full-refresh replication if desired.

To define a replication source from a host computer associated with an SQDR Plus Capture Agent, click the Advanced tab of the Source Properties dialog.

Advanced properties for a source

If the source is enabled with SQDR Plus, the Allow Incremental Replication from this Source option is available. If you enable the Allow Incremental Replication option, also specify the name of the Capture Agent Schema to use.

Configuring the Source for Change Data Notifications

There are two mechanisms available for the SQDR Plus Capture Agent to inform the SQDR Service when changes have been committed to the source.

  • The SQDR service polls the Capture Agent periodically to find out if there is change data ready. The polling interval is specified in the Advanced Group Properties for incremental subscriptions.

  • You can configure the Capture Agent to send a UDP notification when changes are committed to a subscribed source table. An SQDR source must be configured with a Notification Address to receive change data notifications from the SQDR Plus Capture Agent.

Using the polling interval and notification feature you can balance network activity and database updates as appropriate for the situation. For example, you can configure such a low latency that the target database provides a mirror of the source, receiving changes almost immediately after they are committed.

The drop-down list for Notification Address shows the IP address of all active network connections. Select the IP address, or enter the address or hostname, of the computer that you want to receive the UDP notification when changes are committed to this source. If Network Address Translation (NAT) is being used and the IP address known to the SQDR Plus host is different than the local address, specify the IP address that the host uses to communicate with the client computer. Select Disable if you want to receive change data only with the normal polling cycle. If you disable notifications for a source that is defined in a group that is set to receive notifications, the Receive Change Data Notifications option in the group properties will be toggled to show that notifications have been disabled.

The port that is used for the UDP notification and the frequency for sending it are configured from SQDR Plus. If the computer that is configured to receive the UDP notification has a firewall, be sure it is not blocking the port number that the SQDR Plus host uses to send the notification (the default port is 2728).

See the Source Properties: Advanced tab topic for details about all the advanced options.


Using Connection Strings

See below for Connection String Examples.

A source or destination can be configured using either an ODBC data source or a DSN-less connection string.

To configure a a DSN-less connection string:

  1. Select the ODBC Driver radio button. The source or destination screen will now look like this:

  2. Select an ODBC driver from the list.

  3. The ODBC driver name will appear in the Source or Destination Name field above the DSN list. Be sure to modify this name to clearly identify the database; the name cannot be changed after it is saved.

  4. Enter a valid connection string (maximus 254 characters); see below for examples.

  5. Enter the User ID and Password of an account that has permission to obtain data from the selected server. If you are configuring a connection to a SQL Server that uses Windows authentication you do not need to enter the user ID and password; select the Use Integrated Security radio button to disable the user and password fields, and add Trusted_Connection=Yes to the connection string.

  6. Click the Advanced tab if you want to modify the connection limits, configure AS/400 multi-member access, or configure a Source for incremental replication operations (SQDR Plus is required for incremental replication.)

  7. Click OK to save the properties and return to the Replicator Manager window.

Connection String Examples

The following examples typically show the minimum required keywords to establish a connection; refer to the documentation for the ODBC driver of interest for full information.

In cases where multiple drivers are available for a DBMS type, the recommended driver is typically listed first. However, there are scenarios where an alternate driver may be recommended. Contact StarQuest support and refer to the technical document SQDR: Recommended ODBC Drivers for advice.

  • Db2 (StarSQL)

  • SQL Server & Azure

  • Oracle

  • MySQL/MariaDB

  • Informix

  • PostgreSQL

  • Exasol

  • Netezza

  • Pervasive PSQL

  • Amazon Redshift

  • Salesforce

  • Snowflake

  • Text files

  • Tibero

  • Db2 (IBM drivers)

Db2 (StarSQL)

HostName=myhostip;Server=MYRDB

HostName=myhostip;Port=446;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR

HostName=myhostip;Netlib=SQSSL.DLL;Port=448;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR

See the StarQuest Technical Document Using a DSN-less Connection with StarSQL for additional information.

It is very important to specify IsolationLevel=2 when a Db2 for i source is being used for incremental replication.

SQL Server (Microsoft 17 Driver)

Server=(local) or Server=. or Server=127.0.0.1 (for a local default instance)

Server=.\SQLEXPRESS (for a local named instance)

Server=MYHOST (for a default instance)

Server=MYHOST\SQLEXPRESS (for a named instance)

Server=MYHOST,<port> (for a default instance listening on a port other than 1433)

Server=MYHOST,<port>\SQLEXPRESS (for a named instance if SQL Browser service is not being used)

Server=MYSERVER;Trusted_Connection=Yes;Database=MyDatabase;Encrypt=Yes;

Use Trusted_Connection=Yes to use integrated Windows security.

Use Encrypt=Yes to specify an SSL connection.

Use Encrypt=Yes;TrustServerCertificate=yes to specify an SSL connection without verifying the server certificate. This is useful if the server certificate was issued by a Certificate Authority (CA) unknown to the client machine or the connection specifies an IP address or hostname other than the Common Name defined in the certificate.

Use Database to specify a default database; this can be overridden when creating subscriptions.

If you get a connection failure ("invalid connect string param"), it may be necessary to add a trailing semicolon.

Specify MultiSubnetFailover=Yes when connecting to a SQL Server 2012/2014 availability group listener or Failover Cluster Instance.

SQL Server (SQDR bundled ODBC driver)

HostName=localhost\SQLEXPRESS; EnableQuotedIdentifiers=1

During creation of a subscription using this driver as a destination, select Insert using ODBC rather than the default of Use native-loader function in the Replication Options of the Destination panel.

Azure SQL Database

In the Azure Portal, select the Overview panel for the SQL database.

Select Show database connection strings - this will display the connection string to use with the Microsoft ODBC 17 driver.

Here is an example; specifying the database here is important.

Server=tcp:myserver.database.windows.net,1433;Database=mydatabase;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

To use the SQDR bundled ODBC driver, the connection string will look something like this:

HostName=myserver.database.windows.net; EnableQuotedIdentifiers=1; Database=mydatabase

Go to the Advanced Panel of the source or destination and change Target Schema from the user name (e.g. myID@myaccount) to dbo or the desired schema name.

Oracle (SQDR bundled ODBC driver)

HostName=myhostip;PortNumber=1521;ServiceName=myservice.mydomain.com

HostName=myhostip;PortNumber=1521;SID=MYSID

If you are using a multitenant container database (CDB), use the ServiceName property to specify a connection to the PDB of interest. The ServiceName is a fully qualified name consisting of the PDB name followed by a domain name; use the command lsnrctl status on the Oracle server to list the service name expected by the Listener.

See Oracle SSL Connections for connecting with SSL (Secure Sockets Layer). This is required for Oracle Autonomous databases.

Oracle (Oracle ODBC driver)

DBQ=MYSERVER (where MYSERVER is one of the configured TNS names)

or DBQ=//hostname:port/SID (for connecting without tnsnames.ora)

You can also use an Oracle Internet Directory (OID) entry in place of tnsnames.ora.

If you are using a multitenant container database (CDB), specify a connection to the pluggable database (PDB) of interest with:

DBQ=//hostname:port/ServiceName

Where ServiceName refers to the PDB of interest; see the previous section for a details about ServiceName.

MySQL/MariaDB (MySQL Community Edition ODBC driver)

SERVER=myhostip;PORT=3306;DATABASE=mydb;FOUND_ROWS=1;NO_SSPS=1

This example shows connecting via TLS/SSL to AWS RDS for MySQL, validating the certificate downloaded from AWS:

SERVER=mysql2.xxxx.us-east-1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;FOUND_ROWS=1;NO_SSPS=1;SSLMODE=VERIFY_CA;SSLCA=C:\aws\global-bundle.pem

If fetching large amounts of data from a MySQL source results in memory issues for the SQDR service, add NO_CACHE=1 to the connection string

MySQL (SQDR bundled ODBC driver - MySQL Enterprise only)

HostName=myhostip;PortNumber=3306;Database=mydb

MariaDB/MySQL (MariaDB ODBC Driver)

SERVER=myhostip;PORT=3306;DATABASE=mydb;OPTIONS=2;CHARSET=utf8

SERVER=mysql2.xxxx.use-east1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;OPTIONS=2;CHARSET=utf8;FORCETLS=1;SSLCA=C:\bin\aws\global-bundle.pem

OPTIONS=2 is the equivalent of MySQL FOUND_ROWS=1 - Return matched rows instead of affected rows. This is only needed for destination.

Informix (SQDR bundled ODBC driver)

HostName=myhost;PortNumber=9088;Database=mydb

Informix (IBM Informix driver)

Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y

The use of DELIMIDENT is not required if the server has been configured with DELIMIDENT (e.g. in an environment variable), or Quote SQL Identifiers is disabled in the Subscription Wizard panel of SQDR Service properties

OR (using Setnet32):

Server=myconn;DATABASE=mydb

where myconn is the name of a connection configured with Informix Setnet32:

Hostname=myhost
Protocolname=olsoctcp
Service Name=9088

Informix (IBM Db2 ODBC driver)

HOSTNAME=myhost;PORT=9089;DATABASE=mydb

PostgreSQL (SQDR bundled ODBC driver)

HostName=myhost;Database=mydatabase;PortNumber=5432

This example shows connecting via TLS/SSL to Azure Database for Postgres without validating the certificate:

HostName=mypostgres.postgres.database.azure.com;Database=postgres;PortNumber=5432;EncryptionMethod=1;ValidateServerCertificate=0,

This example shows connecting via TLS/SSL to AWS RDS for Postgres, validating the certificate downloaded from AWS, and requiring TLS 1.2:

HostName=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;PortNumber=5432;EM=1;VSC=1;TrustStore=C:\aws\global-bundle.pem;CPV=TLSV1.2

The following properties are automatically added by SQDR when using a connection string:

FetchTSWTZasTimestamp=1;MaxCharSize=255;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;BatchMechanism=2

PostgreSQL (psqlODBC driver)

Servername=myhost;Database=mydatabase;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364

Servername=mypostgres.postgres.database.azure.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=require

Servername=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=verify-ca;pqopt={sslrootcert=C:\\aws\\global-bundle.pem}

Exasol

EXAHOST=demodb.exasol.com

Netezza

Servername=myhost;Port=5480;Database=MYDATABASE

Pervasive PSQL

DBQ=MYDATABASE;ServerName=myhost.1583;TCPPort=1583

Amazon Redshift (SQDR bundled ODBC driver)

HostName=myhostip;PortNumber=5439;Database=mydb

Salesforce (SQDR bundled ODBC driver)

HostName=login.salesforce.com;TransactionMode=1;SQLEngineMode=2

Note: when connecting to Salesforce, the password field should contain an authentication token appended to the password - PasswordAuthenticationToken. The token is a case-sensitive alphanumeric code that’s tied to your password. Whenever your password is reset, your security token is also reset.

To reset the authentication token: In the Salesforce web interface, go to your personal settings. Enter Reset in the Quick Find box, then select Reset My Security Token. The new security token is sent to the email address in your Salesforce personal settings.

To specify the location of the schema map files created and maintained by the driver, specify SchemaMap or SMP. Note that older documentation (prior to March 2021) incorrectly shows the short name as SM rather than SMP.

HostName=login.salesforce.com;TM=1;SEM=2;SMP=C:\mapfiles\mySFserver.conf

Here is an example using a proxy server listening on port 3128:

HostName=login.salesforce.com;TM=1;SEM=2;PXHN=myproxyserver;PXPT=3128

Snowflake

SERVER=hostname.snowflakecomputing.com;DATABASE=MYDB;WAREHOUSE=MYWH

Text files

Database=C:\mydest;AllowUpdateAndDelete=1
or
Database=C:\mydest
or
Database=L:\Transfer;AllowUpdateAndDelete=1;TableType=Character;Delimiter=|;DataFileExtension=CSV;FirstLineNames=1

For details, see Using the SQDR Text Driver.

Tibero

Server=myhost;Port=8629;Database=mydb

IBM i Access (aka iSeries Access, Client Access)

System=mysystem; CommitMode=1; DefaultLibraries={QGPL,SQDR}

where mysystem is the name of a system that has been configured with System i Navigator.

It is important to specify CommitMode=1 (*CS - read committed) when a Db2 for i source is being used for incremental replication.

Using DefaultLibraries is optional, but simplifies configuration when using SQDR Plus.

System=mysystem;CommitMode=1;DefaultLibraries={QGPL,SQDR};Database=IASP1

where IASP1 is the name of a database name (RDB entry) associated with an Independent Auxiliary Storage Pool (IASP).

IBM Db2 ODBC driver (Db2 Connect)

Hostname=myhostip;port=446;Database=MYRDB;Protocol=TCPIP;Authentication=SERVER
Hostname=myhostip;port=50000;Database=MYRDB;Protocol=TCPIP;Authentication=ENCRYPT

SSL:
Hostname=myhostip;port=50448;Database=MYRDB;Protocol=TCPIP;Authentication=ENCRYPT;
SECURITY=SSL;
Ssl_client_keystoredb=C:\mySSLdir\mykeystore.kdb;
Ssl_client_keystash=C:\mySSLdir\mykeystore.sth

Make sure that the stash file (mykeystore.sth) is readable by the user SYSTEM (or whatever user ID the SQDR service is running as).


Deleting a Source Server

Deleting a Source

When you delete a source, the Replicator Manager removes the selected listing from the diagram in the left pane of the main window and permanently deletes any subscriptions that use the source. If any of the subscriptions for the selected source are also members of a replication group, the subscriptions are also deleted from the group(s). You cannot delete a source if a Replicator Service has an active connection to it.

  1. Select a source from the Sources folder of the Replicator Manager window.

  2. Right-click and select the Delete command.


Working with Destinations

A destination is the database server, database, or table to which the source object (table, view, alias, or synonym) is replicated. Although you must define the source object before you can define a subscription, you do not need to pre-define destinations. The Add Subscription Wizard allows you to create a new destination or modify an existing destination as you define the subscription. Destinations that you specify using the Add Subscription Wizard are automatically added to the Destinations folder.

The destination must be defined as an ODBC data source name (DSN) that uses a driver associated with the database management system that will receive the replicated data. The definition also must include the user ID and password to use when replicating the specified data to the destination system.

Since different replication operations can use different user IDs, a single destination computer may be represented as multiple destinations in Data Replicator, each of which has its own user-supplied destination name and its own combination of user ID and password.

When you select the Destinations folder in the left pane of the main window and have Details selected for the View menu options, the right pane displays the following information for each destination associated with the current Replicator Service:

  • The name Data Replicator uses to identify the destination

  • The ODBC data source name (DSN) associated with the destination

  • The user ID that Data Replicator uses when replicating to this destination. For a SQL Server destination, the user ID displayed is the SQL Server login.

To sort the list of destinations, click any column heading to sort according to the values in that column. Click the same column header again to change between ascending and descending sort order.

When you select a specific destination in the left pane of the main window and have Details selected for the View menu options, the right pane displays the following:

  • The name Data Replicator uses to identify the destination

  • The ODBC Data Source Name (DSN) associated with the destination

  • The database User ID that SQDR uses when replicating to this destination (for SQL Server destinations, this is the SQL Server login)

  • The number of connections to the destination that are currently active

  • The number of connections to the destination that are being held open in the connection pool.

Select a destination in the right pane and right-click to display a context menu from which you can delete the destination (if there are no subscriptions that reference it) or display the destination properties.


Managing multiple SQL Server destinations

Managing Multiple Destinations

You can create snapshot subscriptions in the Data Replicator that replicate data from a single source object to multiple destinations within the same DBMS, such as illustrated below. However, this may cause the data that is replicated to differ for each destination as each subscription reads the source data at a slightly different point in time. This method also concentrates the replication load at the source computer, where computing resources may already be in high demand.

Multiple subscriptions to multiple destinations

A more reliable method of replicating data to multiple tables for the same type of destination is to create a Data Replicator snapshot subscription that replicates the data to one destination, and then use the replication facility within the DBMS to replicate the data to additional destinations. For example, you could use the Data Replicator to replicate data from a Db2 or Oracle source object to a SQL Server destination, and then use the SQL Server replication functions to replicate the data to additional SQL server destinations. This method ensures the same data is replicated to all destinations as they are all replicated from the same snapshot of the source data. This method also concentrates the replication load at the destination, rather than at the source, conserving those host resources for other uses.

Single subscription followed by replication within DBMS


Adding a Destination

Although you must define a source object before you can define a subscription, you do not need to pre-define destinations. The Add Subscription Wizard allows you to create a new destination or modify an existing destination as you define a subscription. Destinations that you specify using the Add Subscription Wizard are automatically added to the Destinations folder.

To directly add a destination, you select the ODBC driver or destination DSN and provide the user account information that has permission to access the destination data.

  1. From the Insert menu, select the Destination command.

The Insert->Destination command displays the Destination Properties dialog.

  2. EITHER Create a destination based on a DSN-less connection string by performing the following steps:

  1. Select the ODBC Driver radio button.

  2. Select an ODBC driver from the list. For instance, select ODBC 17 for SQL Server for SQL Server sources and StarSQL 32 or StarSQL (64-bit) if you are using StarSQL to access Db2 sources.

  3. The ODBC driver name will appear in the Source or Destination Name field above the DSN list. Be sure to modify this name to clearly identify the database; the name cannot be changed after it is saved.

  4. Enter a valid connection string (maximus 254 characters) e.g.

StarSQL: Server=MYRDB;HostName=myhostip;Port=446;IsolationLevel=2;PkgColID=SQDR

SQL Server: Server=MYHOST\SQLEXPRESS

See Using Connection Strings for more examples.

OR

Create a destination based on an ODBC data source by selecting a destination DSN from the list. The destination DSN name automatically appears in the Destination Name field above the DSN list, but you can modify the Destination Name field to better identify the destination database if desired.

The default behavior of the Data Replicator Manager is to limit the display of available ODBC data sources to drivers supported by SQDR. To display all data sources, set the advanced setting addUnsupportedDrivers to true.

  3. Enter the User ID and Password of an account that has permission to access data on the selected destination server. The Data Replicator enforces the security setting configured for the selected DSN. If the DSN is SQL Server that uses Windows authentication you do not need to enter the user ID and password.

  4. Click the Advanced tab if you want to modify the connection limits for the destination.

  5. Click OK to approve the destination properties and return to the Replicator Manager window.

Replicator Manager displays the new destination name in the left pane diagram, under the Destinations folder.


Deleting a Destination

You cannot delete any destination that is referenced by an existing subscription. You must first delete the existing subscriptions that use the destination, or change the existing subscriptions to use a different destination before you can delete the destination. If the destination is not referenced in any subscription you can select it, right-click, and select the Delete command to remove it from the Replicator Manager display.


Creating Subscriptions

A subscription defines the data that is to be replicated from a particular source object to a particular destination. The subscription is managed by the Replicator Service, which is connected to a SQL Server control database.

The Replicator Manager includes an Add Subscription Wizard to help you define subscriptions. You can use the Add Subscription Wizard to define a snapshot subscription, which performs a full refresh of the source data to the destination.

SQDR Plus option If SQDR Plus is installed at the source you also can use the Add Subscription Wizard to define an incremental replication subscription. Incremental subscriptions perform a baseline replication against which changes are tracked, and update the destination data as required to keep it synchronized with the source data. The topic Defining Incremental Replication Subscriptions describes how incremental subscriptions differ from snapshot subscriptions.

If you need to replicate several objects from the same source, you can use the Add Subscription Wizard to create multiple subscriptions at once. The Add Subscription Wizard allows you to create a new group to contain the subscriptions, or add the new subscriptions to an existing group. Subscriptions that perform incremental replication must belong to a group, although the group can have only one member subscription or many subscriptions. Creating a Group of Subscriptions describes the benefits of running subscriptions in a group and the methods for creating a group.

To start the Add Subscription Wizard:

  1. In the Replicator Manager main window, select the appropriate source, or add a new source if the desired source is not already defined.

  2. Select the Subscriptions folder beneath the appropriate source, then right-click and select Insert Subscription.

The Add Subscription Wizard launches to help you define what data you want to replicate. After you save the subscription you can select it and execute the Properties command to set additional properties or change existing properties.


Defining Incremental Replication Subscriptions

SQDR Plus information Defining Incremental Replication Subscriptions

A subscription defines the data that is to be replicated from a particular source object to a particular destination. A snapshot subscription provides full refresh replication, which copies all the source data that you specify for replication, regardless of when that data was last replicated. This provides a "snapshot" of the specified source data at the time of replication.

For sources that are enabled with SQDR Plus you can create subscriptions that perform incremental updates to the destination data. Incremental subscriptions perform a baseline replication against which changes are tracked, and update the destination data as required to keep it synchronized with the source data. Incremental subscriptions have the following requirements and limitations relative to snapshot subscriptions:

  • Each incremental subscription must be a member of a group, and all members of the group must use the same source and destination.

  • No two incremental subscriptions can replicate to the same target table.

  • Foreign constraints are not replicated for incremental operations; however, unique constraints and indexes can be optionally replicated. You also can use a Relative Record Number (RRN) or ROWID column to help ensure there is a unique field to identify change data.

  • You can incrementally replicate a subset of the source columns to a destination, but the subset must include at least one of the fields associated with a unique index to identify the change data. The unique index can be the Relative Record Number (RRN) or ROWID column.

  • If you are replicating from a Db2 for Linux, Windows, and UNIX (Db2 for LUW) source and the source does not have unique indexes defined, only change data from insert operations can be applied to the destination. A new baseline replication must be performed if delete or update operations are committed to the source table.

  • If you are replicating from SQL Server, the source table must have a primary key defined.

  • The maximum row length of tables that can be replicated incrementally is slightly less than the maximum allowed by Db2 for LUW to accommodate the mechanisms that track the change data. SQDR Plus needs a minimum of five columns (more if there are unique indexes) for managing the incremental change data in the staging table, therefore the database table cannot contain the maximum number of columns or the maximum bytes per row that are allowed by Db2 for LUW. LOB data is replicated directly from the source instead of being stored in the change data (staging) tables.


Creating Multiple Subscriptions for Same Source

Creating Multiple Subscriptions for the Same Source

If you need to create a number of subscriptions that use the same source, you can use the Add Subscription Wizard to create the subscriptions all at once. This can provide an efficient method for defining properties that are common to all the subscriptions. The Add Subscription Wizard also provides an alternate method for manipulating the source object list with the Export and Import buttons. You can import the contents of a tab or semicolon-separated text file (with a .txt extension) to populate the Source Objects list of the source selection pane, as described in the Importing and Exporting Source Objects topic.

Be sure to review and understand the following considerations before you create multiple subscriptions at once.

  • All source and destination tables must be regular SQL files, not IBM i (AS/400) multi-member files.

  • All subscriptions created at one time initially share a single destination and a single set of Destination Options, Replication Options, and a single destination Object Owner. For snapshot subscriptions you can change these properties by editing individual subscriptions after they are created. The source and destination cannot be changed for incremental subscriptions.

  • All snapshot subscriptions created at one time initially share a single set of Schedule settings. View the properties of each individual subscription after the subscriptions are created to change each subscription's Schedule settings. (Subscriptions that perform incremental replication can be scheduled only from the Group Properties.)

  • You cannot specify BEFORE and AFTER SQL processing statements or criteria for rows to replicate while creating multiple subscriptions simultaneously, and defaults are used for all destination column settings. However, you can edit each subscription after the group of subscriptions is created to specify replication criteria, columns information, processing statements, and which indexes or constraints you want to replicate.

Use the Shift-Click or Control-Click techniques to select more than one object from the list of source objects, as illustrated in the following graphic. Note that, if you select multiple objects, they are added in the order they appear in the Source Objects list, not the order in which you select them. See Controlling the Run Order of Subscriptions for more information about ordering subscriptions.

selecting multiple source objects

On the Destination screen, you have the option of defining derived columns that are inserted into each subscription. In addition, you can import derived column definitions for multiple tables. See Subscription_Options.

After you select the destination and click Next, a Define Group pane appears so you can create a new group or add the selected source objects as members of an existing group. Objects that are defined for incremental replication must belong to a group. If you create a new group you can specify the properties for it; if you add the source objects to an existing group the existing group properties apply to the new members.

After you respond to the remaining Add Subscription Wizard prompts and click Finish, the Replicator Manager saves the subscriptions, creating the individual subscriptions under the selected source and, if appropriate, adding the subscriptions to the specified group. After you create the subscriptions, modify the properties of individual subscriptions as appropriate for the particular data each will replicate.


Selecting the Subscription Source Object

Subscription Source Properties

You specify which source object you want to replicate when you add a subscription or view the Source tab properties for an existing subscription.

Note that, if you display the properties for an existing subscription and click OK, the subscription is dropped and re-added, which runs a baseline replication. Click Cancel instead of OK to dismiss the properties dialog if you have not made changes to the subscription and do not want to run another baseline.

Replication Type

SQDR Plus option If the source system is enabled with SQDR Plus you can select whether you want the subscription to perform Snapshot or Incremental replications. Without SQDR Plus you can perform only Snapshot replication operations.

You cannot change the Replication Type option after the subscription or a new group has been saved.

Source Filter

The fields in the Source Filter area of the dialog let you shorten the list of object names that are transmitted across the network for display in the Source Objects list. Enable the Allow Mixed-Case Input if you want the Schema and Name that you enter for the filter to be case-sensitive. If the Allow Mixed-Case Input option is disabled (unchecked), which is the default setting, text that is entered for the Schema and Name is forced to uppercase letters for transmittal to the database. Only the source objects that match the names and case specified by the filter are returned to the Source Objects list when the list is populated.

Click the Refresh button to send a request to the source server for a list of object names that match the Database, Object Schema, and Object Name fields. You can enter a complete object schema or use the "%" wildcard character alone or with a partial name string. For a Db2 source, specify either the % wildcard or a full schema or object name.

You cannot change the source database after an incremental replication subscription or group is saved.

Source Objects

After you enter the Object Schema and Name values, click Refresh to display the list of source objects. The Source Objects area lists the objects so you can select which to replicate. The user account that is used to connect to the source may need permission to the object for it to appear in the list.

Supported object types that can appear in the Source Objects list are TABLE, VIEW, ALIAS, and SYNONYM. (Other object types, such as AUXILIARY TABLES, are not currently supported for replication operations.)

Note that you can create multiple subscriptions at once by using the SHIFT-click or CTRL-click techniques to select more than one source object to replicate. Review Creating Multiple Subscriptions from the Same Source to understand the differences in subscriptions that are created at one time.

When you are creating a new subscription you also can use the Export and Import functions to change the list of source objects. After you Export source object names you can manipulate the list with a text editor or spreadsheet application so you can import the modified list. See Importing and Exporting Source Objects for details.

If you change the source object(s) after a subscription has been saved, rebuild the column list to reflect the columns of the new source object(s) before you save the modified subscription.

Select a Member to Replicate

This option appears if the selected source is a Db2 for i database and the AS/400 Multi Member File Access option is enabled for it in the Advanced Source Properties.

To perform incremental replication from a multi-member file, you must enable the multiMember property in the SQDR Plus configuration (default is enabled).

If you select a single file, you can choose either *ALL or an individual member from the Select a Member to Replicate dropdown field.

If you select multiple files, you can choose either *FIRST or *ALL.

If you select a regular SQL table to replicate rather than a multi-member physical file, the Select a Member to Replicate field displays the name of the source table.

When your subscription includes multiple members, the Subscription Summary will show the generated destination table names, which will be of the form FILE_MEMBER.


Exporting and Importing Source Objects

Importing and Exporting Source Objects and Column Definitions

When you are adding a subscription you can use SHIFT-click or CTRL-click selection techniques to refine the list of source objects that you want to replicate. The Add Subscription Wizard also provides an alternate method for manipulating the source object list with the Export and Import buttons on the Select the Source pane. You can import the contents of a tab or semicolon-separated text file (with a .txt extension) to populate the Source Objects list of the source selection pane. Review the topic Creating Multiple Subscriptions for Same Source to understand the implications of creating multiple subscriptions at once.

When you export source object names, they are saved in a tab-delimited text file that contains the following values for each source object:

<schema> <table_name> <object_type>

After you export the source object names you can use a text editor such as Notepad, or spreadsheet application such as Excel, to manipulate the list of source objects that you want to subscribe to. For example, if a database has a very large number of tables and you want to replicate sixty percent of them, you could export the entire list of tables, use search and selection techniques within the text or spreadsheet application to remove the names of the objects you do not want to replicate, and then import the list of source objects that you do want to replicate. This procedure can provide a more efficient method of selecting objects than using the SHIFT-click or CTRL-click method of selecting which objects to replicate.

You can also specify a different name for the destination table; this feature is only applicable if you are creating multiple subscriptions:

<schema> <src_table_name> <object_type> <dest_table_name>

Another modification you can make is to add lines using the following format (where object_type is COLUMN or MAP), which allows the same text file to be used for defining derived columns and custom column mappings as well as importing table names. You can also use the JSON format. See Importing Mapping Scripts.

<schema> <table_name> <object_type> <expression> <destColName> <destColType> <type> <precision> <scale nullable>

When specifying column definitions, you can use the wildcard character (%) for schema and table names. For example:

% % DROP COL1
% % MAP        DESCRIP varchar 1024 0 TRUE

Note that even though the same import file can be used for both purposes (importing a list of tables and importing column definitions), each line in the file is used for only one purpose.

Begin a line with the hyphen character to designate a comment.

Follow the steps below to export the names of selected source objects from the Source Objects list to a tab-delimited text file, modify the file, and import the contents of the modified file into the Source Objects list of the Subscription Wizard.

  1. Start the Add Subscription Wizard if it is not already active.

  2. In the Select the Source pane specify the Source Filter parameters and click the Refresh button to list the objects of that source.

  3. If you want to export all of the source object names, click the Export button with no objects selected. To export a subset of the objects use the SHIFT-click or CTRL-click techniques to select which objects and click Export.

  4. In the Save As dialog select the directory in which you want to save the file, enter a filename, and select the file type. Save the file in ASCII text (.txt). Click Save.

  5. If the filename already exists you are asked whether you want to overwrite it, and a dialog appears to confirm how many source object names were exported. Click OK to proceed.

  6. Modify the list of source objects as desired using a text editor or spreadsheet application and save the file with a new filename.

  7. From the Subscription Wizard - Select the Source pane, click the Import button.

  8. In the Open dialog locate the .txt file that you edited and click Open.

  9. Click OK to acknowledge the dialog that confirms how many names were imported. Note that an entry may be greyed-out if it would result in a duplicate subscription or if the table does not exist on the source or the user does not have authority to know of its existence.

  10. Click Next to continue defining the new subscriptions for the imported source objects.

After you create the subscriptions, modify the properties of individual subscriptions as appropriate for the particular data each will replicate, such as to add SQL processing statements.


Subscription Replication Criteria

By default, Data Replicator replicates all rows from the source table to the target table or stored procedure. When you define a subscription or view the properties of an existing subscription, you can specify a SQL SELECT statement that determine which data in the source table is replicated. The SELECT statement allows you to filter the source data so that only data that meets the criteria is replicated to the destination table.. You also can enter SQL statements to group or reorder rows for a snapshot subscription. See the section below for additional considerations for applying criteria to incremental replication subscriptions.

To replicate only selected source rows, type a valid SQL WHERE clause in the edit box. For example, to replicate only source rows where the ZIP column contains values greater than 89999 and less than 95000, you would enter WHERE ZIP>89999 AND ZIP<95000 in the criteria edit box.

For snapshot subscriptions you can enter SQL clauses using GROUP BY and HAVING in the criteria edit box. For example, to have the destination table display rows grouped by ZIP column value, you would enter GROUP BY ZIP as the criterion.

After you specify the criteria, click the Verify Criteria button to help ensure the SQL statement performs a valid operation. Data Replicator evaluates the criteria and either shows how many source rows would be selected based on that criteria, or displays information if the criteria is not valid.

You cannot specify replication criteria when you create multiple subscriptions at one time, although you can later edit individual subscriptions to add criteria that determine which source data the subscription replicates.

SQDR Plus option Specifying Replication Criteria for Incremental Subscriptions

The criteria you specify for an incremental subscription not only applies to the baseline replication but also to changes that are made to the source table. When the source table changes, the changes are evaluated against the criteria to determine whether to replicate the change to the target table. Evaluating criteria for incremental replication operations is most successful and consistent when the criteria uses only constant values as selection criteria.

The nature of applying changes incrementally imposes additional restrictions for specifying replication criteria.

  • Do not specify GROUP BY, ORDER BY, or HAVING clauses in the criteria for incremental replication operations.

  • The replication criteria cannot be evaluated against LOB values, as LOB values are not stored in the change data tables.

  • Avoid references to other tables. Changes to a table that is used by another table are not automatically applied to the dependent table.

  • Avoid using dynamic system values, such as the current date. This can produce unexpected results, such that records that correspond to the current date are replicated initially, and then only the changes to those records are replicated to the destination.

  • Be aware that the criteria you specify may require Data Replicator to change the type of operation that is sent to the destination table. For example, if the change involves an UPDATE operation to a value that previously did not meet the criteria, Data Replicator sends the operation as an INSERT instead of an UPDATE. If an INSERT meets the criteria and a subsequent UPDATE does not, the UDPATE is changed to DELETE so the value will be deleted from the target table.

Run a new baseline replication to re-evaluate the source table against the criteria if you need to ensure the destination table reflects all the data that meets the criteria.

In some cases (e.g. when the source of an incremental replication is SQL Server. MySQL, or another DBMS that allows mixed or lower case column names), you may need to place double quotes around the column name. e.g. WHERE "ZIP">89999 AND "ZIP"<95000.


Selecting the Subscription Destination

Snapshot Subscription Destination Properties

When you add a new subscription or view the properties for an existing subscription, you specify the destination Data Source Name (DSN) and a table owner who has permission to work with the destination table. The user ID that owns the table must have SELECT, INSERT, UPDATE, and DELETE privileges for the destination table. Depending on the destination options you specify for the subscription and how the destination table is structured, the user account also may need permission to create, drop, and alter tables and indexes.

Destinations

To specify a destination for the replication, select any name in the Destination list or click New to define a new destination. If you create a new destination or select a destination and click Modify, the Destination Properties dialog appears so you can enter or change the properties. If you are adding a member subscription to a group of incremental subscriptions the new member must use the same destination, although you can specify a different database at the destination.

Destination Information

Database. From the Database drop-down list, select the target database for the replicated data. This field is shaded when the information is not required.

Object Schema. Enter the object schema name for the destination that will receive the replicated data.

Object Name. Enter a name for the destination object. If the object name you specify does not already exist, enable the appropriate Destination Option---either Create Object When Subscription Is Saved or Re-Create Object On Each Replication. For information about allowed characters and lengths for table names, consult the documentation for the destination database management system.

If you are creating more than one subscription simultaneously you cannot specify the Object Name. The Add Subscription Wizard assigns a default name to each destination object. After you create the group of subscriptions you can edit the individual subscriptions to modify the destination object name and other subscription properties.

Member Field. If the selected destination is an IBM i (AS/400) computer and the destination properties has the AS/400 Multi Member File Access option enabled, you can select which members to replicate to. In a given subscription, you can replicate to a single member of the destination table file. Use the Member field to specify which member to replicate to.

If you specify an existing destination table in the Object Name field, the Member field displays the members currently existing for that table. You can select an existing member or enter a new member name for StarQuest Data Replicator to create and replicate to.

If you specify a new destination table in the Object Name field, the Data Replicator creates a new multi-member physical file with the object name and member that you specify.

If you do not want to replicate to a multi-member physical file, you can set the value of the Member field to <SQL File Type> to replicate to a regular SQL table. If you are creating multiple subscriptions at once or the source is enabled for Incremental Replication, the destination tables must be regular SQL files and the AS/400 multi-member file option is not available.

Fold Identifiers. This option directs SQDR to preserve the case of the identifiers on the destination in the situation where the preferred case of the destination DBMS is SQL_IC_UPPER or SQL_IC_LOWER. In other situations (destination preferred case is SQL_IC_MIXED or SQL_IC_SENSITIVE), no folding ever occurs. See Fold case of identifiers per destination default for details.

Subscription Options

Enter Derived Column(s)... | Displays the Derived Column dialog, where you may enter any valid derived column value. You can add multiple derived columns by pressing the button more than once. The resulting column definitions are then incorporated into each of the newly created subscriptions.
Import Mapping Script... | You can define derived columns or custom column mappings for one or more tables by importing the contents of a JSON file or a tab or semicolon-delimited text file (with a .txt extension). See Importing Mapping Scripts for details.

Destination DDL Options

The Destination DDL Options are presented in two categories of radio buttons, and you combine an action in the first category with an action in the second category to control how data is replicated to the destination. Some of the Destination and Replication Options are applicable only if the source object is a table and appear dim if the object is a VIEW, ALIAS, or SYNONYM. If the destination is an Db2 for i database, see AS/400 Multi Member Destination Options.

Create Object When Subscription Is Saved | Drops and re-creates the destination object each time the subscription is modified. This allows you to make changes to the structure of the destination object, but should not be used for a table that has referential integrity constraints or if multiple database users need access to the table.
Re-Create Object On Each Replication | Drops the destination object and re-creates it before each full refresh subscription. This option may provide better performance than the Use Existing Table option, but should not be used if the destination table has referential integrity constraints or if multiple database users need access to the table.
Use Existing Table | Deletes all rows in the specified existing destination table before each full refresh replication, but does not drop the table. Use this option if the destination table has referential integrity constraints that you want to preserve between replications, or if special table permissions need to be preserved so that multiple database users have ongoing access to the table.
Create if does not exist, else use existing | Similar to Use Existing Table, except that SQDR will create the table if it does not exist when you save the Subscription.

Replication Options

The Replication Options that you select for a destination also are affected by the commit interval values that are set in the Global tab of the Service Properties.

Truncate Table Before Replication

Removes all rows from a preexisting SQL Server or Oracle destination table before each replication, but preserves the existing table structure and permissions. The Data Replicator uses the SQL TRUNCATE command to discard all the rows at once, without logging deletion information about individual rows. The Truncate Table Before Replication option generally replicates data more quickly than the Delete Existing Data Before Replication option.

You can select Truncate Table Before Replication if the subscription has a SQL Server or Oracle destination and if you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, or if the subscription has a Db2 destination, the Truncate Table Before Replication option is not available.

Delete Existing Data Before Replication

Deletes all rows from the preexisting destination table before each replication, but preserves the existing table structure and permissions. The destination database management software logs the deletion of each row.

You can select this option for any subscription where you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, the Delete Existing Data Before Replication option is disabled.

Append Replicated Rows To Existing Data

Inserts replicated rows at the end of the preexisting destination table, while preserving existing table structure and permissions. Data from previous replications or other database transactions is preserved unless you use destination BEFORE processing to remove it.

You can select this option for any subscription where you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, the Append Replicated Rows To Existing Data option is disabled.

Use Native-Loader Function

Insert Using ODBC

For a replication with a SQL Server or other supported database as a destination, the Use Native-Loader Function and Insert Using ODBC options let you choose whether records will be inserted into the destination table using the Native Loader facility (e.g. SQL Server Bulk Copy) or using ODBC. Native Loader usually is faster than ODBC, but is available only for certain destinations (e.g. SQL Server and Oracle) and does not allow uncommitted data to be rolled back if a replication fails. The Native-Loader option is not applicable if you are replicating a source object that is not of type TABLE.

Manual Synchronization

This option signifies that no data copying or index replication activities will occur when the replication/baseline is run. Depending upon the other destination options selected, the run may still (re)create the destination table and/or delete/truncation destination rows and perform any specified pre-processing steps. This option is primarily used by incremental replication but is available for snapshot replication.

Null synchronization - DDL Only

This option specifies that no data will be written to the target table. This option is primarily used by incremental replication but is available for snapshot replication.


Selecting the Subscription Destination for IR subscriptions

SQDR Plus information Incremental Subscription Destination Properties

When you add a new incremental replication subscription you specify the destination Data Source Name (DSN) and a table owner who has permission to work with the destination table. The user ID that owns the table must have SELECT, INSERT, UPDATE, and DELETE privileges for the destination table. If the subscription replicates indexes the user account also may need permission to create, drop, and alter tables and indexes.

Destinations

To specify a destination for the replication, select any name in the Destination list or click New to define a new destination. If you create a new destination or select a destination and click Modify, the Destination Properties dialog appears so you can enter or change the properties. Note that you cannot change the destination after you save the incremental replication subscription.

Destination Information

Be careful to specify the correct database and table schema and owner as you cannot change the destination information after an incremental replication subscription is saved.

Database. From the Database drop-down list, select the target database for the replicated data.

Object Schema. Enter the table schema name for the destination that will receive the replicated data.

Object Name. Enter a name for the destination table. If the destination table already exists on the destination host you can enable the "Use existing table for baseline" destination option. For information about allowed characters and lengths for table names, consult the documentation for the destination database management system.

If you are creating more than one subscription simultaneously you cannot specify the Object Name. The Add Subscription Wizard assigns a default name to each destination table. After you create the group of subscriptions you can edit the individual subscriptions to modify the destination table name and other subscription properties.

Fold Identifiers. This option directs SQDR to preserve the case of the identifiers on the destination in the situation where the preferred case of the destination DBMS is SQL_IC_UPPER or SQL_IC_LOWER. In other situations (destination preferred case is SQL_IC_MIXED or SQL_IC_SENSITIVE), no folding ever occurs. See Fold case of identifiers per destination default for details.

Subscription Options

Enter Derived Column(s)... | Displays the Derived Column dialog, where you may enter any valid derived column value. You can add multiple derived columns by pressing the button more than once. The resulting column definitions are then incorporated into each of the newly created subscriptions.
Import Mapping Script... | You can define derived columns or custom column mappings for one or more tables by importing the contents of a JSON file or a tab or semicolon-delimited text file (with a .txt extension). See Importing Mapping Scripts for details.

Destination Options

The Destination Options are presented in two categories of radio buttons, and you combine an action in the first category with an action in the second category to control how data is replicated to the destination.

Create every time subscription is Saved | Drops and re-creates the destination table each time the subscription is saved. This allows you to make changes to the structure of the destination table. Since this option drops and re-creates the table, user access to the destination table can be temporarily affected and this option should not be used if uninterrupted access to the table is required.
Create every time baseline is Run | Drops the destination table and re-creates it before each baseline replication is run. This option may provide better performance than the "Use existing table for baseline" option, but should not be used if uninterrupted access to the destination table is required.
Use existing table for baseline | Saves the specified existing destination table prior to running a baseline replication. Use this option if special table permissions need to be preserved so that multiple database users have ongoing access to the table. Note that Automatic ALTER handling will not occur if you use this option.
Create if does not exist, else use existing | Similar to Use Existing Table for Baseline, except that SQDR will create the table if it does not exist when you save the Subscription. Note that Automatic ALTER handling will not occur if you use this option.

Baseline Replication Options

The Baseline Replication Option that you select for a destination also is affected by the commit interval values that are set in the Global tab of the Service Properties.

To avoid ANY modification of the destination table, specify Use existing table for baseline and Append replicated rows to existing data destination options.

Truncate table before replication

This option can be used in conjunction with either the Create every time subscription is Saved or Use existing table for baseline options. The Data Replicator uses the SQL TRUNCATE command to discard all the rows at once before the baseline replication is run. The existing table structure and permissions are preserved, but the rows are discarded without logging deletion information about individual rows. No triggers are run with the truncate operation. If the database stores procedures that need to run when the baseline is run, execute them by specifying Destination Processing for the subscription.

The "Truncate table before replication" option uses significantly less system resources than the "Delete existing data before replication" option. The Truncate option is available only for database hosts that support the SQL TRUNCATE command, such as SQL Server and Oracle.

Delete existing data before replication

This option can be used in conjunction with either the "Create every time baseline is Run" or "Use existing table for baseline" options. The Data Replicator uses the SQL DELETE command to delete all rows from the existing destination table before the baseline replication is run. The existing table structure and permissions are preserved, and the destination database management software logs the deletion of each row. Triggers that are designed to fire during DELETE operations are executed.

Important Information Do not use the "Delete existing data before replication" option with large tables as the logging operations can exhaust system resources and cause the destination database to become unresponsive for an extended period of time.

Append replicated rows to existing data

This option works in conjunction with the other Destination options and the Baseline Replication options.

Use Native-Loader Function

Insert Using ODBC

For a replication with a SQL Server or other supported database as a destination, the Use Native-Loader Function and Insert Using ODBC options let you choose whether records will be inserted into the destination table using the Native Loader facility (e.g. SQL Server Bulk Copy) or using ODBC. Native Loader usually is faster than ODBC, but is available only for certain destinations (e.g. SQL Server and Oracle) and does not allow uncommitted data to be rolled back if a replication fails. The Native-Loader option is not applicable if you are replicating a source object that is not of type TABLE.

Manual Synchronization

This option signifies that no data copying or index replication activities will occur when the replication/baseline is run. Depending upon the other destination options selected, the run may still (re)create the destination table and/or delete/truncation destination rows and perform any specified pre-processing steps (post processing steps are ignored, if present).

It provides an alternative method of utilizing incremental updates, without first using SQDR to establish a synchronized copy, when it may be impractical to use baselines. Care should be taken when using this option to insure that the initial conditions are met to resume incremental replication processing. The initial load of data can be done using backup/restore techniques, SQDR snapshot replication, or other methods.

Considerations: An incremental subscription’s baseline will notify the Capture Agent on the SQDR Plus host that the position of the staging data has been updated to the moment that the subscription has been “run". It is incumbent upon the user to insure that the source and destination tables are in agreement at this time; otherwise change data may be misapplied, leading to “row count” errors.

It is recommend that manual synchronization be undertaken under controlled circumstances, when the source data is not being modified, and no change data is being staged, and the containing group is paused. Upon completion of the manual operation to synchronize the destination table, the subscription must still be “run” to cause the Capture process to commence delivering changes. The containing I/R group may then be “resumed”, causing the subscription to become Active.

Null synchronization - DDL Only

This option specifies that no data will be written to the target table. Use this option if you are using the Change Data Processing feature to call a stored procedure to perform functions other than updating a target table. See Change Data Processing for additional information.

Incremental Apply Options:

See Incremental Subscription Options for an explanation of these options.

The values of the Incremental Apply Options will use the Group Defaults if the "Insert Member" option was selected; otherwise, no Group options are provided as a default. For an existing subscription, the values may be changed. Modifying the "Ignore Deletes" option will involve the subscription being dropped and re-added. Changing any incremental apply option will require a new baseline to be run.


Importing Mapping Scripts

You can define derived columns or custom column mappings for one or more tables by importing the contents of a text file containing JSON or tab or semicolon-delimited directives. The file dialog will (by default) show files with an extension of .json, .txt, or .tsv.

JSON format is supported in SQDR 5.22 & later and permits more complex SQL expressions than the original text format and allows embedding of special characters such as tab and semicolon. It also adds the new column attribute staging_expression for the map and column actions. JSON directives can be intermixed with the original tab or semicolon-delmited text format.

JSON Format

JSON lines are enclosed in brackets and must contain a string element called action which must have a value of one of the following

  • drop: removes a source column from the subscription

  • map: redefines a source column in the subscription. It may just change the destination characteristics (i.e. the destination column name) or combine a "drop" and "column" operation in one line - rendering a source column using a derived column expression (i.e. source_expression and stage_expression)

  • column: adds a derived column to the subscription

  • pcolumn: defines a column as a "partition" rule column - must be a character literal. The value must be a character literal. For more information about using a partition rule column, see the technical document SQDR: Partition Key for Many-to-One Replication.

Required parameters depend on the action specified.

  • schema string: may use wildcard for suffix

  • table string: may use wild card for suffix

  • source_column string: The source column name for drop, map

  • source_expression string: expression for column action. Also may be specified for map if redefining the existing source_column name to a derived column expression; otherwise this should be an empty string.

  • stage_expression string: expression for column action. Also may be specified for map if redefining the existing source_column name to a derived column expression; otherwise this should be an empty string.

  • dest_column string

  • dest_type string

  • precision number

  • scale number

  • nullable bool

  • comment string: optional string element for documentation

Examples

Drop a column named No_Column (in all tables being added):

{ "action": "drop", "schema": "%", "table": "%", "source_column": "No_Column", "comment": "drops a column called "No_Column - if it is present" }

Change the data type of the destination column:

{ "action": "map", "schema": "%","table": "%", "source_column": "ID", "dest_column":"ID", "source_expression":"", "stage_expression":"","dest_type":"bigint", "precision":19, "scale":0, "nullable":false, "comment": "mapped - just changes dest characteristics" }

Change the contents of column DESCRIP to lower case in table STARTRAK.APPS:

{"action": "map", "schema": "STARTRAK","table": "APPS", "source_column": "DESCRIP", "dest_column":"DESCRIP", "source_expression":"lower(\"DESCRIP\")", "stage_expression":"", "dest_type":"nvarchar", "precision":50, "scale":0, "nullable":false, "comment": "mapped to use derived column logic" }

Add a derived column named LastUpdated of data type datetime2, containing the timestamp of when the table was baselined OR when the row was last updated on the source system:

{"action": "column", "schema": "STARTRAK","table": "APPS", "dest_column":"LastUpdated", "source_expression":"@ts", "stage_expression":"","dest_type":"datetime2", "precision":27, "scale":7, "nullable":false, "comment": "column" }

Add a partition rule column named PartitionColumn, containing the literal value 1:

{"action": "pcolumn", "schema": "STARTRAK","table": "APPS", "dest_column":"PartitionColumn", "source_expression":"'1'","dest_type":"char", "precision":1, "comment": "pcolumn - never nullable" }

Additional examples may be found in the topic Defining Derived Columns.

Classic (TAB/semicolon-delimited) Format

Note: This format may be retired in future versions of SQDR.

Derived Columns:

<schema> <table> COLUMN <expression> <dest_column> <dest_type> <precision> <scale> <nullable>

where <expression> is the value for a derived column.

You may use the wild card character % in the <schema> or <table> field to indicate all schemas or all tables; for instance, specifying % % will add the derived column to all subscriptions currently being created.

A derived column definition can be used to override the default column name and data type of the "RRN" column (@RRN for Db2 for i source or @ROWID for Oracle source) used by SQDR; see examples below.

Derived Column as a Partition Key:

<schema> <table> PCOLUMN <expression> <dest_column> <dest_type> <precision> <scale> FALSE

Specify the PCOLUMN keyword when the derived column is to be used as a Partition Key - i.e. it will be used as part of a composite primary key for many-to-one incremental replications. The expression must be a static value, and the nullable field must be FALSE. See the technical document Using a Partition Key for Many-to-One Replication for details.

Custom Column Mappings:

<schema> <table> MAP <source_column> <dest_column> <dest_type> <precision> <scale> <nullable>

where <source_column>is the name of source column that will be mapped to <destColName> using the specified type, precision, scale & nullable values.

Custom Column Mappings:

<schema><table> MAP <source_column>[:[@]<source_expression>] <dest_column> <dest_type> <precision> <scale> <nullable>

Specifying the source_column token with a "colon" character followed by the source derivation value will replace the source column in the subscription with the derived column phrasing (if the source column exists in the source table).

If the derived column expression begins with the special character @, then a simple macro expansion is performed on the source_expression and dest_column replacing any occurrence of ^s with the matched source column name. This is of particular use if wild card matching is being performed on the schema or filename.

If : <source_expression> is omitted, then the normal "MAP" function is performed, changing only the destination characteristics.

If the @ macro designation is omitted, then no substitution of the source expression or destination column name will occur.

Example:

MYSCHEMA MYTABLE MAP DESCRIP:@UPPER(^s) ^sTransformed varchar 50 0 TRUE

Replace the column named DESCRIP with the derived column phrasing, creating a column DESCRIPTransformed varchar(50) NULLABLE

Omit a Column:

<schema> <table> DROP <source_column>

The DROP command will omit a source column from the subscription.

You can also specify the equivalent of a DROP action by using the MAP directive and specifying an empty destination column name; see example below.

Examples:

MYSCHEMA MYTABLE COLUMN MDY(MDYZ) MDYZ_1 date 10 0 TRUE

%;%;COLUMN;@RRN;RelativeRecordNumber;bigint;19;0;FALSE

This creates a derived column named "RelativeRecordNumber" (instead of "RRN") of type of bigint (instead of decimal(15,0)).

MYSCHEMA MYTABLE MAP COLSRC COLDEST date 10 0 TRUE

MYSCHEMA MYTABLE DROP COLSRC

MYSCHEMA;MYTABLE;MAP;COLSRC;;date;10;0;TRUE (this is equivalent to DROP)

The same input table may be used to import a list of table names in the Source dialog, specifying TABLE in the third field instead of COLUMN, MAP or DROP. See Importing and Exporting Source Objects.

Begin a line with the hyphen character to designate a comment.

If a script has been imported on the Destination page, selecting the Rebuild button on the Column page will utilize the script.


Selecting the Group While Adding Subscriptions

Adding Subscriptions to a Group

If you select multiple source objects for snapshot replication, an Add to Group checkbox option allows you to specify an existing group or create a new group of the selected subscriptions. You can just click Next to continue defining the subscription(s) without placing them in a group.

SQDR Plus option If you are creating a subscription for incremental replication of data, the subscription must be a member of a group.

Group Name

You can accept the default group name, select an existing group from the list, or click New to create a new group for the subscription(s) that you are currently adding.

Comment

The Comment field shows the description entered for existing groups, but you can enter or change the comment only from the Group Properties dialog.

Buttons

Click the New button to create a new group for the subscription(s) that you are adding. The New button displays a dialog for you to name the group, and then you can click the Properties button of this Wizard pane if you want to specify the group properties. Otherwise you can edit the properties after the group is created.

Click the Properties button to display the Group Properties dialog. You can specify the properties if you are creating a new group, but you can only view the properties of an existing group. (Existing group properties can be changed by selecting the group and executing the Properties command.)


Subscription Columns Properties

Subscription Column Properties

When you define a subscription that replicates a table (or object treated as a table) or view the Columns tab properties for an existing subscription, you specify which source columns to replicate and the order of destination columns in the destination table. For a new subscription, all source columns are listed, with default destination column settings. An existing subscription displays only the columns defined for the saved subscription. For an existing subscription you can click Rebuild Column List to re-display all the available source columns and reset the destination columns to the default values.

Rebuilding the column list may remove derived columns unless they have been defined using the Import Mapping Script... option on the Destination tab. Using the Merge with Source button will retain existing derived columns, or you can re-import the derived column mapping script prior to rebuilding the column list.

If the structure of the source object(s) is changed in a way that affects the data that is replicated (such as dropping a source column that is subscribed to) you must rebuild the column list to reflect the new structure of the source object(s) before you save the modified subscription.

The Columns properties show:

  • Source Name: the name of the source table column

  • Source Type: the data type for the source column, with the precision and scale shown in parenthesis if they are set.

  • Destination Name: the name of the destination column that corresponds to the source column. Subscriptions that perform incremental replication from a Db2 for i or Oracle source may have an additional, derived Relative Record Number (RRN) or ROWID column to help track source changes that need to be replicated to the destination. The General Group Properties option, Force Use of RRN, determines whether this column is added to the staging table that tracks changes for the subscription.

  • Destination Type: the data type for the destination column, with the precision and scale shown in parenthesis if they are set

  • Nulls: an X appears if the destination column allows null values

The total number of columns that will be replicated, including any derived columns, appears below the column properties.

If you are replicating to and from different types of database systems, be sure to understand how the different DBMS support data types. The topic Mapping Data Types provides more information about how data types are mapped from one DBMS to another. If the source database is SQL Server and the Data Replicator service is running on a system that supports double byte character sets (DBCS), see the topic Service Properties Subscription Tab for how you can increase the precision to better accommodate DBCS data.

The Columns dialog includes several buttons that allow you to perform the following actions.

Button | Action
Add Columns | Displays the Add Columns dialog. Note that, in conformance with standard SQL capabilities, you can add a column that is already designated for replication. This causes the column to appear more than once in the destination table, providing that you specify a unique name for each column in the destination table. Unique column names within a table are required for correct operation.
Delete Column | Deletes the selected column(s) from the list of columns to be replicated. Use Shift-Click or Ctrl-Click to select multiple columns to delete at one time. Do not delete any columns that serve as the key columns (primary key or unique index) for the destination table. You choose which constraints and indexes on the source data that you want to replicate in the Indexes pane.
Modify Column | Displays the Column Properties dialog so you can specify the name and other characteristics of the destination column. Use Shift-Click or Ctrl-Click to select multiple columns to modify at one time.
Insert Derived Column | Displays the Column Properties dialog so you can specify the name and other characteristics of the destination column.
Rebuild Column List | Lists all the source table columns that are available for replication and returns destination column settings to their default values. Rebuilding the column list does not affect derived columns that have been defined. You must rebuild the column list if the source object is altered in a way that affects the subscription, such as if a source column that the subscription replicates is dropped.
Merge with Source | When creating a subscription, especially between unlike database systems, you may have performed extensive column mapping customization. This button displays the Merge Source Schema Results dialog, which allows you to preserve existing customizations if the table on the source system is altered (e.g. columns are added or dropped).

You also can use the Move Selected Column control to change the order in which the replicated columns of a snapshot subscription appear in a new destination table. Select the column that you want to re-order, and then click the up or down arrow buttons to move the column to a different position. The order of columns in the list determines the order of columns for a new destination table if the snapshot subscription destination option is set to create the table when the subscription is saved or to re-create the table when a baseline replication is run. However, if a snapshot or incremental subscription is set to Use Existing Table on the Destination tab of the subscription properties, the existing table structure determines the column order in the destination table, regardless of column position specified in the Columns tab.

Clicking a heading button will sort the list in ascending sequence. Click the same heading again, and the list is resorted in a descending sequence.

Create target unique index using source table RRN

This checkbox is applicable only for Db2 for i and Oracle sources.

If the source table does not contain a primary key or unique index, then the checkbox is automatically enabled and cannot be changed.

If the source table contains a primary key or unique index, then the checkbox is enabled and editable, with its default value being checked if the Force RRN setting in the IR Group is enabled.


Defining Derived Columns

Any subscription that replicates tables (or objects that are treated as tables) can contain derived columns. Each derived column must be based on a SQL expression that is valid for the source DBMS. A valid SQL expression can be a constant, function, or any combination of source column names, constants, and functions connected by operators.

If you are performing incremental replication, the SQL expression must also be valid for the Db2 for LUW staging database; if the expression for the Db2 for LUW staging database differs from that of the DBMS host, enter it in the Staging Expression field. For example, when replicating from an Oracle source database, you might wish to use Oracle-specific derived column expressions that are not standard "SQL92" scalar functions. Using the Staging Expression field to supply the equivalent function for Db2 LUW avoids the need to create User Defined Functions (UDF's) as described in Example 6a below.

For successful replication, a derived column must return the same number of rows as the other columns in the subscription. In practice, this means that a derived column that is based on an aggregate function cannot be used in the same subscription as columns based on individual source rows.

See the tech note Hints for Derived Columns for additional examples and other hints.

The following examples illustrate some of the ways that derived columns can be used. Adding a derived column to the destination data provides flexibility for manipulating the data, such as to:

  • add a column that contains a constant value

  • perform an arithmetic operation on individual rows

  • perform an arithmetic operation on the entire source table

  • perform arithmetic operations on groups of source table rows

  • concatenate text fields

  • convert data to use a different data type (Using User Defined Functions, Db2 Character Data to SQL Server Datetime and Oracle Date to Db2 Date)

  • add columns containing capture metadata for auditing and change detection purposes.

Important Information

Derived column syntax must comply with SQL syntax rules for the source, as well as the Db2 for LUW staging database (if you are performing incremental replication). Different source platforms and software versions may require slight variations in syntax. For more information about specific SQL requirements, consult the documentation for your source DBMS.

The first four examples are based on a source table that contains sales data in four columns: ITEM, PRICE, TAXRATE, and SALESPERSON. The fifth example is based on a source table that contains personnel data including the columns FIRSTNAME and LASTNAME. The sixth example shows how to convert a Db2 character date to a SQL Server datetime data type and back to a character string.

Derived column definitions can be imported from a JSON script, using the Import Mapping Script button on the Destination panel after selecting a destination; see Importing Mapping Scripts. Some of the following examples include the JSON script that implements the described derived column.

Example #1: Constants

Goal: Add a column called Event that contains a constant value.

Expression: 'Spring Sale'
Column Name: EVENT
Data Type: char
Precision: 15

{ "action": "column", "schema": "%","table": "%", "dest_column":"EVENT", "source_expression":"\"Spring Sale\"", "stage_expression":"","dest_type":"char", "precision":7, "scale":0, "nullable":false, "comment": "column" }

Example #2: Arithmetic operation on individual rows

Goal: Add a column called SalesTax where PRICE is multiplied by TAXRATE for each source row.

If an arithmetic expression is applied to a source column containing a null value, the corresponding destination table value may also be null. In the example listed here, a null value in either the PRICE or TAXRATE column would produce a null value in the SalesTax column for the corresponding destination table row.

Expression: PRICE * TAXRATE
Column Name: SalesTax
Data Type: decimal
Precision: 8
Scale: 2

{ "action": "column", "schema": "%","table": "%", "dest_column":"SalesTax", "source_expression":"PRICE * TAXRATE", "stage_expression":"","dest_type":"decimal", "precision":8, "scale":2, "nullable":false, "comment": "column" }

Example #3: Arithmetic operation on entire source table

Goal: Create a table that shows the total sum of all values in the PRICE column of the source table.

To achieve the goal of this example, you must delete all original source columns from the list to be replicated. The values in these columns cannot be replicated to the same table as a sum, because the sum returns only one row.

Expression: SUM(PRICE)
Column Name: TOTAL
Data Type: decimal
Precision: 8
Scale: 2

{ "action": "drop", "schema": "%", "table": "%", "source_column": "PRICE" }
{ "action": "drop", "schema": "%", "table": "%", "source_column": "TAXRATE" }
{ "action": "column", "schema": "%","table": "%", "dest_column":"TOTAL", "source_expression":"SUM(PRICE)", "stage_expression":"","dest_type":"decimal", "precision":8, "scale":2, "nullable":false, "comment": "column" }

Example #4: Arithmetic operation on groups of source table rows

Goal: Create a table that shows subtotals of values in the PRICE column, grouped by SALESPERSON.

To achieve the goal of this example, you must delete all original source columns from the list to be replicated, except the SALESPERSON column by which records will be grouped. The values in the other columns cannot be replicated to the same table as a sum grouped by salesperson, because the sum returns only as many rows as there are salespeople listed in the source table.

Expression: SUM(PRICE)
Column Name: TOTAL
Data Type: decimal
Precision: 8
Scale: 2
Criteria (enter on the Criteria tab): PRICE > 0 GROUP BY SALESPERSON

{ "action": "drop", "schema": "%", "table": "%", "source_column": "PRICE" }
{ "action": "drop", "schema": "%", "table": "%", "source_column": "SALESPERSON" }
{ "action": "column", "schema": "%","table": "%", "dest_column":"TOTAL", "source_expression":"SUM(PRICE)", "stage_expression":"","dest_type":"decimal", "precision":8, "scale":2, "nullable":false, "comment": "column" }

Example #5: Concatenating text fields

Goal: Add a column in which LASTNAME and FIRSTNAME values appear together, separated by a comma and a space.

Expression (for SQL Server source): LASTNAME + ', ' + FIRSTNAME
Expression (for Db2 for i or Oracle source): LASTNAME || ', ' || FIRSTNAME
Column Name: FULLNAME
Data Type: char
Precision: 35

{ "action": "column", "schema": "%","table": "%", "dest_column":"FULLNAME", "source_expression":"LASTNAME || ', ' || FIRSTNAME", "stage_expression":"","dest_type":"char", "precision":35, "scale":0, "nullable":false, "comment": "column" }

Example #6a: Using User Defined Functions

You can use User Defined Functions (UDF's) to convert data between different DBMS types. If you are using incremental replication, the UDF must be defined in both the source database and the local Db2 for LUW staging database. More information about creating and using UDF's will appear in a future technical document; contact StarQuest Support for assistance.

SQDR Plus 4.50 and later provides several UDF's for several common Db2 for i date conversion requirements where the source column is defined as decimal (numeric), zoned, or packed, and contains various forms of dates.

The following functions are provided:

  • MDY(MMDDCCYY) - where MMDDCCYY is the name of a DECIMAL(8,0) column containing a date expressed as MMDDCCYY, where MM is month, DD is day of month, CC is century and YY is year within century.

  • YMD(CCYYMMDD) - where CCYYMMDD is the name of a DECIMAL(8,0) column containing a date expressed as CCYYMMDD, where CC is century; YY is year within century, MM is month and DD is day of month.

  • CYMD(CC,YY,MM,DD) - where the date is stored in 4 separate DECIMAL(2,0) columns on the Db2 for i source.

These functions return a result of data type SQL_DATE. NULL input results in a result of NULL. A value of zero is mapped to '1901-01-01'.

Example: MDYZ is the name of a DECIMAL(8,0) column containing a date value defined as a zoned field (a DDS definition of 8S); the resulting destination will contain a derived column named MDYZ_1 of type date.

Expression: MDY(MDYZ)
Column Name: MDYZ_1
Data Type: date
Precision: 10
Scale: 0
Nullable: TRUE

If the SQDR Plus Staging Agent for the Db2 for i source was created prior to installing SQDR Plus 4.50, contact StarQuest Support for assistance in creating the UDF's in the local Db2 for LUW staging database. StarQuest Support can also provide assistance for creating the UDF's in the Db2 for i source database if you are not using incremental replication.

Example #6b: Converting Db2 Character Data to SQL Server Datetime

Goal: Convert a Db2 character data type date field to the SQL Server datetime data format, and back to the character data type on Db2. The source Db2 character string does not conform to the SQL Server datetime data format, but can be converted by using expressions to create a derived column on the target with the data formatted according to the desired data type.

In the SQDR subscription that replicates a char(8) data type from Db2 for i to a SQL Server datetime format, add a derived column that consists of the following expression. The Db2 timestamp function converts the char field to a timestamp and appends a time value so the column can be replicated to a SQL Server datetime field.

Expression: TIMESTAMP (<column_name> || '000000')
Data Type: datetime

To replicate the SQL Server datetime column back to the Db2 char(8) formatted column, use the SQL Server CONVERT function to add the data as a derived column to the Db2 target.

Expression: CONVERT (char(8), <column_name>, <date_style_value>)

The <date_style_value> determines the date format to use when converting the datetime data to character data. For example, specifying 112 converts it to the ISO standard format of yymmdd. Refer to the SQL Server documentation for a complete list of the style values and information about the CONVERT function.

Example #6c: Converting Oracle Date to Db2 Date

Goal: Replace an existing Date column with a derived column that uses an Oracle TO_CHAR function to convert the Date field to a Char field. The properly formatted Char field can be then replicated to a Db2 Date field.

Select the existing Date column listed in the Columns pane of the subscription properties, highlight the Date column and click Delete Column. Click the Insert Derived Column button and set the column properties as follows:

Expression: TO_CHAR (<original_date_column_name>, 'YYYY-MM-DD')
Column Name: use the same name as the original column name
Data Type: DATE

If your Db2 system uses a different DATE format than what is shown in the example, you can change the TO_CHAR function to conform to the desired format. Refer to the Oracle documentation for more date formatting options.

Capture Metadata

The following macros can be used to insert SQDR Plus capture metadata into a derived column in the destination table when the @ character occupies the first position of the derived column string. A space in front of the @ character acts as an escape character and disables value substitution.

Macro

Data Type

Notes

@TX

BINARY(10)

The SQDR Plus-generated hex-binary transaction id for the change record. Note that this is not the transaction id from the source system.

@TS

TIMESTAMP

The time on the source system when the change was made (or NULL if no changes have occurred to the row). Use DATETIME or DATETIME2 for SQL Server Destination.

@ts

TIMESTAMP

The time on the source system when the table was baselined OR when the row was last updated.

@LT

TIMESTAMP

Load Time - the time on the source system when the subscription was run i.e. when the table was baselined

@lt

TIMESTAMP

The time when a row was first inserted into the destination table, either as a result of a baseline OR an INSERT during incremental Apply.

@tz

DECIMAL(6,0)

Source system timezone offset (HHMMSS)

@TYPE

CHAR(1)

Also known as Operation.

I/D/U indicates Insert/Delete/Update source table operations.

S indicates that the subscription needs to be rebaselined. This can be an indication of a REORG at the source or that there is a disruption of the SQDR Plus Capture Agent’s log reading position.

X indicates that the source table has been dropped.

A indicates that the source table has been altered.

B may be generated for subscriptions with “criteria” (i.e. a where clause). This additional change data row sent for each update contains the values of all the columns prior to the update. This option is not available for some source databases, such as Db2 for i for with only *AFTER journaling or SQL Server sources.

C indicates that all change records collected during a baseline copy have been processed. This record can refer to baselines for other groups or even other agents subscribing to the same table.

@SEQ

BIGINT

An internal sequence number that is used to order staged change data when applied to the destination. It represents the relative order in which the update occurs in the source log within a given transaction. For a given source log, it is also the sequential order in which the operation was processed within the log.

@RRN

BIGINT (Db2 for i source)
CHAR(18) (Oracle source)

Relative Record Number (Db2 for i)
ROWID (Oracle)
See ROWID_RRN_settings in Advanced Settings for information about properties that allow you to customize the default definitions of derived columns based on ROWID and RRN.

@JED

@JED('element')

VARCHAR(254)

Journal Entry Detail: configure collectLogDetail=true on the SQDR Plus staging agent. See below for details. Db2 for i source only.

@SCHEMA

VARCHAR(128) Nullable

Source Schema

@OBJECT

VARCHAR(128) Nullable

Source Object

@MBR

CHAR(20)

Db2 for i member name. Typically null, or the same name as the table.

@DSN

VARCHAR(128) Nullable

Source name of the subscription

@null

Defines the value of NULL as the derived column expression. May be used for either the source and/or the staging derived column expression.

@skip

Provide means of defining a column on a destination table (the column will appear in the CREATE TABLE statement), without replicating a value during Apply. The initial value will be NULL. This is different than the @null expression which supplies the value NULL for the data element during incremental replication. @skip leaves the value unchanged for purposes of Apply. This allows the end user to manage the contents of the column on the destination independently of the replication process.

Note that the data types are guidelines and may vary depending on the destination DBMS.

Be sure to set the Nulls checkbox (allow the destination column to be NULLABLE) in the Column Properties dialog, as the initial value when the baseline is run will be NULL. The data will be updated after an Insert or a Delete.

@JED (Journal Entry Detail)

The Journal Entry Detail is an XML structure containing multiple elements. @JED returns the entire XML structure. Use the syntax @JED('element') to obtain the value of a single element. The destination type should be large enough to accept the maximum expected value and should be nullable, since the value is undefined for a baseline. For example, @JED('journal') can be defined as VARCHAR(20) NULLABLE. Some elements such as @JED('rrn') or @JED('seqno') may be mapped to bigint.

@JED('enttype') returns a default value of "RR" after the table has been baselined.

IBM CDC columns | equivalent SQDR macro | source type | precision
Commit Cycle ID (&CCID) | @JED('ccid') | VARCHAR | 20
Entry Type (&ENTTYP) | @JED('enttyp') | CHAR | 2
Entry Type Code (&CODE) | @JED('code') | VARCHAR | 2
Journal Name (&JOURNAL) | @JED('journal') | VARCHAR | 20
Journal Sequence Number (&SEQNO) | @JED('seqno') | VARCHAR | 20
Record Modification User (&USER) | @JED('user') | VARCHAR | 10
Source Job Name (&JOB) | @JED('job') | VARCHAR | 10
Source Job Number (&JOBNO) | @JED('jobno') | VARCHAR | 6
Source Job User (&JOBUSR) | @JED('jobuser') | VARCHAR | 10
Source Program Name (&PROGRAM) | @JED('program') | VARCHAR | 10
Source RRN (&CNTRRN) | @JED('rrn') | VARCHAR | 20


Synchronzing Column Mapping

Synchronizing Column Mapping

When creating a subscription, especially between unlike database systems, you may have performed extensive column mapping customization. The Merge with Source feature preserves existing customizations if the table on the source system is altered (e.g. columns are added or dropped).

Here is an example of the Columns dialog before modification:

Press the Merge with Source button; the following dialog is displayed:

Note:

  • The columns that may be added can be individually deselected, or the check box may be unchecked.

  • The Altered columns are only those that have source column differences.

  • The Columns to be dropped are those that were originally in the subscription but are no longer part of the source schema.

  • The text message "RRN column differences..." only appears when this special column (which is not part of the source schema, but may be affected by source schema INDEX changes) has been altered.

If you press cancel, no action is taken.

Below are the results after clicking "OK". In our example, we now have 18 columns, where previously 14 were defined. Five new columns were added, and one existing column was dropped.

Also, note that the destination column names that start with "m_" have been preserved during the refresh operation, so you do not have to re-enter customizations for existing columns.

If the user presses "Merge with Source" button and no changes are detected, then the following dialog is presented:


Creating the Destination Object

When you view the properties for an existing subscription, the Create Statement tab shows the SQL statement that is executed at the destination to create the target object.

If the target object is a table the Data Replicator automatically forms the CREATE TABLE statement and you cannot modify it. If the target object is a view, alias, or synonym. you may be able to modify the CREATE statement.

An alias is a qualified name that can be used by any authorization ID. A synonym is an unqualified name that can be used only by the authorization ID that created it.

If the CREATE statement is dynamically prepared on a Db2 destination, users with SYSADMIN authority can create synonyms for other users by changing the value of the CURRENT SQLID special register before the CREATE SYNONYM statement is issued.


Subscription Processing Properties

The Processing properties for a subscription are SQL statements that automatically alter data in the source or destination databases, either immediately before or immediately after replication. Use the fields in the Source Processing frame to modify source data. Use the fields in the Destination Processing frame to modify destination data.

Source Processing | Destination Processing
Type one or more SQL statements in the source BEFORE edit field to alter the source table data just before each replication associated with the current subscription. | Type one or more SQL statements in the destination BEFORE edit field to alter the destination table data just before each replication associated with the current subscription.
Type one or more SQL statements in the source AFTER edit field to alter the source table data just after each replication associated with the current subscription. | Type one or more SQL statements in the destination AFTER edit field to alter the destination table data just after each replication associated with the current subscription.

Follow these guidelines when you enter the SQL statements in each processing frame:

  • To enter multiple SQL statements, type a semicolon (;) after each statement.

  • Use fully qualified table names. If you enter unqualified table names, the Data Replicator attempts to use the default database and owner.

  • Press ENTER within any of the edit fields to type on a new line.

You cannot specify SQL processing statements when you create multiple subscriptions at one time, although you can later edit individual subscriptions to add SQL processing statements.

SQDR Plus option If you specify for processing to occur for an incremental subscription, the source and/or target data is processed only for the baseline replication.


Replicating Indexes and Constraints

If the source data has indexes or constraints (primary key or reference constraints), you can choose whether to replicate those to the destination.

SQDR Plus option For a subscription that performs incremental updates there must be a field that uniquely identifies change data. This can be indexes or primary keys and, if the host database is Db2 for i or Oracle, a Relative Record Number (RRN) index that SQDR Plus adds to the staging tables. The indexes are created only when the baseline replication occurs. If the indexes change at the source the baseline must be re-synchronized. If the subscription is for a Db2 for i or Oracle source table you can replicate the RRN or ROWID to help ensure there is a unique field that identifies change data. Using the RRN can help overcome problems caused when a unique field on the source is no longer unique on the target due to differences in how different database systems support data types, such as timestamp.

The Indexes tab of the subscription properties lists any primary or foreign keys or constraints that are placed on the source data. Click Refresh if you need to re-display all the constraints and indexes that exist for the source object. If you are creating or editing a subscription that performs snapshot replication, click the checkbox in the Replicate column next to the constraints or indexes you want to replicate to the destination. If you are creating or editing a subscription that performs incremental replication you must replicate at least one column that uniquely identifies the change data.

Important Information

If the subscription replicates constraints, be sure that the column(s) that serve as the key columns for primary key(s) or unique indexes are included in the Columns properties. If you remove a key column from the destination image but choose to replicate a primary key that uses the column, running the subscription will generate an error when it attempts to create the primary key.

If the indexes or constraints for the source data change, click the Refresh button on the Indexes tab of the subscription properties to refresh the list of indexes and constraints and modify the subscription accordingly. For example, if you change the source table for a subscription, refresh the indexes and constraints and rebuild the columns list to ensure the subscription replicates the correct data.

Note that changing the group property options Replicate All Indexes or Replicate All Constraints also changes the selections on the subscription's Indexes tab. Whichever selections are made last to the indexes properties are the choices that will be in effect when baseline replication of the subscription is run, individually or as a member of a group. You cannot disable the option for replicating constraints for a group of incremental subscriptions.


Subscription Schedule tab

Scheduling a Subscription (Snapshot) or Group of Subscriptions (Snapshot or Incremental)

The Schedule properties tab allows you to schedule a snapshot subscription or group (incremental or snapshot) to run automatically, such as to run the subscription or group on a specific date or on a regular basis. If a subscription is a member of a group you can schedule the group to run independently of the individual subscription. Be careful that the schedules do not overlap or the subscription will be busy when it is triggered by the other schedule.

Important Information

For subscriptions to run automatically, the appropriate network connections must be functioning at the scheduled time, and the Replicator Service must be running and be able to access the control database and the source and destination database.

Select the Scheduled checkbox to enable the schedule. If you de-select the Scheduled checkbox, the subscription or group will not run automatically until the Scheduled option is again enabled. Subscriptions and groups can always be run on demand using the Run Subscription or Run Group commands, regardless of whether you define a schedule for automatically running the subscription or the group.

Select the One Time option, and enter the date and time for replication in the On Date and At Time edit fields, to run the subscription or group once at the specified time. Select the Recurring option to run the subscription or group on a regular basis, such as nightly, daily, or weekly. Click Change to display the Task Schedule dialog to define or modify the schedule for running the subscription or group.

If you are creating multiple snapshot subscriptions from the same source, the same replication schedule is applied to all the subscriptions you are creating. After you create the subscriptions you can edit individual subscriptions to modify the schedule for each.

If you modify an existing schedule, and do not wish the subscription or group to run immediately after saving your change (for instance, you have a subscription or group that should be run only at night or on weekends), be sure to modify the start date of the schedule.


Naming and Saving a Subscription

The final Add Subscription Wizard screen prompts you to name and save the new subscription. Each subscription name for a particular source must be unique within the Replication Manager. The dialog shows any subscription names that have already been defined for that source so you can avoid entering a duplicate name.

To use extended characters in subscription names, you must perform a procedure on the SQL Server before you create a subscription, as described in Using SQDR with Extended Characters.

After you enter a unique name, click Finish to save the subscription.

If necessary, click Back to view earlier Add Subscription Wizard screens and make modifications before you save the subscription.

Run each snapshot replication subscription after you create it and inspect the destination table to verify that the subscription works correctly. The Data Replicator validates many of the subscription properties when you save the subscription, but some characteristics (such as authority over destination tables) must be tested by running the subscription.

SQDR Plus option Incremental replication subscriptions require a baseline replication to establish a point after which changes to the source are tracked. The initial baseline replication can occur when the incremental replication subscription is saved, or at the time specified for the group schedule of which it is a member.

Important Information

Run the initial baseline replication for an incremental subscription when the subscription is saved or soon after. This helps avoid problems that would occur if the source table structure is modified, such as adding or deleting columns or indexes or altering primary keys, between the time the subscription is defined and the time the baseline replication is performed.


Saving Multiple Subscriptions

When you create multiple subscriptions that use the same source, the last pane of the Add Subscription Wizard shows the subscription names, source objects, source type, and the destination object name and object type that will be created. Click Finish to save the subscriptions. The Data Replicator Manager validates the subscriptions, displays a dialog that describes any invalid subscription properties, and then displays a Multiple Subscriptions dialog that shows the subscriptions that were successfully created and any that were not created.

After you create a group of snapshot subscriptions, review the properties for each subscription to help ensure it will replicate the intended data at the desired time. When you edit each subscription you can change properties that cannot be specified while you are creating a group of subscriptions, such as the destination table name that was assigned, adding criteria for which data is replicated, adding SQL statements to process the data before or after replication, selecting whether to replicate any indexes or constraints, and a specific schedule for running the subscription automatically.

Run each subscription and inspect the destination table to verify that the subscription works correctly. The Data Replicator validates many of the subscription properties when you save the subscription, but some characteristics (such as authority over destination tables) must be tested by running the subscription.

Important Information

SQDR Plus option If you are creating multiple subscriptions that perform incremental replication, run the initial baseline replication for the subscriptions when they are saved or soon after. This helps avoid problems that would occur if a source table structure is modified, such as adding or deleting columns or indexes or altering primary keys, between the time a subscription is defined and the time the baseline replication is performed.


Creating a Group

Creating a Group of Subscriptions

You can realize significant performance improvement by grouping subscriptions that perform snapshot replication. Subscriptions that perform incremental replication must belong to a group.

Grouping subscriptions provides for high speed replication because they can be run concurrently to take advantage of multi-threading. With multi-threading the Data Replicator can continue fetching data even if another subscription in the group is idle while it waits for an operation to complete.

Groups provide a way to organize existing subscriptions so you can run all the replication operations at once. If the subscriptions in the group are dependent on one another, such that each should not run until the prior subscription completes, you can set the group concurrency level to 1 so they run one at a time. If you want all of the subscriptions in the group to complete as quickly as possible, increase the group concurrency setting to take advantage of multi-threading the operations. See Controlling the Run Order of Subscriptions for more information about the order in which subscriptions run within a group.

Each subscription remains associated with the source under which it is defined, but snapshot subscriptions can be referenced from one or more groups. A subscription that performs incremental replication can belong to only one group, and all the subscriptions in the group must use the same source and destination.

There are two ways to create a group: 1) you can create a group and then add subscriptions to it, or 2) you can create a new group as you are using the Add Subscription Wizard to define subscriptions. After a group is created you can add one or more subscriptions to it either as you are defining a new subscription or by selecting the group and adding an existing subscription to it.

Use the Insert Group command or the Insert Incremental Group command to create a group so you can then add existing subscriptions as members.

  1. Select the Groups folder, right-click, and select the Insert Group or Insert Incremental Group command, as appropriate. (SQDR Plus is required to define a group that performs incremental replication.)

  2. Specify the General group properties, naming the group and choosing whether to replicate indexes and/or constraints and the desired level of error recovery and concurrency.

  3. For a group of snapshot subscriptions, click the Member Subscriptions tab. From the Available Subscriptions pane select the subscriptions you want to add to the group and click the > arrow button, or click the >> button to add all the available subscriptions to this group.

    For a group of incremental subscriptions, click the Advanced tab and select the source and destination you want to use. After the group is created, select the Members folder, right-click, and execute the Insert Member command. The Add Subscription Wizard appears so you can select the specific object(s) to replicate from the source defined for this group.


Group Properties General tab

General Properties for a Group

In the Group Properties dialog you identify the group with a name and comment and indicate how you want to handle indexes, constraints, and errors for all member subscriptions in the group. The properties that you can set depend on whether you are editing the properties of an existing group or creating a new group.

Group Name

If you are creating a new group using the Insert Group or Insert Incremental Group commands, enter up to 35 characters for the group name. After a group has been saved you must use the Rename command if you want to change the name of the group. If you are creating a new group from the Add Subscription Wizard you name the group in the New Group Name dialog and it cannot be changed in the Group Properties dialog.

Indexes/Constraints Options

If the source data for a snapshot subscription has indexes or constraints (primary or foreign key or reference constraints), you can choose whether to replicate those to the destination.

The Indexes/Constraints options allow you to specify whether you want to replicate all indexes and/or all constraints for all the subscriptions that are members of the group. Enabling the group setting overrides the settings for replicating specific indexes and constraints that are specified on the Indexes tab of the properties for each subscription.

For example, if the Indexes tab in the properties for a subscription is set to replicate any of the available indexes and constraints and you include that subscription as a member of a group that does not replicate all indexes or constraints, the indexes and constraints that are set to replicate for the subscription are automatically disabled. Likewise, if the subscription properties do not specify to replicate any of the indexes and constraints and you include the subscription as a member of a group that has the Replicate All Indexes and/or Replicate All Constraints options enabled, all the available indexes or constraints, respective to which group option is enabled, are automatically replicated.

Any of the changes to the indexes and constraints settings that are automatically applied to the member subscription as a result of changing the group property affect the properties of that subscription anywhere it appears, whether as an individual subscription under a specific source or as a member of another snapshot replication group.

SQDR Plus option For a subscription that performs incremental updates, you can choose whether to replicate indexes to the destination. The indexes are created only when the baseline replication occurs. If the indexes change at the source, the baseline must be re-synchronized. You cannot replicate foreign constraints, but you have an option of requiring SQDR to use a Relative Record Number or ROWID as a unique field for tracking change data from a Db2 for i or Oracle host. Enable the Force Use of RRN option to have all new subscriptions in the group use a RRN to help ensure the integrity of tables that do not have unique indexes or have columns that contain timestamps or other data types that do not map precisely from the source DBMS to the target DBMS. Setting the Force Use of RRN option adds a RRN column only for new subscriptions; it has no affect on existing subscriptions in the group.

The checkbox for Allow disable/enable indexes is related to the disableIndices service property (an advanced property that is accessed using the SQDR Properties application) but applies only to a specific group rather than the entire service.

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.

Error Recovery Options

The error recovery options allow you to control how the group of subscriptions runs if there are errors or warnings encountered. A warning occurs if there is any truncation of data during a replication. An error indicates a problem that prevents replication of the data. All errors and warnings are reflected in the replication statistics regardless of the error recovery option that is set for the group. The error recovery option only determines whether the group of subscriptions continues to run or whether the replication process is interrupted if a problem is encountered.

If you set the Error Recovery to Ignore Errors and Warnings, Data Replicator attempts to run every member subscription in the group even if an error or warning occurs. Selecting Halt on Errors Only stops the group replication with the subscription that encountered the problem, preventing any subsequent member subscriptions from running. Likewise, the Halt on Errors and Warnings setting prevents any subsequent member subscriptions from running if an error or a warning is encountered during replication of a subscription in the group.

If you set the group property to halt a group of snapshot replications if there is an error, or an error or warning, review the replication statistics to determine the error or warning that was encountered. After you correct the problem that caused the error or warning, you can select the member of the group that caused the replications to halt and then select the Run Group from this Point command to resume replicating the group of subscriptions. If a group of incremental subscriptions is halted due to an error, correct the error and then execute the Run Group command to run a new baseline replication for any subscriptions in the group that require it.

Maximum Retries (default 0) indicates the number of times running a group (typically a scheduled run) will retry a failing subscription.

Allow Checkpoint/Restart: Typically checkpoint restart has been used only for IR subscriptions. Setting this checkbox 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). See also the service property defaultAllowCheckpointRestart .

Concurrency Option

The value you specify for Maximum Subscriptions determines how many member subscriptions in the group can run at the same time. A value of 1 runs one subscription at a time, in succession, according to the Run Order shown for the group. The default value is to run a maximum of 5 subscriptions concurrently.

If the subscriptions in a group run concurrently, the order in which they complete depends on the length of time each subscription takes to run. For example, if there are 10 subscriptions in a group that has a Concurrency value of 3 and the first subscription takes hours to complete, the other subscriptions in the group may complete before the first subscription if they take only minutes to complete.


Group Properties Advanced tab

SQDR Plus information Advanced Tab for Groups of Incremental Subscriptions

The Advanced tab of the properties for a group of incremental subscriptions shows the source and destination objects. If you are adding a new group you can select which source and destination object to use. For an existing group of incremental subscriptions you cannot change the source or destination objects.

The Database and Schema for the source and destination are also displayed. The Database field will be blank if not applicable for the DBMS type e.g. Db2 for i or Oracle.

Polling

For the Polling Interval specify a numeric value and a frequency (Seconds, Minutes, or Hours) to define how often the Data Replicator should poll for changes at the source. The default polling interval is 3 minutes.

The Error Retry limit specifies how many consecutive errors can occur when polling the source before the Replicator Service pauses the group. Specify 0 (zero) for the limit if you want polling to continue regardless of how many errors are encountered.

The option Receive Change Data Notifications from Capture Agent has been removed from this dialog in SQDR 5.10 & later; this feature is now configured only on the Advanced Dialog of the source (see Source Properties: Advanced tab).

Apply

The Apply section contains settings related to applying changes to the target database in an incremental replication.

  • DDL Replication, ADD, DROP and MODIFY control how SQDR handles ALTER changes to the source tables. DDL Replication determines when the subscription is run in reaction to a source ALTER, while ADD, DROP and MODIFY (ALTER COLUMN) determine what action is taken. The default values are defined in the Replication tab of SQDR Service Properties. See ALTER Processing for details.

  • Commitment Limits: See Specifying Commit Limits for Incremental Apply for details about commitment limits (Row Limit and Transaction Limit).

  • Apply DML in Batches: Selecting this checkbox (default) instructs SQDR to apply by destination table order for a given set of transactions. If you need to support Foreign Key relationships on the destination tables (generally not recommended), deselect this checkbox to enable Sequenced Apply for scenarios where the original sequence of DML is required.

    NOTE: Some sources such as SQL Server do not support sequencing regardless of this setting.

  • Automatic Snapshot check box specifies whether or not to run a baseline when needed. This replaces the choice of As Needed or On Demand on the Schedule page in SQDR prior to v5.10.

Change Data Logging

The Change Data Logging section allows you to configure the values used by Apply processing and to specify the name of a stored procedure to process Change Data information instead of, or in addition to, updating a destination table. The stored procedure can reside either in the SQDR control database or the destination database. See Change Data Processing for details.

Logging Level: choose the desired value from the dropdown field:

*DISABLED (default)
Data-Always
Errors-Only
Keys-Always

Select the location of the stored procedure to invoke: Control DBMS or Destination DBMS

Stored Procedure: Enter the name of the stored procedure to be invoked. You may need to qualify the stored procedure e.g. MYSCHEMA.MYPROC. The stored procedure is invoked with the same credentials as used by SQDR to access the control database or destination e.g. sqdr for a Db2 for LUW control database, sa for a SQL Server control database (in this case, you will typically qualify the stored procedure name with dbo), or the userID configured in the Destination for a destination DBMS.

Parameters: Selecting this button will bring up a dialog where you can enter or paste the parameters to be supplied to the stored procedure. For instance, if you are using the SQDR Kafka Producer (TOKAFKA), you would enter the properties for the connection and (optionally) the Topic, with each property on a separate line.

Encoding: choose the desired value from the dropdown field:

*DEFAULT - use the useXMLEncoding service property
JSON
XML

Note: if you configured local.encoding (XML or JSON) in the Comment field of the group in versions of SQDR prior to 5.10 (e.g. when configuring the Kafka Producer), that value will be used to set the Encoding parameter when you update to 5.10, after which local.encoding in the Comment field will be ignored.

Before Image included determines how the Before Image information is delivered:

  • *Default: Use the value defined in the Change Data Logging Defaults section of the Replication tab of the SQDR Service properties.

  • Separately (behavior of SQDR prior to v5.08): the Before Image is a separately logged row.

  • Combined: the Before Image is appended to the After Image data.

Note: to deliver a Before Image, the subscription must be configured as Archive all DML Activity or must include a criteria (even if the criteria is defined as 1=1, which delivers all rows).

Before Image is available from the following source DBMS platforms

  • Db2 for i (for tables journaled as *BOTH)

  • Oracle

  • SQL Server (CDC - Change Data Capture - only)

  • Db2 for LUW

  • MySQL

Subscription Wizard Apply Defaults

The Subscription Wizard Apply Defaults control the default value of certain advanced settings on the destination dialog for newly created subscriptions; see I/R Subscription Options for details.


ALTER Processing

The Apply section of the Advanced dialog for properties of an incremental group contains the settings DDL Replication, ADD, DROP, and MODIFY that control how SQDR handles ALTER changes to the source tables. DDL Replication determines when the subscription is run in reaction to a source ALTER, while ADD, DROP and MODIFY determine what action is taken for the destination table. The default values are defined in the Replication tab of SQDR Service Properties.

Note that these settings determine the action that is taken on the destination table; modifications to the intermediate Db2 LUW staging tables used by SQDR Plus are handled automatically.

DDL Replication: choose the desired value from the dropdown field. This field determines whether to perform DDL replication of source table ALTER statements such as ADD or DROP columns automatically or when the I/R group or subscription is run.

*DEFAULT - use the SQDR Service property (Replication tab) for DDL Replication
On Demand - the user must select the group or subscription and select RUN to apply the DDL change
Automatic - automatically perform the DDL change
Off - disables "DDL Replication" functionality. The behavior is equivalent to SQDR prior to 5.10 (see below for details).

ADD: choose the desired value from the dropdown field. This field determines how to react to an ALTER TABLE ADD operation of the source table.

*DEFAULT - use the SQDR Service property (Replication tab) for ADD.
Ignore - ignore the Column Add, leaving the destination table unchanged with respect to the newly added source column
Perform - Adds the column in the destination table (with null attribute)

DROP: choose the desired value from the dropdown field. This field determines whether to perform. This field determines how to react to an ALTER TABLE DROP operation on the source table.

*DEFAULT - use the SQDR Service property (Replication tab) for DROP.
Ignore - Preserves existing destination column data, and alters the column to permit null values if originally created as Non-Nullable.
Perform - Drops the column in the destination table

MODIFY: choose the desired value from the dropdown field. This field determines how to react to an ALTER TABLE ALTER COLUMN (or ALTER TABLE MODIFY) operation on the source table that results in a change of data type, data type name, precision, scale, or nullability.

*DEFAULT - use the SQDR Service property (Replication tab) for MODIFY.
Ignore - ignore the ALTER COLUMN operation, leaving the destination table unchanged. Replication may encounter issues such as truncation errors.
Perform - Modifies the column in the destination table

No action occurs if you drop or modify a column that is not being replicated.

In SQDR prior to 5.10, an ALTER to a source table would cause a subscription to be flagged as needing a baseline; to recover, the user needed to view the subscription properties and select Merge with Source on the Columns tab, and then Run the subscription or group.

In SQDR 5.10 & later, if DDL Replication is set to On Demand, then the subscription is flagged as needing a baseline, but it is only necessary to Run the subscription or group to perform the ADD, DROP or MODIFY actions as configured. However, you have the option of inspecting and possibly modifying the changes to be made by viewing the subscription properties and selecting Merge with Source on the Columns tab before running the subscription or group.

If DDL Replication is set to Automatic, then the ADD, DROP or MODIFY actions will occur automatically for supported DBMS platforms.

Limitations

Automatic ALTER handling with continuous replication is supported from the following source DBMS platforms

  • Db2 for i

  • Oracle

  • Db2 for LUW

  • MySQL

For other source DBMS platforms (SQL Server and Informix), DDL changes require a new baseline, which is handled automatically if Automatic Snapshot is enabled on the Advanced panel of the Incremental Group.

Automatic ALTER handling is limited to:

  • ALTER TABLE ADD - add a column

  • ALTER TABLE DROP - drop a column

  • ALTER TABLE ALTER COLUMN (ALTER TABLE MODIFY) for changes in data type, data type name, precision, scale, or nullability.

Changes to keys (primary key, foreign key, unique constraint, etc) will result in a flagged subscription (pre-5.10 behavior).

Keywords that specify column position e.g. ADD COLUMN BEFORE (Db2 for i), ADD COLUMN FIRST/AFTER (MySQL) are not supported.

Automatic ALTER handling does not occur for subscriptions where the destination table is configured as Use existing table for baseline or Create if does not exist, else use existing.

It is possible that some destination DBMS platforms may not support every ALTER operation, and manual intervention may be required.

DBMS-specific information for ALTER processing on the destination:

  • Db2 for i: To enable ALTER DROP processing on Db2 for i, issue the following command. If the default message is not set to Ignore, then the DROP COLUMN will fail with the error "Processing of the SQL statement ended. Reason code 10.". Use the WRKRPYLE (Work with System Reply List Entries) command to check whether this command has been issued.

ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY(I)

NOTE: This setting is not needed for IBM i 7.4 TR1 and IBM i 7.3 TR7 & later.

  • Db2 for LUW and Db2 z/OS: When SQDR performs an ALTER for a Db2 LUW or Db2 z/OS destination, it will performs a REORG on the destination table. This operation will appear in the statistics information. If you are performing cascaded replication, this will have an affect on downstream replications.

  • The behavior of ALTER may vary based on the destination type.

For example, an ALTER COLUMN SET NOT NULL operation on the source will have no affect on a Db2 z/OS destination; the replication statistics will show a 'Successful' status, but no DDL operation is performed on the destination:

DDL replicated successfully, with elapsed time 00:00:00. 0 ALTER TABLE statement(s) generated for destination.

This is because Db2 z/OS will not allow an existing column to be set to NON-NULL, so SQDR is not attempting the change, and instead relies upon the source database to enforce the non-null characteristic.

Other Considerations:

  • Any new columns added with ALTER TABLE ADD will be nullable, as SQDR has no knowledge of what default value to use for existing rows.

  • Be aware of the default mapping settings defined on the Service Properties Subscription (Wizard) Tab, as these settings will affect the mapping choices made by SQDR when it processes ALTER ADD or MODIFY actions. Customizations made in the Subscription wizard or mappings configured prior to modifying the default mapping settings will be lost. SQDR will configure mappings for the new or modified columns using the default mappings as if it were creating a new subscription.


Specifying Commit Limits for Incremental Apply

SQDR Plus information Specifying Commit Limits for Incremental Apply

The Apply section of the Advanced dialog for properties of an incremental group allows you to control the commitment limits (Row Limit and Transaction Limit) when applying changes to the target database in an incremental replication.

Background: Incremental changes for incremental groups are returned as a consequence of calling the stored procedure “GetChanges” on the source system. GetChanges is designed to dynamically return multiple result sets per invocation, where each result set corresponds to the staged changes for a given table in the group. The maximum number of result sets that may be returned in a single invocation is determined by the source system. For i5/OS, the maximum is set to 24.

To increase the efficiency of the change data delivery, multiple source transactions may be grouped together within a given table’s result set. The SQDR Service “applies” these transactions in the destination database as a single transaction. This approach reduces the overhead of obtaining changes from the source system but may end up transferring a very large number of rows for a given table, and potentially extending the time associated with completing any given transaction on the source system.

Transaction buffering may also result in a large amount of log space being used by the destination system. SQDR provides a mechanism for the user to manage this performance trade-off. Under the “Apply” section of the Incremental Group Advanced Properties tab, there are two parameters to constrain the amount of buffering that may occur.

The Transaction Limit specifies the maximum number of source transactions that may be buffered. A value of 0 (the default) denotes an unlimited number of source transactions may be aggregated into a single destination transaction. A value of 1 specifies that a source transaction will be applied as a single destination transaction (i.e. no transaction buffering).

Since an unlimited number of transactions may result in a very large use of the destination database log resources, a second governing mechanism is also provided – the Row Limit (Commit Level) which works in conjunction with the Transaction Limit.

The Row Limit specifies a threshold of the total number of rows to be returned for a given invocation of GetChanges. Because at least one transaction must always be allowed, regardless of the number of source rows involved, the Row Limit value is honored on a “best-try” basis. The default value is 20,000; specifying “0” means “no row limit”.

It is recommended that the defaults be used unless specific application requirements dictate otherwise (i.e. single transactions on the destination or a limited amount of log space.)


I/R Subscription Defaults

Incremental Subscription Options

The following Subscription Options are specified on the Advanced property page of an incremental replication group, as well as on the destination page for an incremental subscription. These options are mutually-exclusive.

The values on the Advanced property page of an incremental replication group are default values used when creating new subscriptions. Changing a group default has no effect upon existing subscriptions. The defaults are unchecked for all the options when a new incremental group is created.

NOTE: When copying a subscription from one group to another incremental group, the user may either elect to use the "To Group" defaults or the "From Subscription"-specific values for the newly copied subscription(s). The default behavior is to copy the existing subscription's values.

Strict Apply Rules
(formerly Normal)

Normal handling. Any failure to successfully apply a DML operation will be flagged.

Allow UPSERT
(formerly Use Unique Constraints)

This option causes incremental subscriptions to copy the unique constraints on the source to the destination as unique constraints. When disabled (the default) Unique Indexes are mapped to Non-unique indexes and other constraints (Primary Keys, Foreign Constraints) are not copied to the destination.

When this option is enabled, an update that cannot be performed will be attempted as an insert. Inserts that fail will be attempted as an Update. Deletes that fail to update one row will be silently ignored.

Archive all DML Activity

This option results in the creation of an incremental subscription that treats all insert/updates/deletes as insert operations; i.e. just add the row with the "after" image to the destination table. All DML activity is rendered as an "insert" to the destination table, providing the complete history of the DML operations.

In other respects the subscription's Apply functions are the same; an Altered or Missing notification from the Capture Agent will flag the subscription. Truncate is ignored, since it is essentially a mass delete. Special criteria opcodes are not handled. No destination index is created since all operations only result in rows being inserted (i.e. no update or deletes using a where clause). You may want to include derived columns for Capture Metadata such as @TS (timestamp) and @JED (Journal Entry details).

Note that in the case of deletes, all columns will be null, except the "before" key and any derived column expressions that do not depend upon image data (for example, journal entry details or other SQDR defined macros).

Ignore Deletes

This is passed as a configuration option to the SQDR Plus Capture Agent, and requests that the SQDR Plus agent NOT deliver "DELETE" operations for the Apply processing on the destination table. Changes that are associated with INSERT or UPDATE operations continue to be delivered for application on the destination table. This option is intended for users who wish to treat the destination as an "archive" in addition to maintaining current values. This scenario is common in Data Warehouse environments: for example if the source is a 24-month view of Sales Activity, the destination may be designed to maintain a superset of current data and historical data (e.g. five years of history). This option permits the purging of rows on the destination to be independent of the current activity. See also the advanced setting archiveIgnoreDeletes.

Decompose Update

This option instructs SQDR to replace Update operations with Delete+Insert. This may provide better performance when the destination uses column store indexes or otherwise does not handle updates efficiently.

Requirements & considerations:

  • A complete after image must be available.

  • This option only modifies the behavior when using the Strict Apply Rules or Allow UPSERT destination option; it has no effect on the Archive or Only Stream Change Data apply options.

  • The row count in the statistics will be twice the expected number, as each row update operation results in one Delete and one Insert operation.

Only Stream Change Data
(formerly Change Data processing only)

This option instructs SQDR Apply to perform all steps of processing the Change Data with the exception of updating the target table; select this option if you are using the Change Data Processing feature to call a stored procedure to perform functions other than updating a target table. Only the group stored procedure will be invoked; the destination table will not be modified by Apply. When using this option, SQDR processing will include a Before Image only if a criteria is specified for the subscription; you can specify a criteria such as 1=1. See Change Data Processing for additional information.

Soft Delete
(formerly Treat Delete as Update)

If this option is enabled, deleting a row on the source does not delete the corresponding row at the destination. Instead, the destination row is updated with the values of any derived columns (e.g. columns using macros such as @ts; @LT; @ls). In particular, a derived column defined with “@TYPE” macro is updated with the value of ‘D’ to indicate deletion. The values of non-derived columns in the destination row will not be updated. This technique can be used to preserve a history of the deleted rows. if a row is subsequently inserted in the destination with the same row identifier (i.e. primary key value), then the corresponding destination row is updated with all new column data from the Insert image.

If the destination contains the “@TYPE” column AND the “DELETE” baseline option is specified for the destination in the subscription, then running a subsequent baseline will not delete the rows with a value of ‘D’ from the destination. If the “TRUNCATE” baseline option is specified for the destination, then all rows (including the rows with a ‘D' value) are dropped when a baseline is run.

Db2 for i source only: Alternatively, for a Db2 for i agent with collectLogDetail=true defined, a derived column defined with the “@JED(‘enttyp’)” macro is updated with the value of ‘DL’ to indicate deletion, and baseline behavior is the same as rows containing a 'D' value.


Incremental Group Schedule

SQDR Plus information Incremental Group Schedule Dialog (deprecated)

In SQDR 5.10 and laler, the Schedule Dialog is identical for both snapshot and incremental groups. For details, see Scheduling a Snapshot Subscription or Group of Subscriptions.

The following functions that were specific to the Schedule Dialog for incremental groups have been changed:

  • The choice for running the group As Needed or On Demand has been replaced with the Automatic Snapshot check box on the Advanced Tab for Groups of Incremental Subscriptions.


Adding Incremental Subscriptions to a Group

Follow the steps below if you need to add a subscription that performs incremental replication to an existing group of incremental replication subscriptions. The new subscription must use the same source and destination as the other subscriptions in the group.

  1. Review the General Group Properties to ensure that the options that affect new subscriptions are set as desired. The Replicate Indexes and Force Use of RRN group properties determine whether indexes and constraints are replicated, and whether a RRN column is added to the staging table for the subscription. The Force Use of RRN option is applicable only if the source database is Db2 for i or Oracle.

  2. Select the Members folder of the group to which you want to add a subscription.

  3. Right-click and select the Insert Member command. The Add Subscription Wizard appears so you can define the subscription you want to add.

  4. In the Select the Source pane of the Add Subscription Wizard, specify the appropriate source filter and click Refresh to display the source objects.

  5. Specify the appropriate database information in the Select the Destination pane. The new subscription must use the same DSN as the other subscriptions in the group, but you can specify the destination database information for this subscription.

  6. Complete the Add Subscription Wizard, specifying the columns and selecting any indexes that you want to replicate.

After you save the subscription a dialog appears so you can run a baseline replication if desired. A baseline replication must be run before the Replicator Service can begin tracking changes to the source data for incrementally updating the destination.


Scheduling Snapshot Groups

The Schedule tab of the Group Properties dialog allows you to schedule a group of snapshot subscriptions to run automatically, such as to run the subscription(s) on a specific date or on a regular basis.

Important Information

For a group of subscriptions to run automatically, the appropriate network connections must be functioning at the scheduled time, and the Replicator Service must be able to access the control database and the source and destination database.

Select the Scheduled checkbox to enable the schedule. If you de-select the Scheduled checkbox, the group will not run automatically until the Scheduled option is again enabled. Groups can always be run on demand using the Run Group command, regardless of whether you define a schedule for automatically running the group.

For a group of snapshot subscriptions, select the One Time option, and enter the date and time for replication in the On Date and At Time edit fields, to run the group once at the specified time. Select the Recurring option to run the group on a regular basis, such as nightly, daily, or weekly. Click Change to display the Task Schedule dialog to define or modify the schedule for running the subscription or group.

If a snapshot subscription is a member of a group you can schedule the group to run independently of the individual subscription. Be careful that the schedules do not overlap or the subscription will be busy when it is triggered by the group schedule.


Group Props: Adding Member Subscriptions

Adding Subscriptions to a Group

The Member Subscriptions tab of the Group Properties shows the subscriptions that are a member of this group in the right pane. For a snapshot group, the left pane shows all the available subscriptions.

For more info about incremental groups, see Adding Incremental Subscriptions to a Group.

adding, removing, and ordering member subscriptions

Snapshot only: Select an available subscription and click the > button to add it to the Member Subscriptions list. Click the >> button to add all the available subscriptions to the group. The subscriptions are identified with a name of source_name/object_name. To remove a member subscription from the group, select it and click the < button. Click the << button to remove all the member subscriptions from the group.

When the group is run, the Member Subscriptions are executed in the order they appear in the Member Subscriptions pane, beginning with the member at the top of the list. Select a member and drag it up or down to change the order in which the group members will be run. The Run Order is shown when you select the Members folder of the group and view the right pane with the Details option enabled.


Running Subscriptions

Running Subscriptions and Groups

The Run Subscription and Run Group commands start a replication operation for the selected subscription or group, respectively. You can execute the Run Subscription command for any subscription that is not already running, and the Run Group command for any group that is not already running. Select a subscription or a group and then select the Run Subscription or Run Group command from the File menu or right-click to display the context menu.

SQDR Plus option Note that, after you define an incremental replication subscription and the baseline replication completes, typically there is no need for you to explicitly run the subscription or group again. Running an incremental group creates a new baseline replication for any subscription that is flagged with errors. Periodic polling errors may occur, such as when the SQDR Plus host is unavailable due to periodic maintenance, but usually are resolved by the next polling cycle and do not require creating a new baseline for the incremental subscription(s). Run a new baseline only when there are severe errors that prevent the change data from being applied to the target, such as if the schema is changed and the source table is dropped and re-created.

You also can schedule a snapshot subscription or group to run at a specific time and date or on a recurring basis, or schedule baseline replications for a group of incremental subscriptions. If a subscription is scheduled to run automatically and the schedule is triggered when the subscription is being run on demand or by a group, the scheduled replication generates an error to the event log.

The icon for a snapshot subscription that is not running appears as a page idle snapshot subscription, to which a green up arrow is added running snapshot subscription when the subscription is running. A gray up arrow snapshot subscription running elsewhere indicates the subscription is running elsewhere, such as within a group if you are looking at the list of subscriptions under a particular source.

SQDR Plus option The icons for subscriptions that perform incremental replication differ from snapshot subscriptions. After the initial baseline replication is finished the subscription is monitoring for changes at the source and appears with a green circle active incremental subscription to indicate it is active. There may be a period of time that the page and group icon appear with a black center in the green circle, such as synchronizing the baseline, until SQDR confirms that the baseline is synchronized with any changes that occurred while the baseline was being performed. If a problem occurs that affects the incremental replication operation the subscription icon changes to show a warning condition incremental subscription with warning condition or an error condition incremental subscription with error condition, depending on the severity of the problem. Warning conditions often are corrected over time with no user intervention. Error conditions may need to be corrected by an administrator. For example, if an error is encountered because the source table was dropped or altered, the subscription properties must be reviewed and refreshed to reflect the changes to the source before a new baseline replication can be performed and incremental changes can flow again.


Managing Connections

The Connections folder shows information about all the connections that the Replicator Service is managing. There are always at least two connections allocated for the Replicator Service to communicate with its control database. Other connections are made to source and destination databases when replications are in progress. Connections also may appear because they are being held open in a connection pool.

When you select a Connections folder in the left pane of the main Data Replicator Manager window and select Details from the View menu, the right pane displays the following information for each connection associated with the current Replicator Service:

  • the name of the source or destination that is using the connection, or ControlDB if the connection is used by the Replicator Service to communicate with its control database

  • Connection ID: a unique identifier for each connection

  • Type: the connection type - ControlDB, Source, Destination, Staging, Apply

  • Names: the name of the source or destination participating in the connection, or ControlDB if the connection is for the Replicator Service to communicate with its control database

  • Description: the name of the group or subscription currently using the connection and the name of the operation being performed

  • Status: the current status of the connection - In Use (the listed connection is currently active), Pooled (the connection is being held open in a connection pool), Closing, Connecting, Expired, Zombie or Canceling

  • Dwell Time: the elapsed time that the connection has been in the displayed state

Select Refresh from the View menu (or click the Refresh button button on the toolbar, or press F5) to update the connection information. If you are troubleshooting a problem it may be useful to open another instance of the StarQuest Data Replicator Manager so you can view the Connections information from one session and the replication operations and statistics from the other session.

You can delete a connection by selecting it, right-clicking, and selecting the Delete Connection command. Deleting a connection closes it. If the connection is pooled, it closes immediately. If the connection is in use, the Data Replicator prompts you to confirm that you first want to cancel the replication so it can close the connection.

SQDR Plus information If you have Incremental subscriptions defined and need to upgrade the SQDR Plus software or perform maintenance on the host computer, you can avoid temporary errors if you pause the incremental group and delete the connection while SQDR Plus is unavailable.

WARNING

If you cancel a replication by deleting the connection it is using for the operation, the destination table may not contain the information that was present before the replication started. Any data that it does contain may be incomplete and unreliable. Carefully review the destination data and, if necessary, re-run the subscription that was in progress when you cancelled the replication.

You can limit the number of connections that are allowed to a particular Data Replicator control database, to a particular source, or to a particular destination by editing the Properties for the service, source, or destination, respectively.

See Also

Managing Control Database Connections
Advanced Destination Properties Dialog (destination connections)
Advanced Source Properties Dialog (source connections)
Control DB Tab (service connections)


Group Details

Group Information Details

When you select a Groups folder in the left pane of the main window and click Details on the View menu, the right pane displays the following information for each group:

  • Name: the name that Data Replicator uses to identify the group

  • Error Recover: the type of Error Recovery that is set for the group

  • Max Concurrent Threads: the maximum number of subscriptions that can run concurrently

  • Replicate Indexes: whether indexes for all members of the group will be replicated

  • Replication Constraints: whether constraints for all members of the group will be replicated

  • Description: the comment entered to describe the group

  • Last Status: the status and timestamp for the last run of the group

When you select a specific group in the left pane of the main window and have Details selected for the View menu option, the right pane displays a row of data for each recent run of that group.

  • Start Time: when the group was started

  • Elapsed Time: how much time elapsed while the group ran

  • Rows: the number of rows replicated if the group replicates tables

  • Bytes: the number of bytes replicated if the group replicates tables

  • Throughput: the average number of bytes replicated per second

  • Status: the overall status of the group, which represents the most severe status for any subscription in the group

Click any column heading to sort according to the values in that column. Click the same column heading again if you want to change between ascending and descending sort order.

To see additional information about the replication statistics, double-click the event, or execute the View Replication Event command. You may need to review the status of each member in the group to determine which subscription has a warning or error if the overall group status indicates a problem. You can execute the Run Group command to re-run a baseline replication for any subscription that is in a warning ( Incremental subscription with warnings) or error ( Incremental subscription with error) state. If the schedule for the group is set to As Needed, SQDR will automatically re-run a baseline replication for any member subscription that is stopped due to an error ( Incremental subscription with error).

When you select the Members folder of a group and have the Details view enabled, the right pane lists the member subscriptions with the following information:

  • Name: the name of the member subscription

  • Run Order: the order in which the subscriptions will start (see Controlling the Run Order of Subscriptions for more information)

  • Source Table: the name of the source table from which the subscription obtains the data to replicate

  • Destination: the name of the destination DSN that receives the replicated data

  • Destination Table: the name of the destination table that receives the replicated data

  • Last Status: the status of the last replication operation for the subscription

  • Last Timestamp: the date and time that the last replication operation completed


Subscription Details

Subscription Information Details

When you select a Subscriptions folder in the left pane of the main window and have Details selected for the View menu option, the list of subscriptions appears in the right pane with the following information for each subscription:

  • Name: the name that Data Replicator uses to identify the subscription

  • Source Table: the name of the source table from which the subscription obtains the data to replicate

  • Destination: the name of the destination DSN that receives the replicated data

  • Destination Table: the name of the destination table that receives the replicated data

To sort the list of subscriptions, click any column heading to sort according to the values in that column. Click the same column heading again to change between ascending and descending sort order. Double-click a subscription to display its properties.

When you select a specific subscription in the left pane of the main window, either from the Subscriptions folder of a Source or the Members folder of a Group, and have Details selected for the View menu option, the right pane displays a row of data for each recent replication operation.

The detail for each replication event shows:

  • whether the replication succeeded replication succeeded, failed replication failed, or has warnings Subscription succeeded with info

  • Start Time: when the replication event started

  • Elapsed Time: how much time elapsed while the subscription ran

  • Rows: the number of rows replicated if the source object is a table

  • Bytes: the number of bytes replicated if the source object is a table

  • Throughput: the average number of bytes replicated per second

  • Status: status of the replication operation. Subscriptions have a status of None only when they have no statistics, such as when they have been added to a group but have not yet been run or statistics have been cleared (see Deleting Statistics).

Click any column heading to sort according to the values in that column. Click the same column heading again if you want to change between ascending and descending sort order.

SQDR Plus information Informational messages for incremental subscriptions and groups also appear with history for the replication statistics. These history statistics, noted with a pencil icon such as history icon, show status information such as whether the group is actively polling for changes or has been paused. See Icon Legend for a complete list of icons used in the Replicator Manager.

To see additional information about any replication event, double-click the event, or execute the View Replication Event command.


Replication Event Information dialog box

Replication Statistics Information

For each replication event in the right pane of the main window, you can execute the View Replication Event command to display a dialog with the following replication statistics:

  • The start time for the replication

  • The elapsed time for the replication (how long the replication ran)

  • The throughput (average number of bytes replicated per second)

  • The number of rows replicated if the object is a table

  • The number of bytes replicated if the object is a table

  • Remark that indicates whether the replication was successful

  • Detailed message about the replication. If errors were encountered, the error message text is preceded with the name of the component that returned the error, which can help you troubleshoot the problem. For example, a message such as "[Microsoft][ODBC SQL Server Driver] Numeric value out of range" indicates that SQL Server could not process the replication because it detected an invalid numeric value in the data you are trying to replicate.

Click Next or Previous to display statistics for other replication events that occurred from the same subscription. The order in which events have their statistics displayed depends on the current sort order for viewing subscription details.

SQDR Plus information Informational messages for incremental subscriptions and groups also appear with history for the replication statistics. These history statistics, noted with a pencil icon such as history icon, show status information such as whether the group is actively polling for changes or has been paused. See Icon Legend for a complete list of icons used in the Replicator Manager.

See Also

Deleting Statistics


Viewing statistics

Viewing Subscription Statistics

The Data Replicator Service maintains statistics about each subscription that it manages. Each time the subscription is run, individually or as a member of a group, an entry is logged to record the event statistics. The statistics that appear for an individual subscription show events for every time the subscription is run, whether it is run standalone or as a member of a group. The statistics that appear for a subscription that is a member of a group show only the events that are related to replication of that group.

Subscriptions have a status of None only when they have no statistics, such as when they have been added to a group but have not yet been run or statistics have been cleared (see Deleting Statistics). The Data Replicator may clear older statistics automatically according to the properties configured for the Logging tab of the Replicator Service.

To view the statistics for a subscription:

  1. In the left pane of the Replicator Manager main window, select a subscription whose statistics you want to view.
    Select the subscription from the Sources folder to see all replication statistics for that subscription. If the subscription also is a member of a group, selecting the subscription from the Members folder shows only the statistics that result from running the subscription within that group.

  2. For more information about any replication event displayed in the right pane, double-click the icon or list item to display the Replication Statistics dialog. See Icon Legend for a description of the icons used in the Replicator Manager.


Filtering the Subscription List

You can click any column heading of the subscription list to sort the list according to that heading. This allows you to group the subscriptions according to values such as the Run Order or Last Status.

You also can filter the subscription list to show only the subscriptions that have the selected status values. For example, if you want to see only the subscriptions that have warnings or failed to replicate, you can enable the Warnings and Failure options in the Filtered by Status pane.

To filter the list of subscriptions by status:

  1. Select a Members folder under the list of groups.

  2. Click the Filter button of the Toolbar ( Filter button) or select the Filter Items command from the View menu. Press the Shift key as you enable the Filter Items button or command if you want the Filter pane to appear at the top of the subscription list.

  3. In the Filtered by Status area of the pane, click to toggle each status option on or off to show only subscriptions that have the status values that are checked. Subscriptions have a status of None only when they have no statistics, such as when they have been added to a group but have not yet been run or statistics have been cleared (see Deleting Statistics).

To filter subscriptions by name of Subscription, Source Table, Destination Table, or Destination, click the appropriate button in the Filtered by Names area of the pane. An additional dialog will appear, allowing you to enter the desired value. For instance, this dialog appears when you click the Subscription... button:

The Filter pane appears as long as the subscription list is being filtered. (You can vertically resize the pane to adjust the space available for the subscription list.) To turn off filtering and show the entire list of subscriptions, click the Filter button or execute the Filter Items command a second time.


Setting Up Email Notifications

The Data Replicator provides a Notification feature to help keep an administrator informed of the status of subscriptions. The notifications are sent to an email address to indicate the status of groups of subscriptions (although a group can contain only one subscription, in which case the notification would indicate the status of that subscription). An error or warning from a group indicates that one or more of the subscriptions in the group encountered an error or a warning. You also can have notifications sent for successful completion of a group or when an incremental group encounters errors polling the source for changes, depending on which types of messages are selected in the Notification properties.

For example, if the notification message type is Errors and Warnings the SQDR Service will send an email notification to the specified recipient if any group contains a subscription that encounters an error or warning. The administrator can then use the Replicator Manager to further investigate the problem and correct it so the replication operation can complete successfully.


Controlling the Run Order of Subscriptions

When you click on the Members folder of a group and have the Details view shown in the right pane, a Run Order column shows the order in which the subscriptions will run when the group is run. The Run Order reflects when the order in which the subscriptions will start. The order in which the subscriptions complete may differ depending on the Concurrency group property setting and the length of time that it takes each subscription to run. If it is important that the subscriptions in a group complete in the order of the Run Order you can set the Concurrency to 1 so that one subscription must complete before the next one starts.

You can re-order a group of snapshot subscriptions to change the order in which the subscriptions are started. From the Members tab of the Group Properties, select a member subscription and drag it up or down until the subscriptions appear in the order you want. For example, the following illustration shows re-ordering the replication of the authors table to be third in the Run Order instead of first.

re-ordering snapshot subscriptions

Select the Members folder for the group to verify the Run Order of the subscriptions.

verifying the subscription Run Order

SQDR Plus option Incremental subscriptions are run in the order they are added to the group. To change the order of incremental subscriptions within a group, delete and re-add the subscription(s) you want to move to the bottom of the order.


Controlling Updates for Incremental Subscriptions

The polling interval for a group of incremental subscriptions affects when updates are applied to the destination. For example, if you want the updates to be applied to the destination database only once per day, you would set the poll interval to be greater than 24 hours, such as a value of 25 hours. After the baseline replication for the subscription is created, updates will be applied to the destination only when the source is polled for changes at the specified interval.

In addition to polling the source for change data you can configure SQDR Plus and the incremental subscription to receive a notification when changes are committed to the source. When the subscription receives notification that changes are available, the changes are then applied to the destination immediately instead of waiting for the normal polling cycle. The topic Configuring a Source for Incremental Replication describes how to configure the source to receive notifications. Refer to your SQDR Plus documentation for information about configuring the Capture Agent to send change data notifications.


Programmatic replication

Running Replications Outside Replicator Manager

Using COM (VBScript, Visual Basic, and C#)

You can create a VBScript, Visual Basic, or C# application to run replications and perform other SQDR tasks; refer to the StarQuest tech note Automating SQDR tasks using COM (VBScript, Visual Basic & C#)

Using Stored Procedures to Manage SQDR Subscriptions (Deprecated - local SQL Server control database only)

You can manage SQDR subscriptions by calling the SQL Server extended stored procedure sqdrxp_startreplication. This feature is not available when using a remote SQL Server instance or Db2 for the control database.

Refer to the Microsoft SQL Server documentation for more information about executing stored procedures. The SQL Server service should be running as a user ID that Local Activation permission for the SQDataReplicator class. This security permission can be modified using the Component Services administrative tool.

To run an individual subscription:

USE master
EXEC sqdrxp_startreplication
 @source = '<source_name>',
 @subscription = '<subscription_name>',
 @wait = 'Y'|'N'

The optional argument, @wait, determines whether the replication operation must complete before the stored procedure call returns. The default for the @wait argument is 'No', which causes the call to return before the replication operation completes if the @wait argument is not specified.

To run a group of subscriptions:

EXEC sqdrxp_startreplication @source = ' ', @subscription = '<group_name>', @force = 'Y'|'N'

See the next section for details about the optional @force parameter.

Forcing Baselines for Incremental Subscriptions

When an incremental subscription in a group is in a warning state, that condition may be resolved over time; however, error conditions may require that another baseline replication operation be performed.

You can force a new baseline replication to be run for an incremental subscription with Data Replication Manager by selecting the subscription from the Subscriptions folder of the corresponding source (Sources folder) and executing the Run Subscription command.

If you want to force a baseline to be run for every incremental subscription in a scheduled group that is in a warning or error state, SQDR provides a programmatic method using the optional @force parameter of the sqdrxp_startreplication extended stored procedure (SQL Server control database only). When the @force parameter is specified, a new baseline replication is run for all the incremental subscriptions in the group that have encountered an error or a warning.


Skipping scheduled replications

Skipping a Scheduled Replication

If a snapshot subscription or group is scheduled to run on a regular basis, you can disable it from running and still retain the schedule settings. This allows you to temporarily suspend the replication, skipping as many of the scheduled replications as needed, and then re-enable the subscription or group to run on the previously defined schedule.

  1. In the Replicator Manager main window, select the subscription or group that you want to disable.

  2. Right-click and select the Properties command.

  3. Click the Schedule tab and de-select (no checkmark) the Scheduled option.

  4. Click OK to accept the change and return to the main window.

The Data Replicator keeps the previous schedule information, but will not automatically run the subscription as long as the Scheduled option is disabled.

To resume regular replications after disabling a subscription, repeat the above steps but turn on (checkmark) the Scheduled option to re-activate the schedule.

SQDR Plus option For subscriptions that perform incremental replication you can change the schedule for when SQDR checks the group of subscriptions to determine if any require a baseline replication to be run. You also can pause updates to temporarily suspend the incremental replication operations.


Pausing Updates for Incremental Subscriptions

If you need to temporarily stop updates for a group of incremental subscriptions, such as to resolve an error condition or perform hardware maintenance, you can pause the group. The updates will continue to be staged so they can be applied to the destination when you resume the update operation. If you need to upgrade the SQDR Plus software or perform maintenance on the host, you can avoid temporary errors if you pause or stop the Incremental group and delete the connection while the host is unavailable. Resume updates for the group after the updated SQDR Plus software is running.

You can also select a source and destination and pause or resume all incremental groups associated with the source or destination.

While the incremental group is paused, the status for each of the subscriptions and the group reflects the warning state ( Warning icon for incremental subscription, Warning icon for incremental group) and the statistics icon changes from Active ( Statistics for Active Incremental Group) to Stopped ( Statistics icon for a paused incremental group). When you resume the updates, changes at the source that have been staged while the subscriptions were paused are applied to the destination.

Depending on the activity that occurs on the source while an incremental group is paused, another baseline replication may be needed to re-synchronize the destination data with the source data. You can set the group schedule to run baseline replications automatically as they are needed, or execute the Run Group command to clear most error conditions. To force a new baseline replication for an incremental subscription select the subscription from the Subscriptions folder beneath the corresponding source (Sources folder) and execute the Run Subscription command.


Aborting a replication

Stopping a Snapshot Replication or Group

Stopping a replication after it has begun copying data can leave the destination data in an unreliable state. The state of the destination data depends on how long the subscription ran, the commit interval that was used for the bulk copy procedures, and the destination and replication options that were specified for the destination table. If the subscription or group finished running before the cancellation request was actually received by the Replicator Service, the destination data may be fine. But if the cancellation request interrupts the replication, the destination data should not be used.

To stop a replication that is currently running:

  1. In the main Replicator Manager window, select the subscription or group that is associated with the replication you want to stop.

  2. Right-click and select the Stop Subscription or Stop Group command, as appropriate for the selected object.

SQDR Plus option You cannot stop an incremental replication operation or group. Use the Pause Updates and Resume Updates commands if you need to temporarily stop an incremental subscription or group.


Resume an interrupted baseline or snapshot

Resuming an interrupted baseline or snapshot

One advantage of using incremental subscriptions is most apparent with large database tables, since once the baseline has been run, only changes need to be transmitted. However, the execution of a baseline for a very large table may take a long time and can be subject to disruptions in the network or on the source or target system. This feature allows you to resume an interrupted baseline, appending new rows to the last committed row in the destination table, without having to start the baseline from the beginning.

This feature applies to both snapshot and incremental subscriptions.

In the Global Service properties for the SQDR service, set the ODBC or BCP commit interval to a relatively low non-zero value, or use an ODBC commit interval value of one, which means "use autocommit" - each insert is checkpointed; this provides the most granular level of recovery at the expense of performance.

For a SQL Server source, the subscription should include a derived column with a value of $ROWGUID and destination column type of CHAR(36). You do not need to specify a destination column name.

If a baseline fails to run, the icon associated with the subscription will be a yellow exclamation mark, indicating that a baseline is still required, and there will be an event with a red "X". If you view the details of the replication event, you may see an error such as:

Fetch failed at source.
ODBC message: SQLSTATE 08S01, native error 0, [StarSQL][StarSQL ODBC Driver] Communications link failure.

When the baseline runs again (either started explicitly by selecting "Run Subscription", or started as a scheduled or "as-needed" triggered operation), the details of the replication event should contain an informational message:

Subscription restarted after <N> rows.

Viewing the properties of the subscription and saving it will reset the subscription and remove all checkpoints; the next baseline will run from the beginning.

Note: Checkpoint Restart is not available if you have disabled the use of the ORDER BY clause for performance reasons - see the useOrderBy advanced setting.


Copying a Subscription

The Duplicate command allows you to make a copy of a selected subscription. You must give the duplicate subscription a different name, and it must use the same logical source as the original subscription. To use a different source, create a new subscription rather than duplicating an existing one.

A duplicate snapshot subscription is created with the same replication settings as the original. Before you duplicate a subscription, review the Destination Option settings in the original subscription. You may need to edit the original subscription to avoid inadvertent loss of data when the duplicate subscription is created, such as if the original subscription is set to create a table when the subscription is saved. The duplicate subscription also inherits the same schedule, so be sure the original subscription will not run until you can change the duplicate subscription schedule and destination settings.

SQDR Plus option Duplicating a subscription that performs incremental replication creates a copy of the subscription that performs snapshot replication. (You cannot have two incremental subscriptions updating the same destination table.) The Destination Option settings of the snapshot subscription depend on whether the Use Existing Table option was enabled for the incremental subscription. If the Use Existing Table option is enabled for the incremental subscription, the snapshot subscription will be configured to use the existing table and delete the existing data before replication. If the Use Existing Table option is disabled for the incremental subscription, the snapshot subscription created from it will be configured to re-create the destination object with each replication.

After you duplicate the subscription, you can change the properties for the duplicate subscription, and then, if necessary, change the original subscription back to its original settings. See Duplicate Subscription dialog for more discussion of these issues.


Renaming a Subscription

Each subscription that is associated with a particular source must have a unique name that can be up to 29 characters. Within a group a subscription is identified by its source and name, in the format source_name/subscription_name. If you change the name of a subscription that also is a member of one or more groups, the name is automatically changed wherever the subscription is used.

To rename a subscription:

  1. Select the subscription that you want to rename.

  2. Right-click and select the Rename command to display the Rename Subscription dialog.

  3. Enter a new name for the subscription and click OK.


Renaming a Group

Each group that is defined under the Groups folder must have a unique name. A group name can be up to 35 characters.

To rename a group:

  1. Select the group that you want to rename.

  2. Right-click and select the Rename command to display the Rename Group dialog.

  3. Enter a new name for the group and click OK.


Copying or converting IR member

SQDR Plus information Copying a Member of an Incremental Group

The ability to copy incremental subscription members allows you to set up multiple target locations using the same basic subscriptions for a given source.

You may copy an incremental subscription between existing replication groups if the following criteria are met:

  • Both groups are using different SQDR Destinations. If you need to replicate to the same target database, create a new Destination.

  • The destination group must not already contain a subscription for the same source file.

  • Both the source and target groups must be paused.

  • The Database and/or Object schema in the Advanced tab of the Source and Destination properties must be configured.

To copy a subscription:

  1. Pause the TO and FROM groups with Pause Updates.

  2. In the Data Replication Manager, display the members of the incremental group containing subscriptions that you wish to copy.

  3. Select the members (subscriptions) that you wish to copy. Use the control or shift keys to select multiple members.

  4. Right click and select Copy Member from the drop down menu. If Copy Member is disabled, then you need to pause the group.

You will see a list of eligible target groups (those that use a different destination, and are paused).

  5. Choose the group that you wish to copy the members to and select OK.

  6. Optionally provide a member qualifier to use as a prefix or suffix.

  7. Click OK.

Because subscription names must be unique within a given source, this dialog allows you to specify a prefix and/or suffix member qualifier; the value is added to the name of the newly created subscription to insure uniqueness. You may also replace a previously-specified prefix or suffix by entering a value for the “Old Qualifier”. If no value is provided, SQDR will name the subscriptions with automatically generated unique names of the form "TOGroupName_subscription-name" where "TOGroupName" is the name of the Group the subscriptions are copied to.

When attempting to copy a subscription to a group that already has a subscription of that name, that member will be skipped but copying will continue. After processing the remaining copy operations, a summary showing success and failures is displayed.

When an incremental member (subscription) is copied, the new member's qualifier (database) and owner (schema) values (for both source and destination) are updated to reflect the defaults in effect for the "TO-group's" source and destination. These values can be viewed and modified on the "Advanced" page of the definition of a source or destination. The values are evaluated at the time of the copy operation, and may be subsequently changed without effecting existing subscriptions.

Caveats:

  • This operation may be performed even when the groups use different source databases or different schemas, but this must be done with caution, as SQDR does not re-validate the schema on the source system.

  • Subscription schedules are not copied. You may use the group scheduling instead.

  • No verification is made when copying to a destination RDBMS of a different type.

  • A subscription won’t be copied if the table definition of the “FROM” source table does not match the “TO” source table in a different database and/or object schema.


Moving a Member of an Incremental Group

SQDR Plus information Moving a Member of an Incremental Group

You may move an incremental subscription between existing replication groups if the following criteria are met:

  • Both groups use the same source and destination.

  • Both groups are paused.

  • The destination group must not already contain a subscription for the same source file.

To move a subscription:

  1. Pause the TO and FROM groups with Pause Updates.

  2. In the Data Replication Manager, display the members of the incremental group containing subscriptions that you wish to move.

  3. Select the members (subscriptions) that you wish to move. Use the control or shift keys to select multiple members.

  4. Right click and select Move Member from the drop down menu. If Move Member is disabled, then you need to pause the group.

You will see a list of eligible target groups (those that use the same source and destination, and are paused).

  5. Choose the group that you wish to move the members to and select OK.


Converting a Member of an Incremental Group

The ability to convert incremental subscription members to a snapshot subscription preserves any customization work you may have done, such as column mappings and pre- and post-processing. The original incremental subscription is not changed or removed, so you should take steps to avoid target file conflicts - either apply a member qualifier to the Destination Table Name, delete the I/R subscription when you are done, or coordinate when you run the snapshot subscription or run the I/R group.

The procedure is similar to copying an incremental subscription member to another replication group; see Copying a Member of an Incremental Group.

  1. Pause the FROM I/R group with Pause Updates.

  2. In the Data Replication Manager, display the members of the incremental group containing subscriptions that you wish to copy.

  3. Select the members (subscriptions) that you wish to copy. Use the control or shift keys to select multiple members.

  4. Right click and select Copy Member from the drop down menu. If Copy Member is disabled, then you need to pause the group.

  5. Choose the "Convert to Snapshot" button.

  6. To avoid subscription or destination name conflicts, enter a member qualifier and apply to either Subscription, Destination Table Name, or both. You may also replace a previously-specified prefix or suffix by entering a value for the "Old Qualifier".

The destination group information is not used and should be ignored.

The new snapshot subscriptions will appear under the same Source as the original incremental subscriptions. If desired, you can then add the new subscriptions to a snapshot group.


Changing Subscription Properties

You may need to change the properties of an existing subscription, such as if the schedule for the replication needs to be modified or if the table structure of the data is changed.

Clicking OK after you change or just view the group properties will save the subscription and require a new baseline replication be run. If you only want to view the properties without making changes or triggering a new baseline, click Cancel instead of clicking OK to close the Properties dialog.

  1. In the main Replicator Manager window, double-click the subscription you want to edit, or right-click and select the Properties command.

  2. In the Subscription Properties dialog, click the appropriate tab to display the information you want to change.

    If source table structure changes affect the data that is subscribed to, click the Columns tab of the Subscription Properties dialog and then click the Rebuild Column List button to refresh the list of source columns before you save the subscription.

    If you change any subscription properties that affect the structure of an existing destination table, click the Destination tab and use one of the following methods to alter the destination table structure to match the subscription's requirements:

    • For a table with no referential integrity constraints, select to re-create the table, which will drop the existing table and create a new one with the structure defined by the current subscription settings.

    • For a table with referential integrity constraints, select Use Existing Table and use a SQL utility to modify the existing destination table to match the new structural requirements defined by the subscription.

  3. Click OK to approve the subscription properties.

Note that, if the subscription also is a member of one or more groups, changing the group property options Replicate All Indexes or Replicate All Constraints also changes the selections on the subscription's Indexes tab. Whichever selections are made last to the indexes and constraints properties are the choices that will be in effect when the subscription is run, individually or as a member of a group.


Deleting statistics

Deleting Subscription Statistics

Each Data Replicator control database contains a table, named stats, for storing the event information for all subscriptions that are managed by the corresponding Replicator Service. If the stats table reaches the maximum number of rows that it is configured to have, either for a particular subscription's statistics or the statistics for all subscriptions, Data Replicator deletes enough of the oldest records to allow the new statistics to be inserted. You configure the logging of statistics from the Service Properties Logging Tab.

To free space in the stats table, you can delete the statistics for one or more subscriptions or groups when they are no longer needed.

If you select a subscription from the Subscriptions folder and execute the Delete Statistics command, all of the statistics for that subscription--regardless of whether the subscription was run individually or as a member of a group--are deleted from the log.

If you select a group and execute the Delete Statistics command, only the statistics that are related to running each member as part of that group are deleted. Statistics for running the subscription as part of another group or for running it as an individual subscription remain in the log.

  1. In the Replicator Manager main window, select a subscription from the Subscriptions folder or a group from the Groups folders.

  2. Right-click and select the Delete Statistics command.

See Also

Viewing Statistics

Service Properties Logging Tab


Deleting Subscriptions and Groups

If you delete a subscription from the Subscriptions folder of the source it is associated with, the subscription also is removed from any group to which it is a member. If you just want to remove a subscription from a group, keeping it associated with the original source and available to include in other groups, select the subscription from the appropriate Members folder and select the Delete Member command.

If you select a group and choose the Delete command, a dialog appears so you can choose whether you only want to delete the group and its members, or you also want to delete all the member subscriptions from their original Sources\Subscriptions folders too. Choosing the Delete All Group Member Subscriptions option does not delete subscriptions that are also members of another group.

You cannot delete a subscription or a group that is currently running.


Troubleshooting Problems

This section describes how to resolve some of the common issues that can be encountered when using Data Replicator, what diagnostic information is available to help troubleshoot problems, and how to contact StarQuest technical support if you need further help.

The StarQuest Data Replicator software includes a WRITEDB.CMD utility that uses the SQL Server Bulk Copy Program (BCP) to copy the contents of the Data Replicator control database tables and views to text files. If you encounter a problem while using the Data Replicator, StarQuest Technical Support may ask that you run WRITEDB.CMD to provide information about your control database.

The Data Replicator Service also takes advantage of the Windows dump facility should there be a need to troubleshoot unhandled exceptions with the Replicator Service. See Troubleshooting Replicator Service Exceptions if you experience service failures and need to capture a system dump to help determine the cause.


Contacting StarQuest

Please use the following methods to contact StarQuest Ventures if you need to obtain a license key, or have suggestions or need information about StarQuest products.

Support

If you do not want to use the online licensing feature of the License Configuration utility, you can obtain a license key for your product by sending an email to support@starquest.com or opening a support ticket at https://support.starquest.com with the following information:

  • TCP/IP address or Host ID of the computer on which the license will be installed

  • Number of connections purchased

  • Company Name

  • Contact Name

  • Phone Number

  • Email Address

StarQuest Support will send a reply email that provides the license key for your organization's use of the product. Since the license is unique to the computer on which it will be installed, you must contact StarQuest should you need to move the license from one computer to another.

Additional technical support may be available subject to the prices, terms, and conditions specified in your organization's maintenance contract with StarQuest Ventures, Inc.

Sales and Service

If you have ideas for product enhancements or need more information about how StarQuest products can provide solutions for connecting Windows and UNIX applications to IBM host resources, please contact us via any of the following methods.

Address | StarQuest Ventures, Inc. 548 Market St, #22938 San Francisco, CA 94104-5401 USA
Telephone | +1 415.669.9619 Option 1: Sales Option 2: Technical Support
Fax | +1 415.669.9639
World Wide Web | www.starquest.com
Support | support.starquest.com
Info Center | www.starquest.com/docs


Obtaining Licensing Information

The license key for using StarQuest products is based on which product you are licensing, the number of connections you need, and the particular computer on which the license will be installed. The easiest method for obtaining a license key is to use the online licensing feature of the License Configuration utility. For details about online licensing, start the License Configuration utility and click the Help button.

If you prefer to obtain a license key from StarQuest via an email request, you need to provide a static IP address or the Host ID of the computer on which you will install the product. If the computer is in a network that uses the Dynamic Host Configuration Protocol (DHCP), provide the Host ID instead of an IP address to StarQuest when you request the license. IP addresses that are assigned by the DHCP service are dynamic and can invalidate the license key when the computer is assigned a different unique IP address.

Displaying the IP Address

Each node in an IP network must be assigned a unique address, such as 172.19.36.72. The IP address can be static (permanently assigned to the computer) or dynamic (assigned by the DHCP service). If the computer is assigned an IP address by the DHCP service, the IP address can change and you should provide the Host ID instead of the IP address when you request the license from StarQuest.

  1. To display the IP Address of a computer, enter the command ipconfig at a Command Prompt.

A display similar to the following shows the IP address that is currently assigned to the computer.

Connection-specific DNS Suffix: company.com
IP Address....................: 172.19.36.72
Subnet Mask...................: 255.255.255.0
Default Gateway...............: 172.19.24.1

Refer to the documentation for your version of the operating system for more information about displaying a command prompt or using the ipconfig command.

Displaying the Host ID

The Host ID, or Disk ID, of a Windows computer identifies the physical disk in a particular computer. You can display the Host ID from the License Configuration utility by clicking Add from the Licenses tab. Or you can display the Host ID by opening a command window and entering the command label at the command prompt.

The label command displays information similar to the following, from which you provide the Volume Serial Number to StarQuest when you request the license key.

Volume in drive C: has no label
Volume Serial Number is C854-CE05
Volume label <ENTER for none>?


Mapping Data Types

For each source column you want to replicate, you must select a data type for the corresponding destination column. Usually it is best to choose a destination data type that closely corresponds to the source data type, and to set any modifiable destination Precision and Scale settings to match the corresponding source settings.

However, some data types do not have exact equivalents in other environments, and allowable Precision and Scale settings vary for some data types that are otherwise similar. If you are replicating floating point data from one database system to another, there may be slight changes in the data precision due to the architectural differences of the source and target database systems. The data format and precision for dates and time also commonly differ among database systems.

SQDR Plus information Data type differences for columns that are used as a primary key or index can be particularly problematic if they are relied upon to track change data for incremental subscriptions. A key that is unique on the source may no longer be unique on the target if the target supports a different precision value or otherwise changes the value. If you are using SQDR with Db2 for i or Oracle, you can configure SQDR to add a RRN to the staging table to provide a field that will be unique on both the source and target databases.

This topic describes the major differences in how data types are supported by Db2, SQL Server, and Oracle database management systems.

Db2 and SQL Server Data Type Restrictions

The Decimal type exists for both Db2 and SQL Server, but has different allowable values for Precision in the two environments: Db2 allows up to 31 digits, but SQL Server only allows up to 28. For very large decimal values, you may prefer to use a destination data type that uses exponential notation, such as Float or Real, which can preserve the proper magnitude of the data, though it introduces the possibility of rounding errors for replications with a SQL Server destination.

SQL Server does not provide equivalents for Db2 types Date and Time. For these source column types, Replicator Manager sets the default destination data type to Char, which preserves the data, although in a slightly less convenient form. If you manually change the destination data type to the SQL Server type Datetime, Data Replicator manages the difference between source and destination column structures as follows if the records are inserted using ODBC:

  • When a Date Db2 column is replicated to a SQL Server Datetime column, the time portion of the destination value is set to 00:00:00.0000.

  • When a Time Db2 column is replicated to a SQL Server Datetime column, the date portion of the destination value is set to the current system date.

If you replicate a numeric source column type to a non-numeric destination column type, be sure that the destination column has a sufficiently large Precision setting to contain all the characters in the data to be replicated, including decimal point characters.

If you want to replicate a Db2 column of type Timestamp, set the corresponding SQL Server destination column to use either a data type of Datetime or a data type of Char and a Precision of 26. Do not attempt to use the SQL Server Timestamp data type, which is reserved for columns in which SQL Server obtains time information from the local computer system, rather than via replication.

Your choice between destination data types of Datetime and Char should be based on the form and precision of the data your application needs. If you replicate a Db2 Timestamp column to a SQL Server Datetime column, the date and time nature of the data is preserved, but the data is truncated because the SQL Server Datetime data type stores fewer characters than the Db2 Timestamp data type. If you replicate a Db2 Timestamp column to a SQL Server column of type Char with a Precision of 26, the full precision of the Db2 Timestamp data is preserved with minimum use of disk space, but SQL Server treats it as simple character data, rather than date and time data.

If you define a replication to use the SQL Server Bulk Copy facility (BCP) for inserting records, you can insert a derived column to handle the differences between the Db2 Date and Time data types and the SQL Server DATETIME data type. Delete the current column that has the DATE data type from the Columns tab of the properties for the subscription, and insert a derived column that has the following format, where DATEFIELD is the name of the Db2 source DATE column:

Expression: TIMESTAMP(CHAR(DATEFIELD)||'-00.00.00.000000')

Then select Datetime for the data type of the derived column.

Db2 and Oracle

When replicating from Db2 to Oracle, DATE fields are now mapped to Oracle DATE fields (older versions of SQDR mapped them to CHAR(10)).

Db2 Server for VM and VSE Data Type Restrictions

To replicate a SQL Server Text column to a Db2 Server for VM and VSE destination, Data Replicator sets the default destination data type to Longvarchar. In the Db2 Server environment, all Longvarchar fields are set to a 32K field size, so you cannot select a different Precision setting, and data larger than 32K is truncated to fit the destination field size.

To replicate a SQL Server Image column to a Db2 Server destination, Data Replicator sets the default destination data type to Longvarbinary. In the Db2 Server environment, all Longvarbinary fields are set to a 32K field size, so you cannot select a different Precision setting, and data larger than 32K is truncated to fit the destination field size.

Oracle Data Type Restrictions

ODBC drivers for Oracle support more restricted sets of data types than either SQL Server or Db2. If you use an ODBC driver for Oracle to connect to Oracle from an application other than Data Replicator, and if you then use the SQL CREATE command to create a new table, the data type restrictions may not be apparent. If you specify an unsupported data type (such as INTEGER) for a table column, a supported data type (such as DECIMAL) may be automatically substituted.

When you work with an Oracle destination table in StarQuest Data Replicator, Replicator Manager lists only the destination data types actually supported by your current ODBC driver for Oracle. However, as with any situation where an exact equivalent of the source data type is not supported for the destination, Data Replicator attempts to substitute appropriate default values for the destination column data type, precision, and scale. When replicating Oracle Date fields to DBS, Data Replicator sets the default destination data type to Timestamp because the Oracle Date data type more closely resembles a Db2 Timestamp. Refer to Defining Derived Columns for an example of how you can add a derived column to properly replicate an Oracle Date column to a Db2 Date field if you prefer to have the destination use the Date data type.

Oracle allows negative values in columns of the data type DATE. However, the corresponding ODBC data type TIMESTAMP and Db2 and SQL Server equivalents do not allow negative values. If you attempt to replicate an Oracle DATE column that contains negative values, one or more problems may occur. If the destination database is SQL Server, the replication may fail or may insert incorrect data into the destination table. If the destination database is Db2, the processor utilization on the destination computer may climb to near 100%. If this occurs, stop and restart the Replicator Service to return processor utilization to normal levels.

If your source system supports CASE expressions in SQL statements, you can avoid this situation by creating a derived destination column that uses a CASE expression to convert negative DATE values to appropriate equivalents. These changes take place during replication, and do not affect the actual source data. Alternatively, you can add a WHERE clause to avoid replicating any records that contain negative values in a DATE column.

See Also

Error Replicating Text or Image Data to SQL Server
Error Replicating TIMESTAMP
Row Count Errors


How to Enable SQDR Logging for an Incremental Replication Subscription

SQDR Plus information Enabling Logging for an Incremental Replication Subscription

If an SQDR Incremental Replication (IR) Group is flagged due to a polling error encountered by a particular IR Subscription, StarQuest Technical Support may request that you enable logging of this particular IR Subscription in order to capture the activity causing the error. An error that might require this type of logging is:

Subscription is active, but has had unexpected errors or row counts for INSERT, UPDATE, or DELETE on the target table

This topic explains how to enable logging at the level recommended by StarQuest Technical Support, and how to collect the log data after the error condition has been captured. StarQuest recommends that you enable logging only when instructed to do so by a StarQuest Technical Support Engineer.

The logging mechnism uses the Windows Script Host, which cannot be disabled or blocked on the SQDR system from which you will run the command.

SQDR provides a Windows script, EnableKeyLog.vbs, which is used to enable and disable logging. This script can be found in the \Program Files\StarQuest\SQDR directory. The logging commands should all be executed from a Windows Command prompt. To open a Windows command prompt, click on Start-->Run, enter 'CMD' in the Open text box and click OK. The usage of the EnableKeyLog.vbs script is as follows:

EnableKeyLog.vbs <IRGroupName> <SubscriptionName> [off | low | high]

You can use SQL wildcards, such as %, for all or part of either the <IRGroupName> or the <SubscriptionName> variables.

The low level of logging captures only event notifications from the SQDR Plus Capture Agent that result in flagged subscriptions, such as those which require that a new baseline be run (e.g. if the source table has been reorganized/dropped/altered or if row count or data errors have occurred). The high logging level captures all data that is replicated from the source table to the destination table plus all the events that are captured with the low level of logging.

To Enable Logging

Enter the following command to enable logging at a high level, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the IR Subscription you wish to log.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName> high

Enter the following command to enable logging at a low level, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the IR Subscription you wish to log.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName>low

As an example of using a SQL wildcard, the following command would enable key logging at the low level for any subscription named Employees in any IR Group.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" % Employees low

To Disable Logging

Enter the following command to disable logging, where <IRGroupName> is the name of the IR Group and <SubscriptionName> is the name of the subscription for which activity is being logged.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" <IRGroupName> <SubscriptionName> off

If you enter the command using SQL wildcards you can disable logging for some or all subscriptions in some or all IR groups, depending on the subscription and group names and the wildcards you specify. For example, to disable logging for all subscriptions that begin with "Employ" in any IR group, you could specify the following command.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs" % Employ% off

To View the Current Logging Activity

Enter the following command to view the subscriptions that are currently being logged.

cscript "C:\Program Files\StarQuest\SQDR\EnableKeyLog.vbs"

To Collect the Log Data

The log data is stored in the SQDR control database. Use the WriteDB command to export the contents of the control database to text files. Compress the output files to a single file and send it to StarQuest Support.


Running WRITEDB.CMD

Running WRITEDB.CMD (SQL Server control database)

The StarQuest Data Replicator software includes a WRITEDB.CMD utility that uses the SQL Server Bulk Copy Program (BCP) to copy the contents of the Data Replicator control database tables to files.

If you encounter a problem while using the Data Replicator, StarQuest Technical Support may ask that you run WRITEDB.CMD to help troubleshoot the problem. However, more typically, you will be asked to provide a backup image of the control database.

Follow the steps below if you need to run WRITEDB.CMD to generate control database information that you can send to StarQuest Technical Support.

  1. Make sure the SQL Server service is running. (See Controlling Services for SQDR, Db2 for LUW and SQL Server for details.)

  2. Open a Command window (Windows Start-->Run, then enter cmd).

  3. Change to the directory where the Data Replicator is installed.

  4. Run the WRITEDB command, substituting the parameters given in the following syntax as appropriate for your SQDR environment.

  5. Examine the WRITEDB.LOG file and the target directory to confirm the WRITEDB command completed successfully. Send the output files in a compressed folder to the StarQuest Technical Support Engineer you are working with to solve a Data Replicator problem.

WRITEDB.CMD Syntax

Following is the syntax of the WRITEDB command.

writedb.cmd control_DB_name control_DB_owner target_directory [loginID] [password] [server\instance]

where:
control_DB_name is the name of the SQDR control database
control_DB_owner is the owner of the database tables
target_directory is the location where you want to write the files
loginID is the userID for logging into the SQL Server database
password is the password for the SQL Server loginID
server\instance is the name of the SQL Server and instance to which you want to connect

Usage Notes

If you do not specify a loginID and password the command is executed using Windows integrated security.

The bcp utility connects to the default instance on the local computer if you do not specify a server. If you do not specify an instance, the bcp utility connects to the default instance on the specified server.

If you want to specify a server but do not want to specify a loginID and password, enter a dash (-) in place of the loginID and password parameters.

Running WRITEDB.CMD again using the same output directory overwrites the .txt, .dat, and .log files that were generated from a previous execution of WRITEDB.CMD.

Examples

The following command would write the user-defined tables of a control database named ControlDB with an owner of dbo to the \temp directory of drive C using the sa user ID and password.

writedb.cmd ControlDB dbo C:\temp sa password4sa

The following command would write the user-defined tables of a control database named ControlDB with an owner of dbo to the \temp directory of drive C using Windows integrated security (no SQL Server loginID and password), connecting to the default instance on a SQL Server named myserver.

writedb.cmd ControlDB dbo C:\temp - - myserver

Output

The WRITEDB utility creates the following files in the target directory. Files with a .txt extension or a .csv extension can be viewed using most spreadsheet applications. Files with an extension of .dat are data files that are written in native format to preserve the database data types. This list may not be complete if new tables have been recently added to the control database structure.

Filename | Description
appevt.csv | A Comma Separated Value formatted file that contains events generated to the Application Event Log for the SQDR service.
sysevt.csv | A Comma Separated Value formatted file that contains events generated to the System Event Log for the SQDR service.
columns.txt | An ASCII text file with Information from the columns table of the control database.
groups.txt | An ASCII text file with information from the groups table of the control database.
group_subscriptions.txt | An ASCII text file with information from the group_subscriptions table of the control database.
ir_group.txt | An ASCII text file with information about groups that contain incremental replication subscriptions.
ir_keylog.dat | Contains key logging data.
ir_subscription.txt | An ASCII text file with information about the incremental replication subscriptions.
sqconstraints.txt | An ASCII text file with information from the sqconstraints table of the control database.
sqindexes.txt | An ASCII text file with information from the sqindexes table of the control database.
srcdest.txt | An ASCII text file with information from the srcdest table of the control database.
statistic.dat | Contains data from the statistic table of the SQDR control database.
subscription.txt | An ASCII text file with information from the subscriptions table of the control database.
writedb.log | An ASCII text file with status information about the most recent execution of WRITEDB.CMD.
CrashDumps\ *.* | Any files that have been written to the Crash Dumps directory by the Windows dump facility due to an SQDR service failure.


Viewing Diagnostic Messages

You can view diagnostic messages concerning Data Replicator operations from within the Replicator Manager, from a text editor, from the Windows operating system, and from SQL Server utilities if they are available. The following table describes what diagnostic information you can find in each location.

Location | Diagnostic Information
Replicator Manager main window | Shows statistics about each subscription that has been run. With the View menu set to show Details, select the desired subscription to display historical statistics for the subscription in the right pane. Select a subscription from the Members folder of a group to display only the statistics for the subscription as it relates to running the group.
Replication Statistics dialog | Provides detailed success or failure messages for the selected replication event. Double-click any replication event listing for a particular subscription to display the detailed event information.
Windows Application Event Log | Shows activity of the Replicator Service and replication operations. Run the Event Viewer console and sort or filter the Source column to group all the Replicator Service (SQDRSVC) entries. If you need to capture ODBC messages, increase the event logging level as described in Service Properties Logging Tab.


Viewing Windows Events

Perform the following steps to view Windows events that are generated by the Data Replicator service. Note that, if a subscription is flagged for an operational error, SQDR displays only three error events and then a summary event notifying that further logging of the same error will be suppressed until a new baseline replication is performed. Data errors, such as SQL or ODBC errors, are never suppressed, so there will be one event record for each occurrence of a data error if the expanded or maximum level of logging is enabled.

  1. Start the Windows Event Viewer, which is available from the Administrative Tools section of the Control Panel.

  2. Click Application in the left pane to display the application event log in the right pane.

  3. Click the Source column to group the events by application.

  4. Scroll to the Data Replicator Service, represented by SQDRSVC in the Source column, as shown below.

viewing SQDR Service events

  5. Double-click any event to view more details. From the Event Properties window you can click the Down Arrow to view next event button to view the next event, the Up Arrow to view previous event button to view the previous event, or the copy event details to the Clipboard button to copy the event details to the Windows Clipboard. Refer to the Event Viewer help for additional procedures for working with event logs, such as how to archive a log.


Bulk Copy Server Table Error

Under certain conditions, a subscription that uses the Insert Using SQL Server Bulk Copy replication option may fail with the following message:

"Attempt to use Bulk Copy with a non-existent Server table."

This error message normally indicates that the name of the destination SQL Server does not match the name that Windows uses for the destination computer. You can address this problem by matching the SQL Server and computer names, as described below.

  1. Determine the name of the destination computer by opening the Windows computer or system properties.

  2. Determine the name of the destination SQL Server. Use the SQL Server osql utility or the ISQL/w or Query Analyzer tools (sp_helpserver stored procedure) to display the server name.

  3. While connected to the destination SQL Server, run the sp_dropserver stored procedure to remove the old server name. For detailed information about the appropriate arguments for this procedure, see your SQL Server documentation.

  4. Run the sp_addlinkedserver stored procedure to set the SQL Server name to match the Windows Computer Name. See your SQL Server documentation for details.

  5. Stop and restart all SQL Server services. The topic Controlling Services for SQDR, Db2 for LUW, and SQL describes how to stop and restart the SQL Server services.


Cannot_Allocate_Space_for_Log

SQDR Plus information Cannot Allocate Space for Log

A subscription may fail to run because of inadequate space in the transaction log for the destination database. Such failures display an error message in the task history log for the target DBMS, such as the following error:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Can't allocate space for object 'Syslogs' in database 'dbname' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log."

Consult the documentation for your DBMS for detailed information about managing the transaction log.


Cannot Connect to Replicator Service

If you cannot connect the Replicator Manager to the Replicator Service, check the following:

  • Use the Windows Services Control Panel to verify that the StarQuest Data Replicator services are started.

  • If the SQDR service is started, examine the Application event log for SQDRSVC error messages. This will often reveal issues such as expired passwords or licenses.

  • Use the Computer Management Windows administrative tool to verify that any Windows userID's used to run the SQDRSVC or access the control database have not been disabled or have expired passwords.

If you are using Db2 for LUW as your control database:

  • Use the Windows Services Control Panel or the Db2 system tray icon to verify that the Db2 service is started.

  • Try to log on to Db2 e.g. with db2 connect to SQDRC user sqdr, to verify that you are using a valid login and password.

  • Use Db2 Data Studio, ODBC Test, or the db2 command line utility to verify that the login you use when attempting to connect to the Replicator Service is permitted to access the Data Replicator control database.

If you are using Microsoft SQL Server as your control database:

  • Use the Windows Services Control Panel to verify that the MSSQLSERVER (or MSSQL$named_instance) service is started.

  • Try to log on to SQL Server using osql, ODBC Test, ISQL/w or Query Analyzer to verify that you are using a valid SQL Server login and password.

  • Use SQL Server Studio or the osql utility to verify that the login you use when attempting to connect to the Replicator Service is permitted to access the Data Replicator control database, and is associated with one of the user names for that database.


Cannot_recreate_pending_drop

Cannot Create Destination Table

If the current subscription is set to re-create the table when the replication is run, and if the destination computer is slow, replication may fail with a message similar to the following:

"[StarSQL][StarSQL ODBC Driver][DB2]THE OBJECT USERNAME.TABLENAME CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE OBJECT."

Changing the Destination Options for the subscription to Use Existing Table and to Delete Existing Data Before Replication may help avoid the error and allow the data to be replicated to the slower computer.


Change Data Notifications Not Received

SQDR Plus information Change Data Notifications Not Received

To receive notification that changes have been committed to the source database, both SQDR Plus and the SQDR subscription must be configured to enable the notification feature. Note that the issues related to firewalls are apply to the scenario where SQDR Plus (tier 2) and SQDR (tier 3) are located on different computers.

On the SQDR Plus host, check the following:

  • Verify the values specified for the udpPort and minimumNotificationDelay parameters of the sqagent.properties configuration file; you can view the agent configuration using SQDR Control Center (SQDR Manager). Make a note of the port number. Refer to the SQDR Plus documentation for details about the sqagent.properties parameters. If these values are not present in the agent configuration, the default values are port 2728 and a delay of 5000 ms.

  • If the SQDR Plus computer is protected by a security firewall, make sure the firewall is configured to allow outbound UDP communications from the port specified for udpPort in the sqagent.properties file.

From the computer that is managing the subscription that you want to receive change data notifications, check the following:

  • The subscription is enabled to receive notifications. Make a note of the IP address or hostname that is configured for the Notification Address. See the Source Properties: Advanced tab topic for details.

  • The properties for the incremental group that contains the subscription allows notifications to be received. The Receive Change Data Notifications option on the Group Properties Advanced tab must be enabled.

  • If the computer that is to receive the notifications is protected by a security firewall, make sure the firewall is configured to allow inbound UDP communications from the port specified for udpPort in the sqagent.properties file on the SQDR Plus computer.

If the UDP notifications must pass through a Virtual Private Network (VPN) server it also may impose restrictions with inbound and outbound rules that take precedence over the Windows Firewall configuration. In this case, review the Routing and Remote Access defined for the VPN interface.

If are using SQDR Plus v3 on a Db2 for i host, there may be packet rules defined for the IP interface. You can use the iSeries Navigator to review and apply packet rules. The IBM i (OS/400) operating system also provides an Add TCP/IP Port Restriction command (ADDTCPPORT) which can restrict use of a particular port range to a user or a user group, which can affect operation of the SQDR change data notification feature.


Destination Column Names and Reserved Names

Because database systems vary on what they consider reserved names, one must be careful when replicating tables with columns that have names that are reserved names for the destination system, but not the source system. You may be able to handle this issue with quoted identifiers, or by editing the subscription and changing the name of the destination column.

An error such as:

[Informix][Informix ODBC Driver][Informix]DDL operations on "rowid" prohibited.

Or

ORA-00904: "ROWID": invalid character

may be the result of using a column name in the source system that happens to be a naming function on the destination system, and where the destination database does not support quoted identifiers to override the default naming. In the case of Informix or Oracle, one can use a different case for the destination column name to distinguish the name.


Destination Data Type Is Different

If you are replicating from one type of DBMS to another, you may see discrepancies between the source data type and the destination data type. Some data types do not have exact equivalents in other environments. In particular, ODBC drivers for Oracle support a more restricted set of data types than either SQL Server or Db2. Replicator Manager lists only the destination data types that are actually supported by the driver that communicates with the destination. If there is no equivalent data type in the target DBMS, Data Replicator attempts to substitute appropriate default values for the destination column data type, precision, and scale. See Mapping Data Types for more information about how data types are mapped from one DBMS to another.


Error Replicating Text or Image Data to SQL Server

When replicating text or image columns that contain null or zero-length data to SQL Server destination tables using BCP, which is the native-loader function for SQL Server, you may experience one of the following problems, depending on the SQL Server ODBC Driver version:

  • The replication subscription will fail with the error message:

Bulk-copy commit failed (bcp_batch). ODBC message: SQLSTATE 37000, native error 4813, [Microsoft][ODBC SQL Server Driver][SQL Server]Expected the text length in data stream for bulk copy of text, ntext, or image data.

  • The number of rows actually replicated does not match the number of expected rows.

You can resolve this problem by using one of the following workarounds:

  • Change the Destination properties for the subscription to use Insert Using ODBC. This option appears in the Replication Options for snapshot subscriptions or as Initial Snapshot Replication Options for incremental subscriptions.

  • Re-order the columns that are replicated so that the text/image column that contains null or zero-length data is the first column of the destination table. On the Columns pane of the subscription properties, highlight the column and click the Move Selected Column up arrow until the column is in the first position. This workaround is not suitable if the table contains more than one text or image column with null or zero-length values.

  • Filter out any null or zero-length data by specifying a WHERE clause in the subscription Criteria properties. An example would be WHERE <text_column> IS NOT NULL.


Error Replicating TIMESTAMP

Error Replicating TIMESTAMP Column

A subscription that attempts to replicate a Db2 TIMESTAMP column may fail with the following message:

"[SQL Server]The conversion of char to datetime resulted in a value out of range."

This problem occurs because Db2 and SQL Server have different ranges for their respective TIMESTAMP and DATETIME fields. You cannot replicate a Db2 TIMESTAMP column to a SQL Server DATETIME column if the source column contains year values earlier than 1753, or if it contains any time value of 24:00:00.000, which is equivalent to the allowable 00:00:00.000. If your Db2 source table contains values that SQL Server does not accept in a DATETIME column, you can set the subscription to replicate to a SQL Server column of type CHAR instead of DATETIME, as described in the topic Mapping Data Types. You also can add a derived column to overcome the data type differences, as described in Defining Derived Columns.

SQDR Plus information If you are running incremental subscriptions that rely on unique fields to track changes, data type differences can produce Row Count Errors if change data cannot be reconciled between the source and target databases.


Incremental Subscriptions and Where Clauses

SQDR Plus information Incremental Subscriptions and Where Clauses

Using references to other tables to construct selection criteria for incremental subscriptions has two consequences:

A View is created in the SQDR Schema referencing user tables on the source system. The views are created over the CT table (in SQDR) and any other user table that is referenced.

Unintended loss of incremental replication fidelity may ensue.

For example if a source table for replication is being selected based upon criteria in another table (the "filter" table) and changes to the "filter" table result in existing rows in the source table either being newly "selected" in the view or "de-selected", these changes will not be detected in the logs (as no inserts/delete activities occurred on the source table). This effect may be mitigated to the extent that a Foreign Key (FK) relationship exists between the source table and the filter table - which specifies "CASCADE DELETES" - so that changes to the filter table will either cause deletes in the log to appear for the source table, OR, rows must only be inserted into the source table AFTER a valid FK exists. This approach is not guaranteed to insure fidelity, because changes are evaluated at the time that Apply requests data, not at the time the transactions occur, so under some circumstances staged rows might be omitted, when they should be included, and vice versa.

We recommend that you avoid this use of horizontal partitioning of source by using only static data for criteria – i.e., use a constant expression in a partition rule.

For Db2 for i users: you cannot specify "criteria" when configuring incremental subscriptions for tables whose journaling is configured with Record Images=*AFTER; *BOTH record images must be journaled in order to use criteria.


Maximizing Bulk Copy Performance

Bulk copy replication speeds depend on multiple factors. If the destination database is on a SQL Server server, you may be able to improve performance of bulk copy replications by enabling the BULK-LOGGED recovery model for the database. The BULK-LOGGED recovery model uses the Select Into/Bulk Copy functionality, which allows non-logged operations to be performed. Refer to the SQL Server Books Online or other SQL Server documentation for more information about setting and using recovery models.


No Source Column Info Appears

No Source Column Information Appears

In the Subscription Properties dialog, if you click the Columns tab and then Rebuild Column List and no list of source columns appears, you may have exceeded the maximum number of connections to the source. Data Replicator waits up to 60 seconds for a connection to the source so it can get and display the available source columns. After that time, it cancels the operation for rebuilding the column list and displays an error message. You can adjust the maximum number of connections allowed for the source from the Advanced Source Properties dialog.

To decrease the possibility that idle pooled connections may prevent the Data Replicator from establishing active connections to other sources or destinations, you can reduce Time Limit settings on the Connection Pools tab of the Service Properties dialog, and you can reduce Maximum Connection Pool Size settings in the Advanced Source Properties and Advanced Destination Properties dialogs.

Contact StarQuest Ventures if you need to increase the number of connections that your licensing agreement allows.


No Source Table List Appears

In the Subscription Properties dialog, if you click the Source tab and then click Refresh and no list of source tables appears, you may have exceeded the maximum number of connections to the source. The Data Replicator waits up to 60 seconds for the source connection that it needs to display the available source tables. After that time, the operation is canceled and an error message appears. You can adjust the maximum number of source connections from the Advanced Source Properties.

To decrease the possibility that idle pooled connections may prevent Data Replicator from establishing active connections to other sources or destinations, you can reduce the Time Limit settings on the Connection Pools tab of the Server Properties dialog, and you can reduce Maximum Connection Pool Size settings in the Advanced Source Properties and Advanced Destination Properties. Contact StarQuest Ventures if you need to increase the number of connections that your licensing agreement allows.


Replication Success with Data Truncation

Replication with Data Truncation

The following destination column types truncate replicated data if the source data is too large to fit in the destination column:

  • binary

  • char

  • longvarbinary

  • longvarchar

  • varbinary

  • varchar

If the Replicator Manager shows the subscription status with a Warning icon in the right pane of the main window, you can select the event and choose View Replication Event to see more information. If that information includes the message "Warning: Data replicated with truncation," some of the data may not have been replicated. The Replication Statistics dialog provides more detailed information about which data was truncated so you can inspect the destination table. For some combinations of data, source column settings, and destination column settings, the data lost by truncation may consist solely of blank spaces. For other combinations, you may need to modify and re-run your subscription to ensure that all important data is replicated.


Replications Don't Run

Replications Do Not Run

Make sure that the Replicator Service and any service needed for the control database (Db2 for LUW or SQL Server) are running on the computer that is managing the subscription, and that the source and destination DBMS are available. The topic Controlling Services for SQDR, Db2 for LUW and SQL Server describes how to review the status and start and stop Windows services.

If many replications use the same source or destination at the same time, check that the Maximum Number of Connections setting for each source and destination is large enough to accommodate the simultaneous replications. Once a given source or destination has the maximum number of connections in use, no new replication involving that source or destination will start until a connection becomes available or the connection limit is increased. Data Replicator will wait up to two hours for a source or destination connection for a replication, but if no connection becomes available in that time, the operation is canceled and an error is recorded in the Windows Event Log.

Make sure that your edition of SQL Server supports enough tasks for the number of simultaneous subscriptions and other tasks you need to run. Contact your Microsoft software provider or refer to the Microsoft SQL Server Web site (http://www.microsoft.com/sql/) for the specifications of the various editions.

If your replications involve Db2 sources or destinations, make sure that you are licensed for an adequate number of StarSQL connections. To decrease the possibility that idle pooled connections may prevent Data Replicator from establishing active connections to other sources or destinations, you can reduce the Time Limit setting on the Connection Pools tab of the Service Properties dialog box, and you can reduce Maximum Connection Pool Size settings for the source and destination. Contact StarQuest Ventures if you need to purchase additional licenses.


Replicator Service Won't Start

A Replicator Service will not start if it does not have the correct security permissions. See Configuring Database Permissions for information about required permissions.


Troubleshooting Replicator Service Exceptions

The Data Replicator Service runs as a Windows service. This topic describes how to enable the Replicator Service to take advantage of the Windows dump facility to capture detailed information about the conditions that may be causing the service to fail.

If you are encountering exceptions from the Data Replicator Service, capturing detailed system information may help StarQuest Technical Support and Engineering determine what is causing the problem. The dump is generated using the Windows DBGHELP dynamic link library (DBGHELP.DLL) and contains technical details of the system environment and processes at the time the problem occurs. This topic describes how to prepare a Windows computer to generate a dump file when the Replicator Service encounters an exception that cannot be handled.

To generate a dump file when the Replicator Service encounters an exception, the Replicator Service program account must have privilege to the Debug Programs security policy. The following section describes how to change the security policy.

Changing the Debug Programs Security Policy

Generating a dump requires that the account under which the Replicator Service runs has privilege to debug programs. Use the Windows Local Security Policy tool to add a user to the Debug Programs policy. The Local Security Policy tool is available from the Administrative Tools shortcut. Select the Debug Programs policy and execute the Properties command to display the properties, from which you can add a user or group to which you want to grant the privilege. The default configuration for the Replicator Service is to run as a SYSTEM account, although another user account can be specified when the Data Replicator Service is configured.

adding Debug rights to Replicator Service account

After you change the Security Settings you must restart the Replicator Service for the changes to take affect. You can stop and start the Replicator Service from the Replicator Manager or from the Windows Services control panel.

Generating the Dump File

After you prepare the system as described in the previous step, proceed to use the StarQuest Data Replicator software in your normal manner. If an exception occurs and the service has permission, a dump file is generated. Send the dump file (.dmp) to the StarQuest technician whom you are working with to solve the problem.

The Crash Dumps Directory

The installation wizard creates a folder named C:\Users\Public\Documents\StarQuest\SQDR\CrashDumps in the common user directory. If an exception occurs a dump file is written to the CrashDumps folder. When a dump file is generated to the CrashDumps folder it is named with a format of:

sqdrsvc-<PID>-<TID>-YYYYMMDD-HHMMSS.dmp

<PID> is the process ID, <TID> is the thread ID, YYYYMMDD is the date, and HHMMSS is the time.

Locate the dump file and compress it into a .zip folder. Attach the .zip file to an email and send the email to support@starquest.com. The content of the CrashDumps folder also is collected if you run the WRITEDB.CMD utility.


Resetting SQDR Views

Resetting SQDR Views to Default Values

You can reset the following View settings to restore the Replicator Manager to its default display values:

  • The type of information that is selected in the View menu.

  • The order in which information is sorted when you click Details on the View menu.

  • The column widths for information that appears when you click Details on the View menu.

To reset the views you must exit from Replicator Manager, delete a file, and restart the Manager, as described in the following steps.

  1. Select Exit to close the Replicator Manager if it is running.

  2. Search for and delete the file SQDR.RMV from the computer on which you want to reset the views; this file is typically located in a directory like C:\Users\<USERID>\AppData\Local\StarQuest\SQDR.

  3. Restart the Replicator Manager to create a new SQDR.RMV file that contains the default values.


Row Count Errors

SQDR Plus information Row Count Errors

Incremental replication operations depend on having unique fields to identify changes that have been committed to the source database. After the baseline replication completes, an incremental replication subscription may fail with row count errors if the change data cannot be reconciled between the source and target databases.

If the collation sequence of the database is Case Insensitive, character fields that are uniquely identified on the host may no longer be unique when they are replicated to the target. For example, fields named “First” and “first” that are unique on the source would not be unique if the target database is configured to be insensitive to capitalization. Certain data types that have different levels of precision in different database systems also can create non-unique fields. For example, Db2 for i supports microsecond timestamps whereas Microsoft SQL Server only supports millisecond precision.

To help ensure unique fields, configure the collation sequence of the target database, especially if it is a SQL Server database, to be Case Sensitive. You can use SQL Server Studio to verify the collation sequence by viewing the database properties.

A case-sensitive collation has the characters “CS” appended to the Collation Designator. For example, the collation SQL_Latin1_General_CP1_CS_AS is a case-sensitive collation for U.S. English systems. You can set the collation for a new database using SQL Server Studio. To change the collation for an existing database, use the ALTER DATABASE (Transact-SQL) command with the COLLATE clause. Refer to your database system documentation for details on specifying the collation sequence for databases it manages.

If the host database is Db2 for i or Oracle, you also can use the Relative Record Number (RRN) or ROWID that SQDR Plus adds to the staging table instead of, or in addition to, using other primary keys or indexes to identify the change data. Using the RRN can help overcome problems caused when a unique field on the source is no longer unique on the target due to differences in how the different database systems support data types such as timestamp, which has microsecond precision in Db2 for iSeries whereas SQL Server supports only millisecond precision.

The TargetChecker administrative tool can help determine whether you are experiencing row count errors.

If you receive row count errors StarQuest Technical Support may ask that you enable logging to gather more details about the failure.


Subscription Not Allowed

SQDR Plus information Subscription Not Allowed

SQDR Plus provides a Publish feature that allows a database administrator to restrict specific database tables from being replicated. If the Publish feature is enabled, only database tables that have been published can be subscribed to for replication. If you receive an error that the ADDSUBSCRIPTION stored procedure failed because the subscription is not allowed, contact your database administrator to request that the table be published for replication.


Using SQDR with extended characters

Using Extended Characters

Extended characters are letters, numbers, or symbols that are among the last 128 characters of a character set, including characters with diacritical marks. StarQuest recommends that you avoid using extended characters in Data Replicator subscription names.


Creating a procedure for AS/400 individual member replication

Creating a Procedure for IBM i (AS/400) Individual Member Replication

To use the Data Replicator and StarSQL or later to replicate to or from individual IBM i (AS/400) file members, you must perform a one-time registration procedure on the Db2 for i database, as shown below.

CREATE PROCEDURE QSYS.QCMDEXC(IN :CMDSTR VARCHAR(1024),IN:CMDLENGTH
DECIMAL(15,5)) EXTERNAL NAME QSYS.QCMDEXC
LANGUAGE C GENERAL

You can specify a value that is larger than 1024 for VARCHAR, but do not use a smaller value.


No Replication Event Information Appears

If you select a particular subscription in the left pane of the Replicator Manager main window, but the right pane of the window does not display success or failure information for a replication event that you know has already occurred, you may be able to find information about the replication event in the Windows Application Event Log. Under some circumstances, the Data Replicator cannot record and display statistics from the control database statistics table, but can enter information in the Windows Application Event Log. If this occurs, review the events for an entry similar to "SQ_STATISTICS_FAILURE." Inspect neighboring event log entries for additional information about the success or failure of the replication event.

If all replications handled by the Replicator Service fail to record and display statistics normally, review the limits set for the statistics data (Service Properties Logging tab), or delete the statistics for one or more subscriptions. If you have SQL Enterprise Manager, you can use it to verify whether the Data Replicator control database has space available in the stats table for additional statistics.


Menu Commands

Command Reference

This section describes the commands that you can execute from the Replicator Manager main window. You can execute the frequently-used commands by clicking on the corresponding Toolbar button, or by selecting an object and right-clicking to display a context menu with commands appropriate for the selected object. Press F1 with a command highlighted to display help for that command.


About Data Replicator Manager command

About StarQuest Data Replicator Manager

Displays the StarQuest Data Replicator software version and copyright information. Note the version you are using prior to contacting StarQuest technical support regarding a software issue.


Copy/Convert Member of an Incremental Group

The Copy Member command displays the Copying a Member of an Incremental Group dialog where you can copy a member of an incremental group to another incremental group. On this dialog, you can also choose to Convert a Member of an Incremental Group to a snapshot subscription.


Delete Command

The function of the Delete command depends on the type of object that is selected when you execute the command, as described in the table below.

Object Selected | Delete Command Function
Connection | Data Replicator closes the selected connection. If the connection is pooled, it closes immediately. If the connection is in use, the Data Replicator prompts you to confirm that you want to close the connection. If you close a connection that is being used by a replication operation, the replication will fail, and the destination table may not contain the information that was present before the replication started.
Destination | Data Replicator prompts you for confirmation, then permanently removes the selected destination from the main window and closes any pooled connections between the Replicator Service and that destination. Data Replicator will not delete a destination if existing subscriptions use that destination, or if there are active connections between the Replicator Service and that destination at the time of the deletion request.
Group | Deletes the selected group from the Groups folder. A dialog appears so you can choose whether you want to delete the original member subscriptions. If you choose Do Not Delete Group Member Subscriptions, the selected group is deleted but snapshot subscriptions remain associated with the source and in any other groups in which they are a member. If you choose Delete All Group Member Subscriptions, the subscriptions also are deleted from the source they are associated with unless the subscription is a member of a different group, in which case those subscriptions are not deleted. You also can execute the Delete Statistics command to discard only the statistics related to running the group of subscriptions.
Member | Deletes the selected subscription as a member of the group. A snapshot subscription is not deleted from any other group that it is a member of or from the original source it is associated with.
Source | After you confirm you want to delete the selected source, the Replicator Manager closes any pooled connections to the source, permanently deletes any subscriptions that were defined for the source, removes any of those subscriptions that are members of a group, and removes the source from the main window. You cannot delete a source if there are active connections to it.
Subscription | To permanently remove the subscription, select it and execute the Delete command. If the subscription is a member of any group, it also is removed from the respective group(s) after you confirm you want to delete it. For a selected subscription, you can execute the Delete Statistics command if you just want to discard the replication statistics for the subscription.


Delete Statistics Command

Executing the Delete Statistics command discards all the replication statistics stored for the selected subscription or for the selected group of subscriptions. Subscriptions or groups of subscriptions that have no statistics appear with a status of None, indicating they have not yet been run, the statistics have been pruned automatically, or they have already been cleared from executing the Delete Statistics command.

If you select a subscription from the Subscriptions folder and execute the Delete Statistics command, all of the statistics for that subscription--regardless of whether the subscription was run individually or as a member of a group--are deleted from the log.

If you select a group and execute the Delete Statistics command, only the statistics that are related to running each member as part of that group are deleted. Statistics for running the subscription as part of another group or for running it as an individual subscription remain in the log.

The Data Replicator also may delete statistics for a subscription according to how the History Logging is configured for the service.


Destination Properties command

Destination Properties

The Destination Properties command displays the Destination Properties dialog so you can define or modify a destination that can be used for one or more subscriptions. Note that any change to the properties of a destination will affect all subscriptions that replicate data to that destination.


Duplicate Subscription command

Duplicate Command

Makes a copy of the selected subscription and prompts you to supply a subscription name for the copy. Although you can change Destination settings for a duplicate subscription after it is created, you should not change the logical source it is associated with. To use a different source, create a new subscription rather than duplicating an existing one.

WARNING

Except for the subscription name, the duplicate subscription is created having the same settings as the original subscription, including the Destination Table and Schedule settings. To avoid conflicts between an original subscription and its duplicate, temporarily change the original subscription properties until you can change the properties of the duplicate. To avoid inadvertent loss of data when the duplicate subscription is created, review the Destination Options in the original subscription before you create a duplicate. See Duplicate Subscription Dialog for a more detailed discussion of these issues.


Export/Import Subscriptions

This function provides a mechanism to export and import subscriptions using a text file which can be used for import on the same or another SQDR system, minimizing the need to re-enter customizations. It can be used to transfer subscriptions from a QA environment to a production environment or for transferring subscription load between servers or for support.

The text file is in JSON (JavaScript Object Notation) format, and is not intended to be edited manually.

To export a group of subscriptions, select an Incremental Group, right-click and choose Export Subscriptions. You will be prompted for a location to save the JSON text file.

To import the subscriptions, select Members under the desired destination group, right-click and select Import Members. You will be prompted for the location of the JSON file.

After selecting the JSON file, you will have the opportunity to choose the destination database and/or schema from dropdown fields. Note that not all DBMS systems support changing these properties; for example Db2 for i allows you to change the destination schema but not the database, and MySQL allows you to change the database but not the schema.

On the next dialog, you can review the subscriptions that will be imported, and you can decorate the imported subscription names with a prefix or a suffix to avoid naming conflicts; an existing group member of the same name will be replaced.


Exit Command

Selecting the File->Exit command or clicking the window Close icon shuts down the Replicator Manager application, disconnecting it from the Replicator Service. The Replicator Service is not stopped when you exit the Replicator Manager. Any scheduled subscriptions will run at the appointed time as long as the Replicator Service, and connections to the appropriate control database and source and destination tables are available.


Help Topics command

Selecting the Help Topics command from the Help menu, or clicking the Help button on Toolbar button on the Toolbar, displays the Help system for the StarQuest Data Replicator. You also can press F1 or click the Help button of dialogs to display information that pertains to that dialog, or press F1 while selecting a menu item or drop-down command. Use the Contents or Index tabs of the Help window to find specific information.


Insert Destination Command

Displays the Destination Properties dialog so you can define a new destination. After you approve the properties, the destination is added to the Destinations folder.


Insert Group Command

The Insert Group command displays the Group Properties dialog so you can define the group properties, member subscriptions, and schedule for replicating a group of snapshot subscriptions. The Insert Group command is available from the Insert menu or from the context menu when the Groups folder is selected.


Insert Group Member Command

The Insert Group Member command displays the Member Subscriptions tab of the Group Properties so you can add any of the Available Subscriptions as a member of the group. The Insert Group Member command is available from the Insert menu when a group or its Members folder is selected, or from the context menu when a Members folder is selected.


Insert Incremental Group

Incremental subscriptions typically are created using the Add Subscription Wizard, which allows you to create a new group as you define the subscriptions. However, you can execute the Insert Incremental Group command to create an incremental group. After you create the group you then use the Add Subscription Wizard and specify the existing group to add member subscriptions to it.


Insert Source Command

The Insert Source command is available from the Insert menu or from the context menu when the Sources folder is selected. The Insert Source command displays the Source Properties dialog so you can define the new source. After you approve the properties, the source is added to the Sources folder.


Insert Subscription Command

The Insert Subscription command starts the Add Subscription Wizard, which displays a series of dialogs that help you define a new subscription or group of subscriptions. The Insert Subscription command is available from the Insert menu or from the context menu whenever a source or another subscription is selected. The new subscription will be associated with the selected source or with the same source as the selected subscription.


Move Member of an Incremental Group

The Copy Member command displays the Moving a Member of an Incremental Group dialog where you can move a member of an incremental group to another incremental group.


Pause Updates Command

If you need to suspend an incremental subscription from running, such as to correct an error or hardware condition, select the group to which it belongs and execute the Pause Updates command. If you need to upgrade the SQDR Plus software, you can avoid temporary errors if you pause or stop the Incremental group and delete the connection before you upgrade the SQDR Plus host. To resume applying updates from the source to the destination select the group again and execute the Resume Updates command.

If you have a large number of incremental groups, you can pause all groups associated with a particular source or destination by selecting the desired source or destination and choosing Pause.


Properties Command

Allows you to view and modify settings for the selected service, source, destination, subscription, or group. The Properties command is available from the context menu, which appears when you right-click a selected object, or the View menu in the menu bar. You cannot view or change the properties of a subscription or a group when it is running a replication.

  • Replicator Service Properties

  • Source Properties

  • Destination Properties

  • Subscription Properties

  • Group Properties


Rename Subscription command

Rename Command

Allows you to rename an existing subscription or a group. The subscription and group names appear in the Replicator Manager main window and are stored in the Data Replicator control database.

Subscription names must be unique within the same source, and each group name must be unique within the Replicator Manager. If you change the name of a subscription, the name change also is reflected if that subscription is a member of any group. For example, if you change the name of a subscription from "sales" to "revenue" for a source named "pubs" and "sales" is a member of a group, the member name in the group also is changed from pubs/sales to pubs/revenue.


Replay Updates

Replay Updates is used to avoid new baselines in disaster recovery scenarios by replaying incremental changes after the destination table is recovered from a backup.

Example:

  1. Configure the pruneRetention parameter of the SQDR Plus Agent to a non-zero value. This value is specified in hours. Consider the overhead of the amount of data that must be saved, as this results in increased storage requirements in the control database of the SQDR Plus Staging Agent; at some point, it may be better to perform a new baseline. For this example, we will specify 24 (hours).

  2. Create an incremental subscription of a large table. The initial baseline may take a long time to complete.

  3. Set up a backup schedule for the destination; for this example, the backup will run nightly at midnight, and the group is paused during updates to avoid updates while the backup is occurring.

  4. During daily operations, the destination database fails.

  5. Pause the incremental group and recover the destination database from the previous night's backup.

  6. Select Replay Updates.

  7. In the Set Replay Position dialog, select a timestamp of 0:00 (midnight) and select OK. The incremental changes made between midnight and the current time are re-applied and the destination database will now be up to date.

  8. Resume Updates.

If you have a large number of incremental groups, you can replay all groups associated with a particular source or destination by selecting the desired source or destination and choosing Replay.


Reset I/R Group

SQDR Plus information Reset I/R Group

This action related to an incremental group allows you to reset subscriptions on the database server without having to manually remove and re-add the incremental group and its subscriptions as described in step 8 of the Tech Note SQV00PL004 Migrating SQDR Plus Subscriptions from a Test Environment to a Production Environment.

This is useful for recovery of orphaned subscriptions that have been accidentally deleted from the SQDR Plus control database on the database server, or when migrating subscriptions to a new database server.

1. Select the incremental group.

2. Choose Reset I/R Group from the File menu or right-click and Choose Reset I/R Group from the dropdown menu.

Answer Yes to the confirmation dialog.

Note that all member subscriptions will be flagged as needing to be re-baselined.


Resume Updates

Resume Updates Command

Select a paused group of incremental subscriptions and execute the Resume Updates command when you are ready to have the subscriptions resume normal operation. After all the changes that were staged while the group was paused are applied to the destination, the Data Replicator service continues monitoring the source for changes as it did prior to pausing the group.

Depending on the activity that occurs on the source while an incremental group is paused, another baseline replication may be needed to re-synchronize the destination data with the source data. You can set the group schedule to run baseline replications automatically as they are needed (when an error is encountered that flags the subscription), or execute the Run Subscription or Run Group commands to clear most warning and error conditions.

If you have a large number of incremental groups, you can resume all groups associated with a particular source or destination by selecting the desired source or destination and choosing Resume.


Run Group from this Point command

If you set the group Error Recovery property to halt a group of snapshot replications if there is an error, or an error or warning, review the replication statistics to determine the error or warning that was encountered. After you correct the problem that caused the error or warning, you can select the member of the group that caused the replications to halt and then select the Run Group from this Point command to resume replicating the group of subscriptions.

For a group of incremental subscriptions execute the Run Group command to create a baseline replication for any subscriptions in the group that have not been baselined or that have been flagged with a warning.


Run Group Command

The Run Group command starts a replication operation for the selected group. You can execute the Run Group command for any group that is not already running.

SQDR Plus information Running a group of incremental subscriptions runs a baseline replication only for subscriptions that do not have a baseline or that require synchronization of the data ( Incremental subscription with warnings warning state). Running a group of incremental subscriptions for which changes are flowing normally ( normal state of incremental replication subscription) has no affect since a new baseline replication is not needed. Select a group and then select the Run Group command from the File menu or right-click to display the context menu. If you need to force a new baseline for an incremental subscription, select the subscription for the Subscriptions folder beneath the corresponding source (Sources folder) and execute the Run Subscription command.

If a group schedule is enabled to run automatically, as described in Scheduling Replications, and the schedule is triggered when the subscription is being run on demand, the scheduled replication generates an error to the event log.


Run Subscription Command

The Run Subscription command starts a replication operation for the selected subscription. You can execute the Run Subscription command for any subscription that is not already running. Select a subscription then select the Run Subscription command from the File menu or right-click to display the context menu.

SQDR Plus information To force a new baseline replication of an incremental subscription you can select the incremental subscription from the Subscriptions folder beneath a specific source (Sources folder) and execute the Run Subscription command. Use the Run Group command if you want to perform a new baseline replication only for incremental subscriptions that do not have a baseline or that are in a warning state ( Incremental subscription with warnings).

If a subscription schedule is enabled to run automatically, as described in Scheduling Replications, and the schedule is triggered when the subscription is being run on demand, the scheduled replication generates an error to the event log.


Source Properties command

Source Properties

The Source Properties command displays the Source Properties dialog so you can view and modify settings for the selected source.


Start Service Command

Starting the Replication Manager automatically starts and connects to the Replicator Service on the local computer. The service appears in the left pane of the Replicator Manager window as the computer name, such as MYCOMPUTER.

Should you need to stop the service you can select the Start Service command when you are ready to re-start the service again. The Start Service command is available from the Service menu in the menu bar, or by selecting the computer name and right-clicking to display a context menu. You also can start and stop the Data Replicator service from the Windows Services Control Panel.


Stop Service Command

Starting the Replication Manager automatically starts and connects to the Replicator Service on the local computer. The service appears in the left pane of the Replicator Manager window as the computer name, such as MYCOMPUTER.

Should you need to stop the service, select the Stop Service command. The Stop Service command is available from the Service menu in the menu bar, or by selecting the computer name and right-clicking to display a context menu. You also can start and stop the Data Replicator service from the Windows Services Control Panel.


Stop Group Command

The Stop Group command stops a group of replications that are currently in progress. This command is available only when the group is running. If the group contains snapshot subscriptions you can resume running it by selecting the first subscription that did not run and selecting the Run Group From This Point command. If you need to pause updates for a group of incremental subscriptions, use the Pause Updates command instead of stopping the group.


Stop Subscription Command

Stops a replication currently in progress for the selected subscription. This command is available only when the subscription is running. Depending on the subscription settings in the Destination tab of the Subscription Properties, the destination table may not have the data it held before the replication was started. For example, if a snapshot subscription is defined to delete existing data before the replication begins and you stop the subscription while it is running, the existing data in the destination table will have already been deleted.


Subscription Properties command

Subscription Properties

The properties for a selected subscription are grouped with tabs that set values for related characteristics. Select a subscription, right-click, and select the Properties command to display the Properties dialog. Note that clicking OK in the Properties dialog drops and re-creates the subscription. If you only want to review the properties and do not make changes, click Cancel to dismiss the Properties dialog without re-creating the subscription.

You can create and modify the following subscription properties:

  • the source object to replicate

  • SQL criteria to use in determining which source rows to replicate

  • information about the destination and how to replicate the data

  • the CREATE statement that will be executed at the destination to create the target object

  • which source columns to replicate or omit

  • SQL processing statements that alter data in the source or destination databases, either immediately before or immediately after replication

  • which indexes and constraints that are placed on the source object that you want to replicate

  • schedule for executing the replication


View Details Command

Displays several columns of detail in the right pane of the main window. The column details vary depending on what object is selected in the left pane. The following topics describe the details that appear for:

  • the Sources folder and an individual source

  • the Subscriptions folder and an individual subscription

  • the Destinations folder and an individual destination

  • the Connections folder

  • the Groups and Members folders


View Filter Items Command

When a Members folder of a group is selected you can filter the view of the member subscriptions to see only subscriptions that have a particular status. You can also filter subscriptions by name of Subscription, Source Table, Destination Table, or Destination.

The View->Filter Items command or the Filter button Filter button on the Toolbar toggle the Filtered by Status pane on and off.

See Filtering the Subscription List for details.

By default the filter pane appears at the bottom of the subscription list pane. If you press the Shift key as you enable the filter, the Filtered by Status pane appears at the top of the subscription list.

filtered display of subscriptions

Click the status options in the Filtered by Status pane to immediately filter the list to show only the subscriptions that have that status. Subscriptions have a None status only when they have no statistics, such as when they have been added to a group but have not yet been run or statistics have been cleared (see Deleting Statistics). You can select any combination of status values to show whatever subset of subscriptions you want to see in the view. Turn off the filter to see all subscriptions regardless of their status.

To filter subscriptions by name of Subscription, Source Table, Destination Table, or Destination, click the appropriate button in the Filtered by Names area of the pane. An additional dialog will appear, allowing you to enter the desired value.


View Large Icons Command

Displays a large icon for each item shown in the right pane of the main window. Depending on what is selected in the left pane, the following objects may appear in the right pane:

Folder icon | Folders that contain definitions of sources, subscriptions, destinations, groups, or show active connections
Source or Destination icon | Sources and Destinations
Subscription icon , incremental subscription | Subscriptions (snapshot or incremental)
Group icon , incremental group | Groups of snapshot or incremental subscriptions
successful replication icon | Event that occurred from running the subscription at the indicated start time. The icon next to the time indicates whether the replication succeeded successful replication icon , failed failed replication icon , or succeeded with cautionary information qualified successful replication icon .
Connection icon | Connections

In addition, statistics for incremental subscriptions are noted in the right pane with pencil icons. See Icon Legend for a complete list of icons used in the Replicator Manager.


View List Command

Displays a small icon for each item shown in the right pane of the main window, and lists the items in a vertical column. Depending on what is selected in the left pane, the following objects may appear in the right pane:

Folder icon | Folders that contain definitions for sources, subscriptions, destinations, and groups or show active connections
Sources and Destinations | Sources and Destinations
Subscription icon , incremental subscription | Subscriptions (snapshot or incremental)
Group icon , incremental group | Groups of snapshot or incremental subscriptions
successful replication event | Event that occurred from running the subscription at the indicated start time. The icon indicates whether the replication succeeded successful replication icon , failed failed replication icon , or succeeded with cautionary information qualified successful replication icon .
Connection icon | Connections

In addition, statistics for incremental subscriptions are noted in the right pane with pencil icons. See Icon Legend for a complete list of icons used in the Replicator Manager.


Refresh Command

The Refresh command updates all visible information displayed in both panes of the main window. It does not update unexpanded branches in the left pane. These are updated when they are expanded, along with the information displayed in the right pane.

Under normal circumstances, Data Replicator automatically updates visible information for active subscriptions and connections, regardless of whether you execute the Refresh command. You can use the Update Speed command in the View menu to control how often automatic updates take place.


View Replication Event Command

The View Replication Event command shows details about an event that occurred from running a subscription or a group. To see the replication event details:

  1. In the left pane of the Replicator Manager, select the subscription or group for which you want to display the replication events.

  2. In the right pane, select the particular event you want to view, right-click and select the View Replication Event command.

The Replication Statistics dialog shows details about the event.


View Small Icons Command

Displays a small icon for each item shown in the right pane of the main window. Depending on what is selected in the left pane, the following objects may appear in the right pane:

Folder icon | Folders that contain definitions for sources, subscriptions, destinations, and groups, or show active connections
Sources and Destinations | Sources and Destinations
Subscription icon , incremental subscription | Subscriptions (snapshot or incremental)
Group icon , incremental group | Groups of snapshot or incremental subscriptions
successful replication event | Event that occurred from running the subscription at the indicated start time. The icon indicates whether the replication succeeded successful replication icon , failed failed replication icon , or succeeded with cautionary information qualified successful replication icon .
Connection icon | Connections

In addition, statistics for incremental subscriptions are noted in the right pane with pencil icons. See Icon Legend for a complete list of icons used in the Replicator Manager.


View Toolbar Command

Selecting the View Toolbar command toggles whether the Data Replicator Manager toolbar is displayed or hidden. The Toolbar provides buttons for quick access to common tasks, such as running subscriptions or groups, displaying properties of an object, and refreshing or changing the view that appears in the Replicator Manager window.

Place your mouse pointer over the buttons below or click any of the buttons to display its function.

Data Replicator Manager Toolbar


Update Speed Command

Changes how often the Replicator Manager automatically refreshes information about subscriptions and connections.

  • High updates the information approximately every 1 second.

  • Normal updates the information approximately every 3 seconds.

  • Low updates the information approximately every 30 seconds.

  • Pause disables automatic updating until you choose High, Normal, or Low. Under certain error conditions, the Replicator Manager disables updating to make it easier for you to view and clear error messages. In such a case, the Replicator Manager displays a message to inform you that updating is disabled.

You can force the information to be updated by selecting the Refresh command, clicking the Refresh button Refresh button on the toolbar, or pressing F5.


Dialogs

This section describes the dialogs that the Replicator Manager displays when you need to provide information. Click the Help button or press F1 when a dialog is active to display information about the fields and controls in the dialog.


Add Columns Dialog

The Add Columns dialog allows you to designate additional source columns to replicate. In conformance with standard SQL capabilities, you can add a column that is already designated for replication. This will cause the column to appear more than once in the destination table, providing that you specify a unique name for each column in the destination table. Unique column names within a table are required for correct operation.

The upper part of the Add Columns dialog box lists source column names and data types. If a data type has precision and scale values associated with it, these are displayed in parentheses following the data type name. To display only the source columns that are not currently selected for replication, enable the Show Unused Columns Only checkbox. To display all source columns, disable this option.

To add a column to the list of columns to be replicated, use one of the following methods:

  • Double-click the column you want to add. This method immediately adds the column to the list of columns to be replicated, while continuing to display the Add Columns dialog box. When you have finished adding columns, close the dialog box by clicking Cancel. (If you click OK to close the dialog box, any currently highlighted column will be added to the replication list, which may result in a duplicate listing.)

  • Click the first column you want to add, then use standard Windows Shift-Click or Ctrl-Click operations to select additional columns. When you have selected all the columns you want to add, click OK to add them and return to the Columns tab or corresponding Wizard screen.


Column Properties Dialog

The Column Properties dialog allows you to modify the destination column information for a source column specified in a subscription, or specify how to calculate values for inserting a derived column.

For example, if the source data is for a legacy application that uses the character data type for a date field in the format of "2005-08-27", you could change the data type to datetime if it is being replicated to a SQL Server destination. If the format of the source data does not fit the requirements for the data type you want to use, you may need to add a derived column so you can convert the data as it is replicated. See the Derived Column Syntax topic for an example of using a derived column to change the data type of replicated data.

SQDR Plus option If the subscription performs incremental replication there may be an additional, derived Relative Record Number (RRN) column for tracking changes that need to be replicated to the destination. The properties of the RRN column cannot be changed.

Property | Description
Source Column Info | If you are modifying a column, the Source Column Info displays the name and data type properties for the selected source column. If you are inserting a derived column, enter the expression to use for calculating the destination value from the source data in the Expression text box of the Source Column Info. See Derived Column Syntax for examples of defining derived columns.
Column Name | Specify a name for the destination column. If the subscription replicates data to an existing destination table, be sure the name you enter exactly matches the name of the column in the existing table.
Data Type | The drop-down list of data types reflects the data types that are recognized by the destination database. For the destination column, specify a data type that closely corresponds to the source column data type. If the subscription replicates data to an existing destination table, the destination table must already use the selected type for the specified column. For a derived column, you must specify a data type that is appropriate for the destination column. If the source and destination systems do not support the same data types, see Mapping Data Types for special considerations.
Precision and Scale | The Precision and Scale values that are valid depend on the Data Type specified for the column and the target database system. The precision and scale is fixed for some data types, and other data types have various ranges of precision and scale. The valid ranges for precision and scale also can differ among the various database systems (see Mapping Data Types). If the subscription replicates data to an existing destination table, the Precision and Scale settings must match those of the existing destination table.
Nulls | Enable (check) the Nulls option to allow the destination table to accept NULL values for the selected column. To prohibit NULL values, click to clear the check box.
Force Changes | A set of check boxes that allow changes to be made when modifying multiple columns that have dissimilar values. Up to five check boxes may be enabled, based upon whether or not the item’s value is identical or dissimilar in all of the selections. If all the values are the same, then the user may enter a new value for that attribute without further confirmation. However, if the selections currently contain dissimilar values, then the user must first check the associated check box to enable data entry in the appropriate field. After this, any change made with “OK” will be applied to all selections, regardless of the original value. The five check boxes are “Source”, “Dest.Type”, “Precision”, “Scale” and “Nulls”. Some data types do not permit the direct specification of the Precision or Scale; in those cases the Data Type will force a value, whether or not the associated Force check box is checked.

Modifying Multiple Columns: If you have selected multiple columns for modification by using the control or shift keys to select multiple list elements, then the source and destination column names are displayed as the special values of “^s” and “^d”, respectively. These are variables which will substitute the original “source” and “destination” names for each list element when OK is clicked. The variables may be combined with other text.

For example:

“RTRIM(^s)” means specify a source expression using the scalar function RTRIM on the source column denoted by “^s” and apply the change to each row in the selection.

“^d_01” will modify the destination name by adding a suffix of “_01” to the destination name.

Here you can see the effect of a prior substitution, where the prefix string “pre_” was added to some of the destination names:


Destination Properties Basic tab

Basic Destination Properties Tab

In the Basic tab of the Destination Properties dialog you define or modify a destination that can be used for one or more subscriptions. Note that any change to the properties of a destination affect all subscriptions that replicate data to the destination. Changing the DSN for an existing destination may make destination column settings invalid. To ensure correct replication after changing a destination DSN setting, rebuild the columns list and make any necessary adjustments to the destination columns settings for each subscription that uses the destination.

See Adding a Destination and Using Connection Strings for additional information.

Property

Description

Destination Name

The destination name can be a maximum of 29 characters. You cannot change the name of an existing destination.

Connection Method

ODBC DSN or ODBC Driver

Choose whether to use an ODBC data source or a DSN-less connection string.

Destination DSN

Select the data source name (DSN) for the logical destination that you are defining or modifying. The Name column displays data source names, and the Driver column displays the name of the ODBC driver associated with each DSN. The DSNs that appear depend on how the Replicator Service is configured to start. If the Replicator Service logs on as a system account, a list of system DSNs appears. If the Replicator Service logs on as a user account, the DSNs defined for the specified user account appear in addition to the system DSNs. (Use the Data Replicator Configuration program to change whether the Replicator Service logs on as a system account or a specific Windows user account.)

To choose a SQL Server destination, look for a listing where the Driver is SQL Server. To choose a Db2 destination, look for listings where the Driver is StarSQL 32 or StarSQL (64-bit). To choose an Oracle destination, look for listings where the Driver name includes Oracle and ODBC. (Driver names for Oracle vary depending on manufacturer and version.)

Destination Driver

Specifies an ODBC driver to use with a DSN-less connection string (maximus 254 characters).

ODBC Driver Connection String

Specifies an ODBC connection string

Authentication Method

Choose between Remote Credentials and Use Integrated Security. If you select Remote Credentials, you must enter a User ID and Password.

If you select Use Integrated Security, the User ID and Password fields will be disabled. You may also have to enter additional information in either the ODBC DSN or connection string e.g. Trusted_Connection=Yes for SQL Server.

User ID and Password

Specify the user ID and password that has permission to access the selected destination. This user ID will be the default owner for any new tables created at the destination by a subscription that uses this destination.

If the destination is a SQL Server system that uses Windows Only authentication, these fields appear dim because the Replicator Service automatically passes the Windows login that is specified in the Log On As section of the Data Replicator Service configuration.

If the destination is a SQL Server system that uses SQL Server and Windows authentication, you must enter the User ID and password of an account that has adequate permissions to replicate the data to the destination.

Click the Advanced tab to specify connection limits for the destination, and OK to approve the destination properties.


Advanced Destination Properties dialog

Advanced Destination Properties Tab

The Advanced tab of the Destination Properties dialog allows you to modify the Maximum Connection Pool Size and Maximum Number of Connections settings for the destination. For a destination that uses StarSQL to communicate with Replicator Manager, the AS/400 Multi Member File Access check box also appears as an advanced property.

Property

Description

Maximum Connection Pool Size

Specifies how many unused connections to leave open between the Replicator Service and the specified destination. Leaving connections open can improve performance because it eliminates the overhead used in allocating and deallocating a connection.

To specify a timeout period after which the Replicator Service will automatically close a pooled connection, click the Connection Pools tab in the Service Properties dialog and specify a Time Limit.

Maximum Number of Connections

Specifies the maximum number of connections that can be made between the Replicator Service and the selected destination. The limit applies to the combination of DSN and database user ID designated by the selected destination name. It does not affect other destinations, although they may be served by the same computer. For SQL Server destinations, the total number of connections to a single SQL Server computer may be limited to the number of SQL Server licenses you hold.

AS/400 Multi Member File Access

This checkbox appears only if the Data Replicator communicates with the selected destination by means of a StarSQL data source. If the destination database is Db2 for i and you enable this option, subscriptions that use this destination can be set to replicate to a regular SQL table or to a specific member of a multi-member physical file.

You must perform a one-time registration procedure on the IBM i Server before attempting to replicate to or from an individual member.

Defaults:

Database

Object Schema

These values are used as defaults for target database and object schema when creating a new subscription. Modifying these values do not affect existing subscriptions.


AS/400 Multi-Member Destination Options

If you are defining a subscription that replicates to a multi-member physical file on an IBM i system (AS/400), there are special implications for the Destination Options of the subscription properties. The information provided in this topic assumes that the source table type always matches the destination table type you select. If you select a source table that is a multi-member file, but select <SQL File Type> in the Member field for the destination table, only the data for the default member of the source table will be replicated. If the source is enabled for Incremental Replication you must replicate only to regular SQL files.

Create Member When Subscription Is Saved Option

  • New file: If you select Create Member When Subscription Is Saved and neither the multi-member destination file nor the specific member exists, the Data Replicator creates both the file and the member.

  • New member in existing file: If you select Create Member When Subscription Is Saved and the multi-member destination file exists but the specific member does not exist, the Data Replicator creates the member and adds it to the existing file. Previously existing data for other members is not affected.

  • Existing file, member, and table structure: If you select Create Member When Subscription Is Saved and both the multi-member file and the specific member exist, the Data Replicator asks you to confirm the operation when you attempt to save the subscription. If you confirm the operation, the Data Replicator drops and recreates the specified member for the existing file. Previously existing data for that member is discarded at that time, but previously existing data for other members is not affected. Between changes to the subscription, you can preserve replicated data for the selected member by selecting Append Replicated Rows To Existing Data. If you do not want to preserve existing data for the selected member, select Delete Existing Data Before Replication.

  • Changing from multi-member to SQL file type: If you select Create Member When Subscription Is Saved and enter the name of an existing multi-member physical table file in the Table Name field, but select <SQL File Type> in the Member field, the Data Replicator prompts you to confirm the operation and then drops the previously existing multi-member file and creates a regular SQL file.

  • Changing from SQL to multi-member file type: If you select Create Member When Subscription Is Saved and enter the name of an existing regular SQL table in the Table Name field, but do not select <SQL File Type> in the Member field, the Data Replicator reports an error when you attempt to save the subscription. Either select <SQL File Type> in the Member field or enter a different name in the Table Name field.

Re-create Member On Each Replication Option

  • New file: If you select Re-create Member On Each Replication, and if neither the destination table file nor the specific member exists, the Data Replicator creates both the file and the member when the subscription is first run.

  • New member in existing file: If you select Re-create Member On Each Replication, and if the multi-member destination table file exists but the specific member does not exist, the Data Replicator creates the member and adds it to the existing file when the subscription is first run. Previously existing data for other members is not affected.

  • Existing file, member, and table structure: If you select Re-create Member On Each Replication, and if both the multi-member table file and the specific member exist, the Data Replicator asks you to confirm the operation and then drops and recreates the specified member for the existing file when the subscription is first run. Previously existing data for that member is discarded, but previously existing data for other members is unaffected.

  • Changing table structure: To use the Data Replicator to change the table structure of an existing multi-member destination table file, you must select Re-Create Member On Each Replication and enter a SQL command (on the Processing tab or corresponding wizard screen) that drops the entire destination table before each replication.

  • Changing from multi-member to SQL file type: If you select Re-create Member On Each Replication, and if you enter the name of an existing multi-member physical table file in the Table Name field but select <SQL File Type> in the Member field, the Data Replicator prompts you to confirm the operation when you attempt to save the subscription. If you confirm the operation, the Data Replicator drops the previously existing multi-member file and creates a regular SQL file.

  • Changing from SQL to multi-member file type: If you select Re-create Member On Each Replication and enter the name of an existing regular SQL table in the Table Name field, but do not select <SQL File Type> in the Member field, the Data Replicator reports an error when you attempt to save the subscription. Either select <SQL File Type> in the Member field or enter a different name in the Table Name field.

Use Existing Member Option

  • New file: If you select Use Existing Member and the destination table file and/or the specific member do not exist, the Data Replicator displays an error message when you try to save the subscription. Either use an AS/400 utility to create the destination table file and member or choose another Destination Option for your subscription.

  • Existing file and member: If you select Use Existing Member and both the destination table file and the specific member exist, the Data Replicator uses the existing file and member at replication time. To delete previously existing data for the specified member, select Delete Existing Data Before Replication. To preserve previously existing data for that member, select Append Replicated Rows to Existing Data. Previously existing data for other members is not affected by your choice between these two options.

  • Changing from multi-member to SQL file type: If you select Use Existing Table and enter the name of an existing multi-member physical table file in the Table Name field, and you select <SQL File Type> in the Member field, the Data Replicator displays an error message when you attempt to save the subscription. The error message appears because the SQL data types specified in the modified subscription do not match the data types in the existing non-SQL multi-member table. Either select a different listing in the Member field or choose Create Member When Subscription Is Saved or Re-create Member On Each Replication.


Duplicate Subscription Dialog

Makes a copy of the selected subscription and prompts you to supply a subscription name for the copy. Although you can change Destination options for a duplicate subscription, the Duplicate command permanently assigns the duplicate subscription to the same logical source as the original. To use a different source, create a new subscription rather than duplicating an existing one.

WARNING

Except for the subscription name, duplicate snapshot subscriptions are created with the same settings as the original subscription, including the Destination and Replication options and the Schedule settings. Since some of the Destination Option settings can affect the destination table when a subscription is saved or run, and the duplicate subscription inherits the same destination options as the original subscription, you must be careful to avoid inadvertent loss of data when creating a duplicate subscription. If the original subscription uses the destination options discussed below, you may need to change the Destination Options in the original subscription before you create a duplicate subscription from it.

If the original snapshot subscription is set to create or re-create the table when the subscription is saved or a snapshot replication is run, and if the original destination table exists when you click OK in the Duplicate Subscription dialog, the Data Replicator Manager asks if you want save the subscription even though it will drop and re-create the existing destination table. If you click Yes, the destination table is dropped and re-created, but the timing for this action varies depending on the original subscription settings, as follows:

  • For a subscription that is set to create the table when the subscription is saved, the destination table is dropped and re-created as part of the process of creating the duplicate subscription. Data previously residing in the table is lost before you can modify either subscription.

  • For a subscription that is set to re-create the table when Re-Create Table On Each Replication, the destination table is dropped and re-created the next time you run the subscription. Depending on the replication schedule for the subscription, the wait until replication time can give you an opportunity to edit subscription properties so that one of the two snapshot subscriptions will use a different destination table.

If the original subscription is set to Use Existing Table, the original destination table is not affected until replication time, when its rows may be truncated, deleted, or appended to, as specified by the running subscription's other Destination Options setting. Depending on the replication schedule for the two subscriptions, the wait until replication time can give you an opportunity to edit subscription properties so that one of the two subscriptions will use a different destination table.


Invalid Subscription Warning dialog

Invalid Subscription Warning

The Invalid Subscription dialog appears if the subscription or group of subscriptions you are attempting to save is incomplete or otherwise invalid and will not replicate successfully. The scrolling text field displays an error message for each problem found.

To save the invalid subscription for future editing, click Yes to dismiss the dialog and return to the Replicator Manager window.

To edit the invalid subscription immediately, click No to return to the Subscription Properties dialogs.

To discard the invalid subscription, click No to return to the Subscription Properties dialog and then click Cancel.

If there is a problem validating a subscription that will replicate to a member of a physical file on an AS/400, you may need to cancel the Subscription Wizard and completely redefine the subscription to specify the correct member.


Multiple Subscriptions Dialog

When you create multiple subscriptions at one time and click the Finish button, the Add Subscription Wizard validates the subscriptions, displaying a dialog that describes any invalid subscription properties, and then displays a Multiple Subscriptions dialog to confirm which subscriptions were created successfully and any that were not created. Make a note of the problems and which subscriptions were invalid so you can correct the properties to replicate the intended data.


New Capture Agent Schema Dialog

Enter the name of an existing schema for the SQDR Plus Capture Agent. The schema names the control tables to use for this source and must be present at the source to run incremental replications.


New Group Dialog

The name of a group can be a maximum of 35 characters. Enter a name for the group and click OK.


Parameter Data

If you are configuring Change Data Logging on the Advanced Properties Tab of an Incremental Group, use this dialog to enter parameters that are passed to the stored procedure.

For instance, when using the Kafka Producer, this dialog is used to enter connectivity information. This may be a single line e.g. bootstrap.servers=172.19.36.175:9092, or it might be several lines long, such as a connection to Azure Event Hubs for Kafka.

The default value for the Kafka topic is the group name, with spaces replaced by underscore. You can override the default by entering the following line in the Parameters dialog:

topic=My_Topic

The use of this dialog replaces the now deprecated use of the Description field for passing parameters to the Kafka Producer.

See Using the SQDR Kafka Producer for details.


Rename Group Dialog

The Rename Group dialog contains a text edit box in which you can change the name of an existing group. The group name appears in the Replicator Manager main window and is stored in the Data Replicator control database. A group name can be up to 35 characters and must be unique within the Groups folder.


Rename Subscription dialog box

Rename Subscription Dialog

The Rename Subscription dialog contains a text edit box in which you can change the name of an existing subscription. The subscription name appears in the Replicator Manager main window and is stored in the Data Replicator control database. A subscription name can be up to 29 characters and must be unique within the subscriptions for a particular source.


Save Subscription Warning

When you define a group of subscriptions at one time, the Add Subscription Wizard displays a dialog to warn of possible data loss, such as if the specified destination table already exists and would be deleted when this subscription is run. The name of the subscription for which the warning applies appears in the title bar of the dialog.

You can choose Yes to ignore the warning for the specified subscription, Yes to All to ignore the warning for any of the subscriptions in the group, No to discard the group of subscriptions, or Cancel to cancel the save and return to the Subscription Wizard.


Service Properties dialog box

Service Properties Dialog

To display the properties for the Replicator Service, select the name of the computer that represents the service, right-click, and select the Properties command. The Properties command also is available from the View menu.

The Service Properties dialog allows you to configure the Replicator Service. You can specify:

  • Replicator (global) settings, which affect how the Replicator Service handles all replications

  • Control DB connection limits, which control how many connections can be established to the Control database and how many connections can be pooled.

  • Connection pool limits, which control how pooled connections are maintained

  • Subscription wizard settings, which affect column mapping choices made by the subscription wizard

  • Logging of replication history records and Windows events

  • Whether to send notifications regarding the status of groups to an email address

When the Data Replicator software is removed there is an option of preserving the service properties, control database, and scheduled tasks that have been specified. If the Data Replicator software is then re-installed, such as to upgrade to a new version, the service properties reflect those that were previously saved.


Service Properties Connection Pools Tab

The Connection Pools tab of the Service Properties dialog allows you to specify how long to maintain pooled connections for the Replicator Service, and how often to check the age of pooled connections.

The Connection Time Limit and Pool Inspection Interval settings work together to control how connections are allocated. A connection is closed after it has been inactive for the specified Connection Time Limit. The exact time that a connection will be closed depends on the frequency of the Pool Inspection Interval relative to the Connection Time Limit. If the pooled connections are inspected frequently, unused connections will be closed soon after they reach the Connection Time Limit. For example, if the connections are inspected every 3 minutes in a connection pool with a Connection Time Limit of 30 minutes, an inactive connection will remain open for a maximum of 33 minutes.

Connection Time Limit (minutes) | Specifies how long the Replicator Service allows each unused, pooled connection to stay open before it is automatically closed. This setting applies to all connection types. To keep all pooled connections open indefinitely, set the Connection Time Limit to 0. Changes to the Connection Time Limit setting takes effect as soon as you click OK.
Pool Inspection Interval (minutes) | Specifies how frequently the Replicator Service checks the connection pool for connections that have been inactive for the number of minutes specified for Connection Time Limit.
Login Timeout (seconds) | Specifies a timeout limit when initiating an ODBC connection. Default 30 seconds.
Packet Size (integer) | Specifies an ODBC packet size when creating a connection. Default 0 (avoid setting the property on a new connection). Example: 32767.

In situations where ODBC drivers limit the number of connections or the number of users, it is important to close unused connections quickly. If you use such a driver for the maximum number of connections between the Replicator Service and Db2, other processes on the Windows Server will be denied access to the driver as long as the Replicator Service holds those connections open. You may also encounter connection limits when using the SQL Server ODBC driver for connections between the Replicator Service and SQL Server, depending on how you have licensed SQL Server.

If you need to support more users or more connections, see the SQL Server documentation for information about obtaining additional SQL Server licenses. To increase the number of connections allowed by your StarSQL licensing agreement, contact StarQuest Ventures.


Service Properties Service Tab

The Service tab of the Service Properties dialog allows you to limit the number of connections that the Replicator Service uses to communicate with its control database. It also displays the version number of the service being managed.

If you change either of the connection settings, the changes take effect as soon as you approve the properties. However, the exact timing depends on the number of active and pooled connections currently present, and on the old and new values for both settings. If you decrease the value of Maximum Connection Pool Size, any excess pooled connections will be closed immediately. If you set Maximum Number of Connections to a value that is less than the number of connections currently in use, Data Replicator allows replications that are in progress to finish before closing their connections. Therefore, some time may pass before the actual number of open connections decreases to the new maximum value. Connection information displayed in the right pane of the main window reflects this situation.

Maximum Number of Connections | Sets the maximum number of connections between the Replicator Service and the Control database. This limit applies only to connections between the Replicator Service and the control tables. It does not limit the number of connections that are allowed between the Replicator Service and sources or destinations residing on the same computer as the control tables. The Maximum Number of Connections must be greater than or equal to the Maximum Connection Pool Size.
Maximum Pooled Connections | Sets the maximum number of unused connections that are kept open between the Replicator Service and the Control database. Leaving a connection open can improve performance because it eliminates the overhead of allocating and deallocating a connection. If you want to specify a timeout period after which the Replicator service will automatically close a pooled connection, specify a Time Limit on the Service Properties Connection Pools Tab.
Replication Service Version | Displays the version of the Replication Service being managed.

See Also

Managing Control Database Connections

Advanced Destination Properties Dialog

Advanced Source Properties Dialog


Service Properties DBCS Tab

Service Properties Subscription (Wizard) Tab

The option on this dialog are used only when creating or rebuilding a subscription.

This dialog appears in the following scenarios:

  • When you select the Service properties (right-click on the server name, select Properties and select the Subscription Wizard Tab). The options selected are saved and affect future subscriptions or future changes to existing subscriptions; modifying the values does not affect existing subscriptions. Some options (noted below) are available only in this view.

  • When you select Mapping Options on the Columns tab of the Subscription Wizard. In this case, the options selected are used only when rebuilding the column mappings of the current subscription; select Rebuild Columns to view the results of using these options.

Character data destination precision heuristic

The Character data destination precision heuristic of the Service Properties dialog is useful if you wish to customize the handling of CHAR and VARCHAR columns to avoid loss of data (truncation) when the database encoding of the destination database differs from the source. Some examples are replication from an EBCDIC SBCS (single byte character set) database to a Unicode database (UTF-8 encoding may require up to three bytes for a single character), or replication to or from a DBCS (double-byte) database configured to support Chinese, Japanese, or Korean ANSI code pages (code pages 932, 936, 949, and 950).

For instance, when replicating from a non-Unicode Db2 for i source to a Db2 for LUW UTF-8 destination, you may encounter the following error for certain character data:

SQLSTATE 22001, native error -302, The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.

This error occurs when some characters are converted into multi-byte characters and the size of the target table's character column is inadequate. The solution to this issue is to edit the column mappings in the affected subscription and increase the size of the affected columns on the target table.

The options in this tab allow you to automatically increase the default Precision value for destination columns of data type CHAR or VARCHAR.

Choose one of the following options for Character data destination precision heuristic:

  • Use source precision (no Inflation) This is the default and appropriate if your data contains only single-byte character sets (SBCS), or if you wish to manually adjust the size of destination character fields.

  • Inflate VARCHAR type

  • Inflate both VARCHAR and CHAR types

If you select an Inflation option, use the Inflation Percentage field to specify the increase in size (in percentage) of the destination column. For example, specifying 10% will result in a CHAR(10) source field mapping to CHAR(11) destination. The inflation percentage is rounded to the nearest integer value. If the result is the same as the original precision, a value of 1 is added. The maximum value is 200%, which increases the destination field by three times; this is sufficient to handle all worst-case expansion scenarios.

The inflation is limited by the maximum precision allowed for a data type on the destination. For instance, if a source column is defined as CHAR(255) and 255 is the maximum for a CHAR on the destination, the size will not be changed.

Inflation will not occur if the Promote CHAR types to WCHAR equivalents is selected.

Inflation will not change the destination type; i.e. CHAR will not be converted to VARCHAR if inflation would exceed the CHAR maximum. If type conversion is acceptable, consider using the Promote CHAR to VARCHAR option.

Data types such as NCHAR and NVARCHAR will map 1-1 to GRAPHIC and VARGRAPHIC types with respect to character precision - i.e. NCHAR(10) -> GRAPHIC(10); both will use 2 bytes of storage. If support for these data types is reported by the destination ODBC driver, SQDR will map to these types and no inflation is required.

Mapping to other national character types, such as nchar and nvarchar (used by SQL Server) will not require inflation, but may over-allocate storage when replicating from UTF-8 to UCS2.

Additional Properties

Option

Description

Create LOB columns as not logged

Certain target systems (e.g. Db2 for Linux, UNIX, & Windows 9.5 and earlier) require that LOB columns > 2mb be created as "NOT LOGGED". Enabling this option will instruct the SQDR subscription wizard to generate a CREATE TABLE statement with LOB columns defined as "NOT LOGGED". This option is global and will affect subscriptions being created for other targets, where this syntax may be illegal. We recommend enabling this option when needed (creating a subscription containing LOB columns to a Db2 UDB LUW database target) and reverting to the default (disabled) after creating the subscription.

Default: Disabled

Service Properties Subscription Wizard Tab only

Default CHAR and VARCHAR as Nullable

This option causes the "Nullable" check box to be automatically selected for any newly-added CHAR and VARCHAR columns. This facilitates replication from DBMS systems that permit an non-NULL empty character string to Oracle and other DBMS systems that differ from the ANSI 92 standard and consider an empty string as a NULL value.

Default: Disabled

Promote CHAR to VARCHAR

This option eliminates the use of a fixed precision character column type in favor of a variable character type.

Default: Disabled

Promote CHAR types to WCHAR equivalents

This option instructs the Column wizard to promote CHAR types to Unicode equivalents (WCHAR) when selecting a destination column type. See also the runtime option Bind Character Types as Wide (Unicode) on the Replication Tab.

Quote SQL Identifiers

Certain character combinations and words are normally forbidden or have reserved meanings in SQL statements. For instance, SQL does not normally allow creating a table named TABLE or including a space in a column name. If you want to use words or character combinations that are reserved or forbidden by SQL in one or more subscriptions, make sure that the Quote SQL Identifiers check box is selected before you save the subscription.

To have subscriptions comply with the regular SQL restrictions, clear the Quote SQL Identifiers check box before you create and save subscriptions.

Default: Enabled

Service Properties Subscription Wizard Tab only

Fold case of identifiers per destination default

This option controls the behavior of the Subscription Wizard when creating new subscriptions or when editing the properties of an existing subscription.

SQDR will fold the destination identifier case to match the preferred case of the destination DBMS, when that DBMS specifies either SQL_IC_UPPER or SQL_IC_LOWER. In other cases (SQL_IC_MIXED or SQL_IC_SENSITIVE), no folding ever occurs.

Disabling the Fold option will preserve the case of the identifiers on the destination as specified on the source table in all cases. Enabling the Fold option preserves the customary behavior.

This setting does not change existing subscriptions unless a column rebuild occurs.

This option appears in several places:

  • Service Properties/Subscriptions Tab

  • Destination panel of subscription wizard

  • Mapping options at the column tab of an individual subscription

For instance, unchecking Fold Identifiers permits a subscription from a lower case source such as informix to create objects with lower case identifiers on a destination whose preferred case is Upper (e.g. Exasol). Note that Quoted Identifiers must also be enabled in such as case (default behavior).

Map NUMERIC(p,0) to INTEGER types

Select this option to map decimal(p,0) or numeric(p,0) to binary equivalents (int, bigint), which are typically more efficient in terms of storage and processing. Large precisions (>9) are mapped to BIGINT and very large precisions (> 18) are not converted.

Examples:

DECIMAL(9,0)->INTEGER

DECIMAL(10,0)->BIGINT

NUMERIC(19,0)->NUMERIC(19,0)

Note: this may not be desirable when mapping to a destination such as Db2 that has natural support for DECIMAL and NUMERIC.

Default: Disabled

Enable deprecated types

Many ODBC drivers report support for deprecated data types. For example, Microsoft SQL Server reports a type of datetime which has significant restrictions (range and precision) as compared to the newer type datetime2. Similarly, text, ntext and image have been replaced by varchar(max), nvarchar(max) and varbinary(max).

To avoid conversion issues, using the modern data types is recommended. The default column mapping behavior of the SQDR subscription wizard is to display only the newer data types. Currently, the cloaking of deprecated types is implemented for SQL Server only.

Select this checkbox to allow mapping to the deprecated types.

Default: Disabled

Service Properties Subscription Wizard Tab only


Service Properties Global Tab

Service Properties Replication (Run) Tab

The Replication (Run) tab (formerly Global tab) of the Service Properties dialog lets you adjust several Replicator Service settings that control how the service handles all replications.

Changes to the Service Properties affect any replication that starts after you click OK to approve the new properties. If a replication is in progress when you approve changes to the Service Properties, the subscription or group runs according to the service properties that were in effect when the replication started. It is not necessary to restart the SQDR service.

Option

Description

Mutithread Timeout (seconds)

Adjusts the amount of time that the Replicator Service waits for a fetch or insert buffer when the Multithread Fetch and Insert option is enabled. The minimum value for this setting is the default setting of 30 seconds, which is appropriate for most replications. If a replication fails with a message indicating that the Replicator Service timed out while waiting for a fetch or insert buffer, increase the value of this setting and re-run the subscription.

ODBC Commit Interval (rows)

For replication operations that use the ODBC insert method, the ODBC Commit Interval setting affects how many rows of replicated data are committed (saved) to the destination table at one time. In the event of a replication failure, uncommitted data can be rolled back, but committed data cannot. If the subscription involves automatic processing on the source before replication, that processing can be rolled back only if no replicated data has been committed and if the Replicator Service has the Use Single Transaction Control for Source Processing option enabled.

An ODBC Commit Interval of 0 directs Data Replicator to commit all rows when it reaches the end of the table.

You also can specify another value to indicate the approximate number of rows that you want to commit at one time. (The exact number of rows that are committed may vary from the Commit Interval value due to multi-fetching, which fetches multiple rows at one time.) If you are replicating a large amount of data, you can free up resources at the destination by periodically committing rows of the table rather than waiting for the last row. For example, if the replication involves inserting one million rows and you set the ODBC Commit Interval to 1000, the destination database only needs resources to track 1000 rows instead of a million rows in case there is a rollback operation.

BCP Commit Interval (rows)

For replication operations that use the Native Loader function (e.g. SQL Server Bulk Copy insert method), the BCP Commit Interval setting determines how many rows of replicated data are committed (saved) to the destination table at one time.

A value of 0 directs Data Replicator to commit all rows when it reaches the end of the table. You also can specify another value to indicate the approximate number of rows that you want to manually commit before the end of the table is reached. (The exact number of rows that are committed may vary from the Commit Interval value due to multi-fetching, which fetches multiple rows at one time.)

Indeterminable Size Column Limit (KB)

This setting applies to columns with data types LONG and LONG RAW on Oracle sources. Before replicating large data types, the Data Replicator attempts to determine how much data must be held in memory during the replication. If the data exceeds the column limit but the precision of the target column is smaller than the limit, the data is truncated during replication and a warning event is logged. If the target column does not limit the data precision, the replication will fail until you increase the Indeterminable Size Column Limit setting or decrease the precision for the target column.

This value is also used to allocate the maximum buffer associated with LOB data for Apply.

The default size for a column that contains data of indeterminable size is 1000KB (1MB). Values greater than 16KB are automatically rounded up to the next multiple of 16KB upon approving the service properties.

Replication Buffer Size (KB)

Default value is 4MB, which should provide optimal performance when the Multithread and Multirow Fetch and Insert options are enabled. Decrease this value only if memory consumption is an issue, or to resolve driver-specific issues. For instance, it may be necessary to reduce this value to 64KB when using Bind Character Types as Wide (Unicode) when the destination is using the Oracle ODBC driver (but not the SQDR Oracle driver or Progress DataDirect ODBC driver for Oracle).

If you are updating from an older version of SQDR, this value may be set to 512KB; we recommend updating it to the new default.

Use Single Transaction Control for Source Processing

StarQuest Data Replicator lets you specify automatic SQL processing on both the source and destination objects if you are performing a snapshot replication. If a replication operation that includes automatic SQL processing fails, the Use Transaction Control for Source Processing option determines whether source processing operations will be rolled back. To roll back source processing in the event of a replication failure, enable the check box. To retain the results of source processing even if the replication fails, clear the check box.

Bind Character Types as Wide (Unicode)

The default behavior of SQDR is to bind all character types as SQL_C_WCHAR. When this option is disabled, the service will bind all character types using SQL_C_CHAR. This option affects runtime behavior; also see the Subscription option Promote CHAR types to Unicode equivalents, which affects the Subscription Wizard.

Use Multithread Fetch and Insert

This option directs the Replicator Service to use separate execution threads for fetch and insert operations. When this option is enabled, fetch operations for each replication use an execution thread that is separate from the execution thread for insert operations, and fetch and insert operations are processed simultaneously. This option may allow faster replication, depending on the number of columns and the row length of the tables. Default is Enabled.

Use Multirow Fetch and Insert

This option enables multirow fetch and insert, which delivers enhanced performance when supported by the source and destination ODBC drivers. Default is Enabled.

Strip trailing blanks (RTRIM) for VARCHAR

This option will strip trailing blanks from VARCHAR columns during replication. This setting affects only snapshot (baseline) behavior. See the advanced setting padVarchar for the setting that affects incremental changes.

Incremental Group
DDL Replication Defaults

  • DDL Replication

  • ADD

  • DROP

  • MODIFY

These settings determine the default behavior when an ALTER occurs on the source table. These values can be overridden for an incremental group in the Advanced Tab of the I/R group; see ALTER Processing for details.

If DDL Replication is set to Automatic, SQDR will automatically perform the DDL change to the destination table for supported DBMS systems; if On Demand is selected, manual DDL replication (by selecting Run for the incremental group or subscription) is required. If Off is selected, then behavior and resolution match that of SQDR pre-5.10: edit the subscription and select Merge with Source on the Column panel, and then Run the group.

If ADD is set to Perform, SQDR will automatically apply the ALTER ADD to the destination table; if it is set to Ignore, it will ignore the change.

If DROP is set to Perform, SQDR will automatically apply the ALTER DROP to the destination table; if it is set to Ignore, it will ignore the change.

If MODIFY is set to Perform, SQDR will automatically apply the ALTER COLUMN operation (for change of datatype, datatype name, precision, scale, or nullability) to the destination table; if it is set to Ignore, it will ignore the change.

DDL Replication determines when the subscription is run in reaction to a source ALTER, while ADD, DROP, and MODIFY determine what occurs.

Change Data Logging Defaults

These settings determine the default behavior for Change Data Logging. Typical applications are the SQDR Kafka Producer or the SQLOGGER stored procedure. These values can be overridden for an incremental group in the Advanced Tab of the I/R group; see Advanced Tab for Groups of Incremental Subscriptions for details.

Before Image included determines how the Before Image information is delivered:

  • Separately (behavior of SQDR prior to v5.08): the Before Image is a separately logged row.

  • Combined: the Before Image is appended to the After Image data.


Service Properties Logging Tab

The Logging tab of the Service Properties dialog allows you to control the logging of replication history records and of Windows events.

History Logging

The Data Replicator Control database contains statistics for all the replication events that are handled by the Replicator Service. For best performance, keep as few records in the database as meets your requirements.

The Limit Replication History Logging option determines whether replication history records are deleted when a certain number of records is reached. If you enable the Limit Replication History Logging option, set the Maximum Total Number of History Records to the maximum number of history records you want to store in the Control database. The default value for this setting is 1000 records. To keep the replication history records indefinitely, disable (uncheck) the Limit Replication History Logging option.

When the number of rows in the Control database reaches the Maximum Total Number of History Records value, the Replicator Service deletes as many records in the history table as needed to allow new replication entries to be inserted. Which records are deleted depends on which subscriptions the new log entries are for, and how many log entries for those subscriptions already exist in the Control database. If adding the new subscription log entries would exceed the value set for Maximum Records Per Subscription, the oldest records for those subscriptions are deleted. If adding the new subscription log entries does not exceed the limit for the subscriptions, the oldest records in the history table are deleted to make room for the new records.

A change to the log size setting generally takes effect as soon as you click OK. However, if you decrease the value of the setting, excess table rows will not be deleted until the next time replication event information is inserted into the Control database. The topic Viewing Statistics describes how to view the replication history for a subscription, and Deleting Statistics describes how to clear the history records for a particular subscription or group.

Windows Event Logging

The default option for logging Windows events is to record the Normal Operational Level of Detail, such as when the service is started and stopped. If you experience a problem with data replications you can enable the Expanded or Maximum levels of logging to capture the following types of ODBC messages in addition to the operational details.

Expanded Warning Logging | Logs ODBC error messages as WARNING events. ODBC errors messages can help troubleshoot insert failures, connect failures, or problems dropping or creating indexes.
Maximum Level of Warning | Logs ODBC information or warning status messages as INFORMATION events. ODBC information and warning messages reflect events such as database context changes, setting the default language, committing transactions, and binding packages when connections are established.

The topic Viewing Windows Events describes how to view the Windows events that are logged. Remember to change the Data Replicator service logging level back to Normal Operational Level of Detail when you are finished troubleshooting a problem.

Event Source Filter

These options can be used to configure detailed customization of which classes of events will result in an Event Log message and/or email notification.

The available options are:

Error (Errors only)
Warn (Errors & Warnings)
Info (All)
None

EVENT CLASS | Default Value
Service Control | Error
Service Configuration | Error
Apply Manager | Error
Replicator Manager | Error
Schedule Manager | Info
Pool Manager | Info
License Manager | Warn
Network | Info
Diagnostic | None


Service Properties Notification Tab

The Notification tab of the Service Properties dialog allows you to send a notification to an email address when a group of subscriptions completes, encounters errors, or encounters errors or warnings. The SMTP mail server must be able to direct email to the user account specified in the “To” parameter and accept any value as the sender.

Enabling and Disabling Email Notifications

Complete the Email Properties to enable sending notifications from the SQDR Service to an email address. If you need to disable this feature clear one of the fields, such as the From field. The notification feature and the Test button are enabled only when all the fields have values.

Important: Restart the SQDR service after modifying the Email Properties.

Email Properties

Complete the following fields to enable the email notification feature. The SQDR Service automatically adds a Subject line to each email that identifies the severity of the notification: SQDR Information Notification, SQDR Warning Notification, or SQDR Error Notification.

To | Specify a valid email address, in the form of name@domain.com, in the To field. The To address is the one that will receive the email notification.
From | The From field can contain any character string in the form of a valid email address. The text that you enter appears in the From field of the notification email. If one person will receive SQDR notifications from multiple computers, you can use the From field to identify the process and computer from which the notification is sent, such as SQDR_COMPUTERNAME@domain.com. If you are sending the notifications to another person you may want to enter your own email address in the From field, in the form of yourname@domain.com, to help identify the origin of the email and provide a valid address for return communications.
SMTP Server | Specify the Internet address of an SMTP server that provides outgoing email service. An Internet address can be an IPv4 address, an IPv6 address, or a DNS domain name. If you are using an SMTP server that requires authentication or specific encryption settings, set the notifyUserID, notifyPassword & notifyMailEncryption advanced settings.
Port | Specify the number of the port on which the SMTP server is listening for sendmail requests. The default port number is 25.

Click the Test button to send a test email to the specified account, and click OK to save the service notification properties. If you want to discontinue sending notifications to email, clear one of the Email Properties fields and save the service properties again.

Message Types

In the Message Types pane you select which type of messages you want to receive. You can choose one of the following message categories, with All being the most inclusive.

All | Sends an email when a group of subscriptions completes successfully or encounters warnings or errors. For groups of subscriptions that perform incremental replication, notifications also are sent if there are errors polling the source, if the subscription is paused, and when incremental counters are reset.
Warning and Errors | Sends an email if a group of subscriptions encounters a warning or error.
Errors Only | Sends an email if a group of subscriptions encounters an error.

Office365 Hints

To send email using Office365, use one of the following configurations:

Direct Send

To use Direct Send (typically used for printers, scanners, other devices, and applications):

SMTP server: your-domain.mail.protection.outlook.com (the MX endpoint for your Office 365 tenant)
Port: 25
notifyMailEncryption: 2 (TLS only)

Advantages:

  • This method does not require authentication.

  • The From address can be any meaningful name as long as it is properly formatted e.g. sqdr@mydomain.com. It does not have to be a registered/licensed Office365 user.

Disadvantages:

  • You can't relay to an email address outside your domain.

  • Your IP address has to be white-listed, which adds complications when SQDR is running in the cloud (e.g. AWS or Azure).

SMTP client submission endpoint

Use SMTP client submission endpoint (this is typically used by email client applications like Outlook) to send email to an address outside your domain:

SMTP server: smtp.office365.com
Port: 587
notifyMailEncryption: 2 (TLS only)

You must configure authentication (mailUser and mailPassword). See Advanced Settings.

The value specified for mailUser must match NotifyFromAddress. This means using a registered/licensed Office365 user, and the NotifyFromAddress address may not be as descriptive as it could be otherwise, especially if you are using multiple SQDR servers.

When using this method, the Test Button will generate a misleading error message (which is still successfully delivered as an email); however, the desired messages from the service itself will be delivered without error.

Office 365 SMTP relay connector

Configure a Office 365 SMTP relay connector to authenticates your device or application with Office 365 using an IP address or a certificate.

The Office 365 administrator creates an SMTP relay connector in the Exchange admin center, specifying Your organization's email server in the From dropdown, and supplying your static IP address.

Configure SQDR as above for Direct Send:

SMTP server: your-domain.mail.protection.outlook.com (the MX endpoint for your Office 365 tenant)
Port: 25
notifyMailEncryption: 2 (TLS only)

Advantages:

  • This method does not require authentication.

  • The From address can be any meaningful name as long as it is properly formatted e.g. sqdr@mydomain.com. It does not have to be a registered/licensed Office365 user.

  • You can relay to an email address outside your domain.

Disadvantages:

  • Your Office365 administrator must create the connector.

  • You must use a static IP address that belongs to your organization exclusively.

  • The connector could potentially be abused by other devices and computers within your network. For example, a compromised desktop computer within your network could use the connector to send spam to external addresses.

For detail, see the Microsoft documentation.


Set Replay Position

Set Replay Position Dialog

Specify one of the following:

  • Timestamp (Point in Time)

  • Transaction Identifier (entered as a hexadecimal constant using 0-9 and A-F)

  • Earliest Position Available

See Replay Updates for more information.


Source Properties: Advanced tab

Advanced Source Properties Tab

The Advanced tab of the Source Properties dialog allows you to control Replicator Service connections to the source. For a source that uses StarSQL to communicate with the Replicator Service, an option also appears for AS/400 Multi Member File Access.

SQDR Plus information If the source is enabled with SQDR Plus you also can specify in the Advanced properties whether the source can be used for incremental replication subscriptions. Refer to the topic Configuring a Source for Incremental Replication for additional details.

Property

Description

Maximum Connection Pool Size

Specifies how many unused connections to leave open between the current Replicator Service and the selected source. A value of 0 closes all unused connections. Depending on your network traffic conditions and replication needs, you may be able to improve performance by increasing the number of connections, since the overhead of allocating and deallocating a connection is eliminated each time a pooled connection is used.

You can specify a timeout period after which the Replicator Service automatically closes a pooled connection. See the Service Properties Connection Pools tab for more information.

Maximum Number of Connections

Specifies the maximum number of connections that can be made between the Replicator Service and the selected source. The limit applies to the combination of DSN and database user ID designated by the selected source name. It does not affect other sources, although they may be served by the same Db2, Oracle, or SQL Server.

AS/400 Multi Member File Access

This checkbox appears only if the Data Replicator communicates with the selected Db2 for i (AS/400) source using a StarSQL data source. If you enable this option, any subscription that uses this source can be set to replicate to a regular SQL table or to a specific member of a multi-member physical file. If this option is disabled and you use this source to replicate from a multi-member file, only the data from the default member of the file will be replicated.

To perform incremental replication from a multi-member file, you must use SQDR Plus v4 or later and enable the multiMember property in the SQDR Plus configuration (default is enabled).

You must perform a one-time registration procedure on the IBM i Server before attempting to replicate to or from an individual member of a file.

Defaults:

Database

Object Schema

These values are used as defaults for source database and object schema when creating a new subscription. Modifying these values do not affect existing subscriptions.

SQDR Plus information Allow Incremental Replication

If the source is enabled with SQDR Plus, the Allow Incremental Replication from this Source option is available. If you enable the Allow Incremental Replication option, also specify the name of the Capture Agent schema to use.

SQDR Plus information Capture Agent Schema

The Capture Agent Schema names the control tables that are used by a particular instance of the SQDR Plus Capture Agent. If the source has more than one Capture Agent schema defined, select which one you want to use for this instance of the source.

Important Information Changing the Capture Agent schema after you save the source properties resets all the groups and subscriptions that use the source for incremental replication operations; a new baseline will be required.

SQDR Plus information Notification Address

The drop-down list for Notification Address shows the IP address of all active network connections. Select the IP address, or enter the address or hostname, of the computer that you want to receive a UDP notification when changes are committed to this source. This should be a static IP address. Select Disable if you want to receive changes only with the normal polling cycle. If you disable notifications for a source that is defined in a group that is set to receive notifications, the group property will be toggled to disable receipt of change data notifications.


Source Properties: Basic Tab

Basic Source Properties Tab

The Basic tab of the Source Properties dialog allows you to define a source or modify its settings. See Adding a Replication Source and Using Connection Strings for additional information.

Property

Description

Source Name

The source name can be a maximum of 29 characters. You cannot change the name of an existing source.

Connection Method

ODBC DSN or ODBC Driver

Choose whether to use an ODBC data source or a DSN-less connection string.

Source DSN

Select the data source name (DSN) for the source you are adding or modifying. The Name column displays data source names, and the Driver column displays the name of the ODBC driver associated with each DSN. Driver names vary depending on the manufacturer and version. For a SQL Server source, look for a DSN entry that uses the Driver SQL Server. For a Db2 source, look for a Driver of StarSQL 32 or StarSQL (64-bit). For an Oracle source, look for a Driver name that includes ODBC or Oracle.

The DSNs that appear depend on how the Replicator Service is configured to start. If the Replicator Service logs on as a system account, a list of system DSNs appears. If the Replicator Service logs on as a user account, the DSNs defined for the specified user account appear in addition to the system DSNs. (You specify whether the Replicator Service logs on as a system account or a specific Windows user account when you configure the service.)

Source Driver

Specifies an ODBC driver to use with a DSN-less connection string.

ODBC Driver Connection String

Specifies an ODBC connection string (maximus 254 characters)

Authentication Method

Choose between Remote Credentials and Use Integrated Security. If you select Remote Credentials, you must enter a User ID and Password.

If you select Use Integrated Security, the User ID and Password fields will be disabled. You may also have to enter additional information in either the ODBC DSN or connection string e.g. Trusted_Connection=Yes for SQL Server.

User ID
and
Password

Enter the user ID and password that allows the Replicator Service to log in and copy data from the current source. If the subscription is defined to perform SQL processing on the source data, the user ID also must have permissions that allow those operations.

If the source is a SQL Server system that uses Windows Only authentication, these fields appear dim because the Replicator Service automatically passes the Windows login that is specified in the Log On As section of the Data Replicator Service configuration.

If the source is a SQL Server system that uses SQL Server and Windows (Mixed Mode) authentication, you must enter the Windows login that the Replicator Service is configured to use.

Click the Advanced tab to display the Advanced Source Properties. The advanced properties allow you to change the connection limits and, if the source is enabled with SQDR Plus, configure it for incremental replication.


Task Schedule Dialog

Use the Task Schedule dialog to set up a recurring schedule for running a replication subscription. For snapshot replications the schedule determines when the replication operation is run. For incremental subscriptions the schedule determines when the Data Replicator checks to determine whether a baseline replication must be performed for any subscription in the group.

Occurs

In the Occurs frame, select the Daily, Weekly, or Monthly option to set the frequency at which the replication task will run. The right frame displays prompts that are appropriate to the option you select:

  • If you select the Daily option for Occurs, in the Daily pane specify the frequency in days for the task to be run. For example, enter 1 to run the subscription every day, or 3 to run the subscription every three days.

  • If you select the Weekly option, in the Weekly pane specify the frequency in weeks and select which days of the week to run the subscription. For example, enter 2 and select Fri to run the replication task every two weeks on Friday.

  • If you select the Monthly option, in the Monthly pane specify on which day(s) of the month(s) that you want to run the subscription. You can schedule the replication based on a specific day of the month, or you can schedule it based on a specific day of the week or a specific week of the month. For example, to run the replication task on the fifth day of every second month, enter Day 5 of Every 2 Month(s). To run the replication task on the fourth Friday of every third month, select The 4th Friday of Every 3 Month(s). To run the replication task on the last day of any month, specify the 31st day of each month, the scheduler will automatically adjust this to the "last day" of a month that has only 30, 29, or 28 days.

Daily Frequency

The Daily Frequency frame specifies the time or times of day that you want the subscription to run. To run the replication task once on each designated day, select Occurs Once at and enter the time to run the replication task in the adjacent edit field. To run the replication task at regular intervals during a range of hours on each designated day, select Occurs Every and specify the interval for running the task and a starting and ending time. For example, to run the replication task every 30 minutes between 9:00 a.m. and 6:00 p.m., enter Occurs Every 30 Minute(s) Starting at 9:00AM Ending at 6:00PM.

Duration

The Duration frame specifies starting and ending dates for the recurring schedule. Enter the date that you want to start running the subscription in the Start Date field. To specify a date after which the subscription will not run, click End Date and type the date in the adjacent edit field. To allow the subscription to run at the scheduled interval until you either change its schedule or delete it, click No End Date. Choose start and end dates that are compatible with the designated days, weeks, or months to run the subscription. For example, if a subscription is set to run every Thursday, setting the start date to a Friday would delay the first replication until the following Thursday. Similarly, if a subscription is set to run on the second day of every month, setting an end date of 11/01/2008 would cause the last replication to occur on 10/02/2008.


Change Data Processing

In addition to, or instead of, updating a destination table, SQDR can be configured to invoke a user-supplied stored procedure to process the Change Data information for purposes of auditing or "push" notifications. The stored procedure can reside either in the SQDR control database or the destination database, and can be used to perform a business process task such as inserting a message into a message system such as IBM WebSphere MQ or Microsoft Message Queuing (MSMQ), sending an email, or logging changes in a database table for auditing purposes.

The StarQuest-supplied SQL stored procedure SQLOGGER (dbo.SQLOGGER for SQL Server and SQDR.SQLOGGER for Db2 LUW) can be used as a model for your own stored procedure. SQLOGGER resides in the SQDR control database and is used to populate the ir_keylog control table, which is a log of the communication between the SQDR Plus system and the client system (running SQDR) as it relates to the application of changes to the destination table (DDL and DML).

Another stored procedure example illustrates the use of a Java stored procedure to write change data to a text file - see Change Data Processing - Java example.

The stored procedure is configured in the Group Properties Advanced tab; you can choose whether to receive complete data (with the exception of large LOB fields) or just the keys associated with the change. If Errors-Only is selected, then data is provided when an error occurs.

Change Data Processing is in addition to other Apply functions (i.e. maintaining the destination tables with change data).

In addition, see the script IR_Keylog.sql in the Tools subdirectory and the following technical documents:

  • Using SQDR Change Data Processing

  • Change Data Processing - Java example

  • Using SQDR Key Logging Data

  • SQDR ir_keylog Table

  • Using the SQDR Kafka Producer


SQL Scripts

The Tools subdirectory contains several SQL scripts that can be used to gather statistics reports from the SQDR control database. Usage is documented in the scripts themselves.

These scripts should be copied and used as templates for any additional customization that you may require; note that they may be replaced by future SQDR updates.

For a control database residing in Db2 for LUW, use the scripts in Tools\db2luw. You will need to first connect to the database SQDRC. The scripts assume that the control tables are in the schema SQDR. The scripts can be run in IBM Data Studio or another tool, including the DB2 command line processor.

For a control database residing in Microsoft SQL Server, use the scripts in Tools\mssql. All scripts assume that the control database is named ControlDB; you will need to modify the scripts if your control database has another name. The scripts can be run in SQL Server management Studio or another tool, including command line tools such as sqlcmd.

IR_Keylog.sql - analyze ir_keylog contents. See the technical documents Using SQDR Key Logging Data and SQDR ir_keylog Table for more information.

TargetChecker.sql - reports discrepancy results of TargetChecker. See TargetChecker for more information.

IR_Group.sql - Illustrates ir_group name control table, providing decoding of I/R Group default options. IR_Group507.sql is provided for compatibility with control databases running on older DBMS versions that do not support JSON encoding (SQL Server prior to 2016 and Db2 LUW prior to 11.1).

IR_GroupStats.sql - reports I/R Group activity.

IR_Subscription.sql - display incremental subscriptions, providing decoding of I/R Subscription options.

IR_SubscriptionStats.sql - reports ranked I/R Subscription activity.

Statistics.sql - reports replication baseline results.


TargetChecker

SQDR Plus information TargetChecker

TargetChecker.vbs:

To perform table validation (comparing row counts between the source and destination tables of an incremental group), the use of the COM interface is recommended. A sample VBScript application TargetChecker.vbs is supplied in the Program directory. This method works only with SQDR Plus v4; see TargetChecker Classic below for use with SQDR Plus v3.

If you get the error "Error 80070005: Access is denied.", run this script from an elevated command prompt.

Usage:

C> cscript TargetChecker.vbs [group subscription]

You can use SQL wildcard characters for the group and subscription parameters; If you do not supply any parameters, the SQL wildcard character % is used for both group and subscription.

For instance, the following command will check all subscriptions in the group.

C> cscript TargetChecker.vbs mygroup %

Results are written to standard output and can be redirected to a file. The results are tab-delimited and can be imported into Excel.

TargetChecker.sql:

This Query displays the results of the TargetChecker.vbs script. Usage is documented in the SQL script itself.

TargetChecker Classic:

The Java-based TargetChecker, in the Tools subdirectory of the SQDR installation, is an administrative tool that compares row counts between the source and destination tables of an incremental group. It should be run on the SQL Server system where SQDR resides, and works only with SQDR Plus v3.

Make sure that this system has Java installed (from a command window enter “java –version). If not, download and install the Java run time from http://www.java.com. If StarAdmin or Db2 is installed on the machine, the included batch file will attempt to use the JVM supplied by those applications.

  1. Copy the contents of C:\Program Files\StarQuest\SQDR\Tools to a working directory e.g. C:\temp\Tools..

  2. Open a “cmd” window (Start menu->Run->cmd)

  3. CD to the tools directory:

C> cd "C\temp\Tools"

  4. Edit TargetChecker.bat, entering the appropriate values for DSN, user and password for the ODBC data source that connects to the source database system:

set DSN=MYDSN
set USER=MYUSER
set PWD=MYPWD

  5. If you are using SQDR Plus for UDB, stop the Capture Agent.

  6. Run TargetChecker.bat

  7. You will be prompted for the name of the ODBC data source and user/password credentials to connect to the control database.

  8. You will then be prompted to select the group to verify.

  9. Results are written to a CSV file.


CatalogTest

SQDR Plus information CatalogTest (deprecated)

This deprecated tool works only with SQDR Plus v3 and will be removed in a future version of SQDR.

CatalogTest, in the Tools subdirectory of the SQDR installation, is an administrative tool that verifies SQDR Plus' view of catalog information such as the index information, etc. of the Capture Agent's host tables.

Make sure that this system has Java installed (from a command window enter “java –version). If not, download and install the Java run time from http://www.java.com. If StarAdmin or bB2 is installed on the machine, the included batch file will attempt to use the JVM supplied by those applications.

  1. Copy the contents of C:\Program Files\StarQuest\SQDR\Tools to a working directory e.g. C:\temp\Tools.

  2. Open a “cmd” window (start menu->run->cmd)

  3. CD to the tools directory:

C> cd "C\temp\Tools"

  4. Edit CatalogTest.bat, entering the appropriate values for DSN, user, and password for the ODBC data source that connects to the source database system. The value of SCHEMA refers to the schema used by SQDR Plus (default SQDR):

set DSN=MYDSN
set USER=MYUSER
set PWD=MYPWD
set SCHEMA=SQDR

  5. Run CatalogTest.bat

  6. Enter Arguments for TableInfo (Schema Table Option):

This entry is a bitmap:

0 - tables (recommended)
8 - tables (different versions)

SQL wildcards or patterns

1 - primary keys
2 - unique indexes
6 - return all indexes
7 - get 2 result sets (one with primary keys and one with all indexes)

schemapattern tablepattern option

The output will contain the output from the stored procedure SQDR.TableInfo() (which is used by SQDR and SQDR Plus) and the results from equivalent ODBC catalog calls. Examine the results to look for any differences.


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 StarQuest Support.

The following parameters are described below:

  • notifyUserID & notifyPassword

  • notifyMailEncryption

  • addUnsupportedDrivers

  • archiveIgnoreDeletes

  • useCluster

  • subCacheLimit

  • useOrderBy

  • ROWID/RRN settings:

  • rowidName

  • rrnName

  • rrnPrecision

  • rrnType

  • forceNonNullPK

  • assumeStableKey

  • suppressWideTypes

  • suppressAutoUnique

  • subMutexTimeout

  • Group Defaults:

  • defaultUseSelectDML

  • defaultApplyRetryTimer

  • defaultIrRowLimitDefault

  • defaultGroupHaltOnError

  • useXMLEncoding

  • incrementalDebug

  • setOracleSemantics

  • miniDumpType

  • disableIndices

  • defaultAllowCheckpointRestart

  • Apply Timers:

  • applyMaxExceptionRetryInterval

  • applyMaxStartupRetryInterval

  • applyMaxAutoSnapshotInterval

  • tolerateSourceCommitError

  • MSSQL_IndexRebuildOption

  • maxMySQLParms

  • minNumBufferAllocation

  • deepSubValidation

  • includeBeforeImage

  • padVarchar

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.

  • 0 (default) - Decorate as [VAR]CHAR(n CHAR) phrase in the CREATE TABLE statement.

  • 1 - Decorate as [VAR]CHAR(n BYTE) phrase in the CREATE TABLE statement. This permits creation of table when using NVARCHAR(4000) and byte semantics.

  • 2 - Do not decorate the CREATE TABLE statement. This will result in using the DBMS NLS_LENGTH_SEMANTICS setting, as returned by the SQL statement SELECT value FROM NLS_DATABASE_PARAMETERS where parameter='NLS_LENGTH_SEMANTICS'

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.


Using the SQDR Text Driver

The SQDR Text Driver can be used to read from or to create files in the following formats:

Format | Description
Comma-separated values | Commas separate column values, and each line is a separate record. Column values can vary in length. These files often have the .CSV extension.
Tab-separated values | Tabs separate column values, and each line is a separate record. Column values can vary in length.
Character-separated values | Any printable character except single and double quotes can separate column values, and each line is a separate record. Column values can vary in length.
Fixed | No character separates column values. Instead, values start at the same position and have the same length in each line. The values appear in fixed columns if you display the file. Each line is a separate record.
Stream | No character separates column values nor records. The table is one long stream of bytes.

CSV or TAB files are commonly used when importing and exporting to/from spreadsheets and some database systems. The default is to create CSV files with an extension of .TXT.

The Text driver executes SQL statements directly on the text files. The driver supports Insert statements and inserts the record at the end of the file. You can execute Update and Delete statements conditionally.

The Text driver can access files up to 15 GB in size.

Table names can be up to 32 character. When the driver creates the file (e.g. with CREATE TABLE), the filename itself is truncated to 8 characters (plus the 3 character extension) and folded to upper case. The mapping between filename and table name is configured in QETXT.INI e.g.:

as created by the driver:

[Defined Tables]
MYTABLE1.TXT=MYTABLE123456

[MYTABLE123456]
FILE=MYTABLE1.TXT

But manipluating QETXT.INI allows for flexibility in naming and location - e.g.:

[Defined Tables]
MySubDir/MyTabInASubDir.txt=MYTABINASUBDIRECTORY

[MYTABINASUBDIRECTORY]
FILE=MySubDir/MyTabInASubDir.txt

The above information about manipulating filename to table name mapping was discovered experimentally and may not apply in all cases.

Path names can be maximum of 128 characters. Setting the Use Long Qualifiers (ULQ) property to 1 allows path names up to 255 characters.

For additional information, refer to the ODBC 7.1.6 User's Guide in the SQDR_ODBC Drivers Program Group.

To use as a destination:

Create a target directory e.g C:\mydest and specify it as the Database.

Enable AllowUpdateAndDelete (unless the desired result is an archive table).

When creating a subscription, specify either Append replicated rows to existing data (for archive purposes) or Truncate table before replication (rather than Delete existing database before replication) on the destination panel.

Sample connection string:

Database=C:\mydest;AllowUpdateAndDelete=1

When creating the source or destination object, specify Integrated Security, or place any value in the User field; this information is ignored.

When you run the baseline or snapshot, the file will be created. In addition a file named QETXT.INI containing column information for all tables in the directory is created.

If you specify FirstLineNames=1, the column information will appear as the first line of the resulting text file.

If the desired result is an archive table, you do not need to enable AllowUpdateAndDelete; instead, specify "Append replicated rows to existing data" on the Destination panel of the subscription. This may result in better performance. Since CSV tables cannot be indexed, searched updates and deletes may be slow.

Here is another sample connection string, specifying that the resulting file will have a CSV extension, and its contents delimited with with the pipe character:

Database=L:\Transfer;AllowUpdateAndDelete=1;TableType=Character;Delimiter=|;DataFileExtension=CSV;FirstLineNames=1

To use as a snapshot source:

Sample connection string:

Database=C:\mydest

The directory should contain the file QETXT.INI containing column information. Refer to the Help documentation or, for examples, examine the files created by SQDR when the text driver is used as destination.

Limitations

  • Data types:

  • Only character, timestamp (dates), and numeric values are supported. However, most data types can be mapped to some kind of character literals.

  • Numbers are limited to a precision of 15.

  • While binary data types (e.g. BLOB) are not supported, hex and hex64 forms may be used.

  • NOT NULL constraints are ignored.

  • CSV tables cannot be indexed, so searched updates and deletes may be slow.

  • Transactions are not supported.


Oracle SSL Connections

To connect using SSL (as is required when connecting to Oracle Cloud Autonomous databases), you will need an Oracle Wallet, which is a password-protected container used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL.

Using SQDR Oracle 8 ODBC driver (recommended)

  • Unzip the Oracle Wallet.

  • Examine tnsnames.ora to identify the hostname, port, and ServiceName.

  • Use keytool (part of Java Runtime) to convert the keystore and truststore from JKS to PKCS12 format. You will be prompted to supply the passwords for the existing keystores.

keytool -importkeystore -srckeystore keystore.jks -destkeystore keystore.p12 -srcstoretype JKS -deststoretype PKCS12 -deststorepass <password>

keytool -importkeystore -srckeystore truststore.jks -destkeystore truststore.p12 -srcstoretype JKS -deststoretype PKCS12 -deststorepass <password>

  • Create an ODBC data source connection to verify connectivity and encrypt the keystore passwords.

On the General tab, enter:

Data Source Name
Host
Port Number
Service Name

On the Security tab, enter:

Encryption Method: 1-SSL
Key Store: full path to keystore.p12
Key Store Password: password for the keystore

  • Select the Test Connect button and verify connecitivty

  • Save the DSN

  • Examine the registry and copy the encrypted password
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\mydsn\KeyStorePassword

If you want to Validate Server Certificate, select the checkbox for Validate Server Certificate and supply Trust Store and Trust Store Password.

Here is a connection string that connects without validating the server certificate:

HostName=adb.us-phoenix-1.oraclecloud.com;PortNumber=1522;
ServiceName=myservicename.atp.oraclecloud.com;
KeyStore=C:\keystore\keystore.p12;KSP=<encrypted-password>;VSC=0;EM=1

To validate the server certificate, set VSC=1 and add the Trust Store and Trust Store Password parameters. At this point, the connection string becomes long and unwieldy and you may prefer to use the data source.

Using Oracle Instant Client

Refer to Oracle documentation for the use of the Oracle Wallet with Oracle Instant Client

Connect Autonomous Transaction Processing Using a Client Application

Connect Autonomous Data Warehouse Using a Client Application

Briefly:

  • Install Oracle Instant Client. This involves running odbc_install from an elevated prompt, adding the directory (e.g. C:\OracleIC) to the PATH system environment and setting TNS_ADMIN to that directory.

  • Unzip the Wallet into a secure folder on your computer.

  • Examine tnsnames.ora from the Wallet and add connectivity information to your working tnsnames.ora file in the OracleIC directory.

  • Copy sqlnet.ora from the wallet to your OracleIC directory and replace "?/network/admin" with the name of the folder containing the expanded wallet. e.g.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA
 = (DIRECTORY="C:\keystore")))
SSL_SERVER_DN_MATCH=yes


Additional Copyright Information

Additional Copyright Information for Third Party Libraries

JSON11

SQDR uses the tiny JSON library for C++11 available at https://github.com/dropbox/json11

Here is the copyright notice for that library:

Copyright (c) 2013 Dropbox, Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


Copy button

Copy


Copy DDL-only Replication Considerations

This option has been deprecated as of SQDR 5.19 & later.

Enabling the Copy DDL Only option replicates only the Data Definition Language (DDL) CREATE statement that creates the object (e.g., view, alias, or synonym) on the destination database.

This option is only available to snapshot replication subscriptions.

If the Copy DDL Only option is enabled on the subscription Source panel, the DDL CREATE statement will be replicated to the destination either when the subscription is saved or every time the subscription is run.

If the subscription replicates an object to a database with a different schema structure (i.e. different qualifier, schema, or table naming conventions), the default CREATE statement generated by the subscription wizard may not be valid. Modify the CREATE statement as needed while the subscription is being defined, as SQDR will not parse or attempt to validate the CREATE statement.

For example, the CREATE statement of a SQL Server view will typically refer to the object using a three-part naming format, as in [database].[owner].[object_name]. If the subscription replicates the object to a different type of database, such as DB2, the CREATE statement will fail because DB2 supports only a two-part naming convention (e.g., <library>.<object>).

Copying DDL only between databases that use similar object naming conventions may also pose a problem if the dependent objects referred to in the CREATE statement are not in the same location on the destination database as on the source database.

For instance, a MySQL DDL CREATE statement may contain a reference to an object in the form of <qualifier>.<object>, where "qualifier" identifies the database name. A DB2 system, despite using a similar two-part naming convention, would interpret the "qualifier" as the library (schema) where the object exists, which may or may not be the correct location of the object. Modify the CREATE statement as needed to ensure that all referenced objects are qualified correctly.

For best results, the CREATE statement should be carefully reviewed and modified accordingly to insure that the resulting statement is valid on the destination database and achieves the desired effect.


Delete button

Delete


Filter button

Filter Display


Help button

Display Help System


ir_keylog Control Table

The ir_keylog control table can be used to drive downstream events instead of using triggers.

Also see Change Data Processing for another approach for accessing the same information.

The ir_keylog file is a log of the communication between the SQDR Plus system and the client system (running SQDR) as it relates to the application of changes to the destination table (DDL and DML.) The file is pruned based upon the number of statistics rows (a service level property.) The table refers to other SQDR Control Tables: subscriptions (identifying the source, destination ODBC DSNs, the source and destination tables), groups - identifying the containing "Group" and the statistics table which summarizes baseline (snapshot) results as well as summary incremental counters.

For a given subscription, the ir_keylog table will contain the details of the row to be inserted, deleted, or updated. The before, after key fields illustrate the source ODBC type, the local program data type and destination ODBC types used to effect the transfer, in addition to the string representation of the value(s) used to identify the row. The resulting number of rows affected by the operation is identified in the result_row_count column. If an ODBC error is reported, the "result_error" column contains the text message associated with the error condition. In the event that an unexpected number of rows (usually something other than "1") are affected by the operation, a subscription is "flagged". The "flagged_count" tracks the number of operations since the last baseline that resulted in an unexpected row_count_error.

The table has the following columns, where the associated text describes the source of the information (i.e. "SQDR Plus" denotes data supplied by SQDR Plus system, "SQDR" denotes data supplied by the client, etc.):

"COLUMN_NAME" | SQDR: FK: table.column relationships or attribute meaning
"run_id" | SQDR: FK: refers to statistic.run_id
"group_id" | SQDR: FK: refers to groups.id; statistic.group_id; ir_subscription.group_id
"subscription_id" | SQDR: FK: refers to ir_subscription.subscription_id
"resync_state" "transaction_id" | SQDR Plus: An enumerator used by SQDR Plus to order transactions
"change_row_counter" "change_row_sequence" | SQDR Plus: An enumerator used to order rows within a transaction
"change_row_timestamp" | SQDR Plus: Time data was logged on i5/OS host DBMS (in seconds - host time) (or staged for UDB host)
"change_row_nanosecond" | SQDR Plus: Time data was logged on i5/OS host DBMS (fractional part - in nanoseconds)
"change_row_type" | SQDR Plus: see below
"change_row_log_detail" | SQDR Plus: log entry details from the journal (if enabled in Staging Agent) in an XML-like format. Fields include job & user name, iSeries journal code, RRN, and sequence number. See below for examples.
"change_row_member" | SQDR Plus: member name of the source, when iSeries multi-member support is enabled and a member other than the default (*FIRST) is requested.
"action_taken" | SQDR: see below
"result_row_count" | Destination DBMS: Number of rows affected by action_taken-associated operation as returned by destination
"result_error" | Destination DBMS: Any ODBC error message associated with action_taken on the destination table
"flagged_count" | SQDR: Current count of number of rows flagged for this subscription
"before_key" "before_key_edited" | SQDR: for "Delete" and "Update" operations - used by WHERE CLAUSE
"after_key" "after_key_edited" | SQDR: for some Update operations (partitioned subscriptions and open-window updates)
"keylog_sort" | SQDR: An increasing sequence number within the ir_keylog file
"keylog_ts" | SQDR: ir_keylog datetime of insertion (client platform datetime)
"insert_ts" | SQDR: ir_keylog row insertion timestamp
"after_image" | SQDR Plus: data from the change for updates and inserts. Contains the after-image (planned, not implemented) and may include the delete before image, if available.

Here are the details of the change_row_type field:

opcode |
"B" | Delete row - Horizontal Partition
"D" | Delete row
"U" | Update row
"I" | Insert row
"R" | Table reorganized
"S" | Snapshot required
"T" | Truncate table
"A" | Source table altered
"X" | Source table dropped
"C" | Close window signal

The result of the change_row_type is described in the "action_taken" column. The codes used for "action_taken" are a subset of the "change_row_type". A special value of 0x00 means the change_row_type was ignored. Some change_row_type only change the state of the subscription and do not result in the changes on the destination table and are NULL.

Here are the details of the action_taken field:

action_taken |
D | Delete row
U | Update row
I | Insert row
T | Truncate table
S | Snapshot required
A | Source table Altered
X | Source table dropped
C | Close window signal
# | "Stale" Close window token
0x00 | change_row_type ignored
? | Unrecognized change_row_type

Here is an example of the change_row_log_detail field (iSeries source):

<detail>
<receiver>MYLIB QSQJRN0014</receiver>
<user>SQDR91</user>
<job>QZRCSRVS QUSER 511037</job>
<type>U 70</type>
<sequence>00000000000019012714</sequence>
<rrn>0</rrn>
<program>RJRNLAPI</program>
</detail>

For more information, see the technical document Using SQDR Key Logging Data and the script IR_Keylog.sql in the Tools subdirectory.


Maximizing performance using multirow fetch and insert

SQDR can deliver enhanced performance for large data transfers by using multirow fetch and insert, when supported by the source and destination ODBC drivers. Multirow fetch and insert behavior is enabled by default in SQDR.

If you need to disable multirow fetch and insert, deselect the checkbox in the Service Properties Replication (Run) Tab.


Modifying Multiple Columns

In the Columns Properties dialog, you may select multiple columns for modification. Use the control or shift keys to select multiple list elements and click the Modify Column button.

You will see this dialog:

The source and destination column names are now showing special values of “^s” and “^d”, respectively. These are variables which will substitute the original “source” and “destination” names for each list element when OK is clicked. The variables may be combined with other text.

For example:

“RTRIM(^s)” means specify a source expression using the scalar function RTRIM on the source column denoted by “^s” and apply the change to each row in the selection.

“^d_01” will modify the destination name by adding a suffix of “_01” to the destination name.

Here you can see the effect of a prior substitution, where the prefix string “pre_” was added to some of the destination names.


Properties button

Properties


Refresh button

Refresh Display


Run button

Run


View Details button

View Details


View List button

View List


View Small Icons button

View Small Icons