StarQuest Data Replicator Release Notes

Stelo Data Replicator v6.15 Release Notes

May 2023

Introduction

Stelo Data Replicator, also known as StarQuest Data Replicator (SQDR), is a replication software product that allows users to copy data between IBM Db2, Oracle, Microsoft SQL Server, MySQL and Informix database environments. Any table, portion of a table, or view can be replicated. Replications can be scheduled to take place at specified intervals or on demand. Replicating data from one database management system to another enables the user to:

  • Make the same data available to users of different database systems
  • Make the same data available to multiple sites
  • Balance network and database server loads by making the data available from two or more database servers
  • Create backup copies of data

Stelo Data Replicator provides:

  • Incremental replication from host databases supported by SQDR Plus.
  • 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.

If you will be using SQDR for snapshot replication, refer to the Quick Start Guide for the StarQuest Data Replicator for detailed information about installing StarQuest Data Replicator for the first time. If you will be using SQDR with its companion product Stelo Capture (also known as SQDR Plus) for incremental replication, refer to the SQDR Plus Quick Start Guide.

After you install the software, refer to the online help for information about configuring and using the Data Replicator. Press F1 or click the Help button in a dialog or on the toolbar to display the online help when using the Data Replicator Manager application.

The following topics are addressed in these Release Notes.

  • What's new in this release!
  • Upgrade Considerations
  • System Requirements
  • General Replication Considerations
  • Installing and using the StarQuest Data Replicator Software
  • Known Issues
  • Documentation Extras
  • Release History
  • Contacting Stelo

What's new in this release!

This release of the StarQuest Data Replicator introduces the following new features and improvements:

  • SQDR v6 is a major update, replacing the use of COM for interprocess communcation with gRPC.
  • SQDR can now run on Linux, including container-based deployments
  • SQDR Streaming Support for both baseline and change data, resulting in high performance support for non-SQL destinations including DataBricks/Delta Lakes on Azure & AWS (among others), Google BigQuery (via Confluent), Kafka streaming including Event Hub and other Kafka-based messaging endpoints.
  • Stelo Data Link Sink for Databricks
  • Enhanced support for Snowflake as destination
  • Support for new Apply option “Merge” for use with supported relational DBMS destinations, such as SQL Server, Snowflake, and PostgreSQL (v15).
  • Role Based Access Control & Auditing
  • Powershell scripting
  • Unity Catalog support for Data Bricks
  • Adding support for replicating to and from SingleStore
  • New interface for running SQDR related queries
  • Added support for Google BigQuery

Upgrade Considerations

IMPORTANT: Users upgrading from SQDR v5 should refer to Upgrade Considerations for SQDR v6 & later.

The following considerations also apply to recent versions of SQDR 5.2x:

IMPORTANT: After upgrading from a version of SQDR Plus prior to 5.10, a harmless "Table Altered" condition will occur for all subscriptions. To resolve this condition, use Data Replicator Manager to either

  • Run the incremental group OR
  • Set the DDL Replication property to AUTOMATIC. This can be done at the group level or at the service level.

Carefully consider the values for the DDL Replication settings before resuming replications after the update. Customers using "archive" subscriptions may want to change the group advanced property to specify "ignore" for dropping columns, as appropriate, to retain historical information. Customers who typically replicate only some subset of columns, and do not want to automatically add new columns may want to specify "ignore" for adding columns. However, most customers will want to use Automatic/Add-Perform/Drop-Perform in order to take advantage of the automatic handling of source schema changes (for source DBMS systems that are supported by SQDR Plus for automatic handling - see the ALTER Processing topic (under Creating a Group of Subscriptions) in the SQDR Help file (drmgr.chm).

The use of the latest ODBC driver for SQL Server (at minimum 17.6.1, released July 2020) is required when using SQL Server as a control database, and recommended when using SQL Server as a source or a destination.

IMPORTANT: CONTROL DATABASE SCHEMA VERSION

SQDR 6.1x introduces Control Database Schema version 6.10; you must be at Control Database Schema version 5.10 before updating to 6.1x.

Users of SQDR 5.10 through 6.11.0913: Run the Data Replicator Configuration to update your existing control database to the 6.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".

Users of SQDR prior to 5.10: See the readme.txt file for SQDR 5.1x-5.2x for instructions for updating from earlier than SQDR 5.10.

System Requirements

  • Supported operating systems
    • Windows Server 2016, Windows Server 2019, Windows Server 2022
    • Windows 10 or 11 64-bit (not recommended for production usage)
    • Linux x86-64 (AMD64 & Intel EM64T)
  • Latest Windows updates installed
  • A minimum screen resolution of 1024 x 768 is recommended.
  • The SQDR Service Properties utility and the Powershell cmdlets require .NET Framework 4.72 or later.
  • SQDR requires Visual C++ 2015/2017/2019/2022 runtimes. The SQDR installer will install the Microsoft-supplied Microsoft Visual C++ 2015/2017/2019/2022 Redistributable package if it is not already present on the system. The Microsoft Redistributable package will remain on the system if SQDR is upgraded or uninstalled.

If SQDR 32-bit version is running on a 64-bit operating system, SQDR must be upgraded to the 64-bit version. See Upgrading to 64-bit SQDR for upgrade instructions or contact Stelo Support.

SQDR requires Visual C++ 2015/2017/2019/2022 runtimes. The SQDR installer will install the Microsoft-supplied Microsoft Visual C++ 2015/2017/2019/2022 Redistributable package if it is not already present on the system. The Microsoft Redistributable package will remain on the system if SQDR is upgraded or uninstalled.

The Data Replicator requires a control database in which to store definitions for sources, destinations, and subscriptions. If you are using SQDR Plus to support incremental replication, we recommend using the instance of Db2 LUW installed with SQDR Plus. Otherwise, you can use any of the following versions of IBM Db2 for Linux, Unix Windows (LUW) or Microsoft SQL Server for the SQDR control database. You can obtain DB2 Community Edition free of charge from IBM. Similarly, you can obtain the Microsoft SQL Server Express free of charge from http://www.microsoft.com/downloads to use as a control database for the Data Replicator.

  • IBM Db2 for LUW 11.5.5fp1 or 11.5.7
  • SQL Server 2016/2017/2019/2022, including Express Edition

The control database can be either local or remote. For a remote database, install the appropriate ODBC driver:

  • For Db2 for LUW control database: Stelo StarSQL (included in the SQDR installer)
  • For SQL Server control database: Microsoft ODBC Driver 17 or 18

Data can be replicated to and from any of the following database systems:

  • Db2 for i (formerly known as DB2/400, DB2 UDB for iSeries, and DB2 for i5/OS) running OS/400 V5R4 and later
  • Db2 for Linux, UNIX, and Windows (DB2 LUW) v9.7 and later
  • Db2 for z/OS v8.1 and later
  • SQL Server 2019, 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005
  • Oracle 10g or later
  • Kognitio WX2 7.1 or later
  • MySQL 4.1 or later
  • MariaDB
  • Amazon Aurora
  • Apache Derby (JavaDB) 10.3 or later
  • TmaxSoft Tibero 6 or later
  • PostgreSQL
  • EDB Postgres Advanced Server
  • Actian (Pervasive) PSQL
  • Amazon Redshift
  • Salesforce
  • Snowflake
  • Exasol
  • Apache Spark/Databricks
  • CSV/text files, using the SQDR Text Driver
  • Informix: any version of Informix supported by v4.10FC6X2, FC7, or later of the IBM Informix ODBC driver:

For incremental replication, SQDR Plus must be configured for the host database system.

Access to Db2 databases requires the StarSQL ODBC/DRDA driver (v6.2 or later recommended and included as part of the SQDR installation), the IBM DB2 ODBC driver, or the IBM i Access Client Solutions driver (for access to data sources on Db2 for i).

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 ODBC Driver 17.x or 18.x for SQL Server. We recommend using the latest ODBC Driver 17.x.

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

General Replication Considerations

IBM DB2 ODBC driver

When using the IBM DB2 ODBC driver, set LongDataCompat=1 for correct replication of LOB data types. When replicating larger LOB columns, set LOBMaxColumnSize and the SQDR service property Inderterminant Column Size to the maximum value to be used.

PostgreSQL

When using the psqlODBC driver for PostgreSQL as a destination, we recommend configuring the Advanced panel as follows. See the Help file for a sample DSN-less connection string. We recommend using v10.1 or later of the psqlODBC driver; do not use v9.06.0500.

  • Max Varchar Size: 32767
  • Max Long Varchar Size: 214748364 (2GB)

When using the SQDR bundled ODBC driver for PostgreSQL as a destination, we recommend configuring the Advanced panel as follows. See the Help file for a sample DSN-less connection string.

  • 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)
  • Extended Options: enter BatchMechanism=2

Oracle

  • Access to Oracle databases requires one of the following ODBC drivers:
    • SQDR bundled ODBC 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)
  • When using an ODBC data source for the SQDR bundled ODBC driver for Oracle, supply the string ColumnSizeAsCharacter=1; ColumnsAsChar=1 in the Extended Options field on the Advanced panel.
  • 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

MySQL

  • We recommend using Connector/ODBC 8.0.27; do not use Connector/ODBC 8.0.28 or later or 5.1.6 . You can also use the MariaDB ODBC driver. You can 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

  • You can use the the SQDR bundled ODBC driver, IBM Informix ODBC driver (CSDK) v4.10FC7 or later, the IBM Db2 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.

Salesforce

  • Set Transaction Mode=1-Ignore on the Advanced Panel.

Snowflake

  • Issue the following command for the user being used by SQDR for the Snowflake source or 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.

BigQuery

  • If a DSN will be used instead of a connection string, then the “Enable Session” check box should be enabled on the Advance property page of the DSN Configuration

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.

Installing and using the StarQuest Data Replicator Software

  • If SQDR will be used with SQDR Plus for incremental replication, refer to the appropriate SQDR Plus Quick Start Guide at https://docs.stelodata.com/Supportdocs/browseQuickStarts.shtml. The SQDR Plus guides include instructions for installing both SQDR and SQDR Plus.
  • If you will be using SQDR for snapshot replication, refer to the Quick Start Guide for the StarQuest Data Replicator for detailed information about installing StarQuest Data Replicator for the first time.
  • 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 when using the Data Replicator Manager application.
  • Browse the StarQuest Data Replicator technical documents at https://docs.stelodata.com/Supportdocs/techSQDR/browseSQDR.shtml for specialized information, such as using the StarQuest Data Replicator in a particular environment or to accomplish a special task.
  • Refer to the technical document Troubleshooting SQDR Installation and Setup Issues for resolving problems that may be occur when installing SQDR or performing initial setup tasks.

Known Issues

MySQL Considerations

Indexes and primary keys on a MySQL source table may appear incorrectly in the subscription wizard; verify that only valid indexes are selected for replication purposes.

Precision Limitation for Oracle CHAR and RAW Column Types

The Oracle ODBC driver driver has a limitation that affects replicating from SQL Server or Db2 columns to an Oracle column of data type CHAR or RAW. Although the Oracle CHAR and RAW data types allow larger values, you must set the precision for the destination column that uses these types to 255 or less. If the source column precision is greater than 255, you can avoid data truncation by changing the destination column to a data type that allows a larger precision value. For character data, change the Oracle destination column data type to VARCHAR or LONG. For binary data, change the data type to LONG RAW.

Oracle Destination (Oracle driver) and Use Unicode Intermediates disables Mutirow Row Fetch and Insert:

If the destination is an Oracle database (using the Oracle ODBC driver) and the SQDR service is configured with Use Unicode Intermediates, the setting for Multirow Fetch and Insert is ignored, and the message "useUnicodeIntermediates disables MultiRow option for replication to Oracle destination" will appear in the replication statistics. Solution: use the SQDR bundled driver or DataDirect driver for Oracle.

Oracle destination limited to only one unsizable LONG mapping

If the destination is an Oracle database, you can replicate only one column that contains an unsizable LONG mapping; this is an Oracle limitation. If your source table contains more than one such column (e.g.multiple columns that map to BLOB or CLOB datatypes), you will need to choose which column to replicate and remove the other columns from the subscription.

Oracle Object Types are not supported
Oracle Object Types (also known as Abstract Data Types (ADT) or user-defined types) are complex compound structures that are not supported using ODBC and thus cannot be replicated by SQDR. You can replicate other data contained in tables with Object Type columns by deleting those columns in the Subscription Wizard.

Incremental Replication of Oracle LOB columns
There is a known issue when performing incremental replication of LOB columns from an Oracle source. When updating the contents of length of a LOB column that is greater than 4000 bytes, you must also update another column of the same row.

BCP error when replicating to SQL Server

Replication to SQL Server may fail in certain situations, such as when there is at least one LOB column n the source table and more than one ''image" column in the target table. You can work around this problem by specifying "Insert using ODBC" rather than BCP in the destination options of the subscription.

Replicating XML columns to and from Db2 for i (iSeries) and Db2 for LUW

When replicating tables containing XML columns to Db2 for i and Db2 for LUW destinations, the following limitations apply:

  • XML support was introduced to the iSeries in IBM i 7.1. When replicating XML data to IBM i 6.1 or earlier, XML columns will be mapped to CLOB.
  • If you are using IBM iAccess driver to access Db2 for i, use the May 2014 fixpak (SI53584) or later. In addition, if you are retrieving XML data from Db2 for i with iAccess, use the ODBC.INI
    keyword XMLDeclarationFormat or the connection string keyword XMLDECLARATION and specify a value of 0 or 2.
  • If you are using StarSQL 6.19 or later as the destination driver to Db2 for i, and inserting NULL-valued XML data, install PTF SI52427(i 7.1) or SI52430 (i 6.1). This PTF is included in the June 2014 DB2 group PTF

Replicating NULL-valued XML columns to Db2 for LUW with StarSQL

If you are using StarSQL 6.19 or later to connect to a Db2 for LUW destination and inserting NULL-valued XML data, you will need the fix for one of the following APARS, included in the listed fixpak:

  • DB2 9.7: APAR IC99936 FP10
  • DB2 10.1: APAR IT00342 FP5
  • DB2 10.5: APAR IT00343 FP4

Memory usage of Service increases when communicating with Manager

Leaving the Data Replicator Manager running for extended periods of time could cause a memory management problem in the Replicator Service. To avoid this, we recommend running the Replicator Manager only when you need to configure the service or subscriptions or monitor the replication operations. In addition, the Replication Manager will automatically exit after running for twelve hours.

Saving Subscriptions to AS/400 Member Files

If you encounter a subscription validation error while saving a new 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 in order to specify a member for the destination file.

Columns with a DEFAULT value

If the source table has columns that are defined with a DEFAULT value, that information is not preserved when the destination table is created using SQDR.

Solutions:

  • Execute an "ALTER TABLE" SQL statement after creating the table (e.g. as a Post-processing step):
  • e.g. ALTER TABLE MYTABLE ALTER COLUMN LOG_DATE SET WITH DEFAULT 'CURRENT TIMESTAMP'

or

  • Copy the CREATE TABLE DDL statement as defined on the source system, execute it in an ad-hoc fasion on the destination system, and configure the SQDR destination as "Use Existing Table"

Config: Control database naming

Do not use a hyphen when naming a SQL Server control database; an ODBC syntax error: "Incorrect syntax near 'Control-DB'" will result when sqdrconf attempts to create the control database.

SQDR Service fails to start

If the SQDR Service fails to start with the error "The service did not respond to the start or control request in a timely fashion.", be sure that the user specified to run the service is authorized to use DCOM. On Windows Server 2008, this can be accomplished by adding the user to the group Distributed COM Users.

MySQL: table not found error

If the name of a MySQL source table contains a period, the table will appear in the list of available source tables, but selecting it for replication will result in a "table not found" error. Either rename the table or create a view over the table and choose the view for replication.

Kognitio WX2 Issues

  • The maximum length for a VARCHAR column should be 7899 rather than 7900. Also, if you have large VARCHAR columns in your source table, edit the column mapping for each subscription individually to make sure that it is set correctly.
  • SQDR reports the creation of an index as sucessful when, in fact, no index is created.
  • You may receive a misleading "table exists" message when creating a table.
  • Certain character data may result in the error "SQLSTATE S1000, native error 1195573780, [Kognitio][WX2 Driver][wx2] CG0214: Invalid string representation"
  • When WX2 is used as a source, views are not supported.

Documentation Extras

  • None

Release History

See the SQDR Release History .

Contacting Stelo

If you need to contact technical support, please provide the following information to help the support engineers address your issue. You can contact Stelo via phone, email, or facsimile as indicated at the bottom of this page.

Company Information Address
Phone
Contact Information First and Last Name of individual contact
Email Address
Host Type Hardware and Operating System (i.e., IBM i 7.3)
Network Protocol/Gateways Protocol and/or Gateways used (i.e., TCP/IP, SSL/TLS)
Client Type Client Type and Operating System (i.e., Windows Server 2019, Oracle Linux 8.6)
Stelo Product,
Version, and Source
Which Stelo product and what version is installed (i.e., StarSQL for Windows v6.41.0719)
Where you obtained the software (i.e., direct from Stelo, name of specific reseller)
Problem Information Provide as much detail as possible, including information about any application that is using the Stelo product when the problem occurs and the exact error message that appears.

© 2023 Stelo. All rights reserved.

Stelo logoStelo, a StarQuest company
548 Market St, #22938
San Francisco, CA 94104-5401
Telephone: +1 415.669.9619
Sales information: https://www.stelodata.com/contact-stelo
URL: https://www.stelodata.com
Support: https://support.stelodata.com
Info Center: https://docs.stelodata.com