StarQuest Technical Documents
StarQuest Data Replicator (SQDR) FAQs
Last Update: 25 April 2022
Product: StarQuest Data Replicator
Version: 4.6x or later
Article ID: SQV00DR006
Abstract
This article answers frequently asked questions (FAQs) about the StarQuest Data Replicator (SQDR). For a general overview of the SQDR solution, see the SQDR product page. For FAQ's related to SQDR Plus (incremental replication support), see the SQDR Plus FAQs.
Questions
- Can I perform incremental updates to a database after completing the initial bulk replication?
- Can I replicate Db2 source tables that contain a "." character in the table name?
- Is there any loss of transparency or loss of datatype information in the replication?
- How does SQDR handle text fields in non-English languages?
- Can I reformat the data in the process of replicating it?
- Since the SQDR replication service requires unlimited access rights to my host data, isn't this a security risk?
- Can SQDR replicate LOB data?
- How can I turn off subscription caching?
- If I want to replicate between Db2 and Oracle, do I still need SQL Server?
- How do I enable SQDR to use other ODBC drivers?
Can I perform incremental updates to a database after completing the initial bulk replication?
SQDR, in conjunction with SQDR Plus, provides incremental replication support. Incremental replication copies only the data that has changed, which reduces the amount of data that is transferred and allows the replication to occur more frequently. Incremental replication requires installing the SQDR Plus software on a Tier 2 staging system (which is typically the same system as that running SQDR) to monitor changes on the host database system and communicate with the Data Replicator Windows-based service. SQDR Plus is currently supported for Db2 for i, Db2 for Linux, UNIX & Windows, Microsoft SQL Server, Oracle, MySQL/MariaDB/Aurora, and Informix source systems.
If your source database is not supported by SQDR Plus and you wish to simulate incremental replication using SQDR, refer to the technique outlined in the StarQuest technical document Using SQDR for Limited Incremental Replication from SQL Server—Advanced.
Can I replicate Db2 source tables that contain a "." character in the table name?
If you attempt to create a subscription for a Db2 source table that contains a "." character in the table name, you may receive this error message:
[StarSQL][StarSQL ODBC Driver][DB2/400]Relational database <LIBRARY> not the same as the current server <DATABASE SERVER NAME>.
To replicate Db2 tables that contain a "." character in the table name, set the UseDSCRDBTBL configuration parameter to NO in the StarSQL data source configuration. This setting is accessible on the Experts Page panel of the data source configuration wizard. Restart the SQDR service to ensure that the new connection setting takes effect.
Is there any loss of transparency or loss of data type information in the replication?
No, SQDR accesses host data through the ODBC interface, which supports most host data types and ranges of data values directly. When you configure a replication operation, SQDR queries the source database and automatically communicates data type and attribute information to the target database along with the actual data. You can also configure custom conversions for exceptional cases.
How does SQDR handle text fields in non-English languages?
SQDR supports textual data in all national languages, including Double Byte Character Set (DBCS) languages. It relies on the ODBC interface, along with host and client code page configuration to ensure a transparent mapping between EBCDIC and ASCII (or Unicode) representations of the text.
Can I reformat the data in the process of replicating it?
Yes, you can choose to skip certain rows or columns of the source table, and you can also specify computed columns based on other columns in the source data. These specifications can be made by using the built-in replication options or by using SQL (structured query language) during the replication configuration process.
Does the SQDR replication service require unlimited access rights to my host data, and isn't this a security risk?
SQDR fully supports your host's data access security. It accesses host data through an ODBC data source or connection string, which is configured with a host user name and password. The host database administrator manages the access rights for this account to the host database, so SQDR can only perform operations on the database that are permitted by the host account.
Yes, SQDR can replicate LOB data to and from Db2, SQL Server, and Oracle data sources.
How can I 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. Use the SQDR Service Properties application to set the subCacheLimit advanced setting to a value of -1 so that SQDR always retrieves new subscription information.
See Advanced Settings in the Reference chapter of the SQDR help file (drmgr.chm) for details.
If I want to replicate between Db2 and Oracle, do I still need Microsoft SQL Server?
The Data Replicator requires a control database in which to store definitions for sources, destinations, and subscriptions. You can use any of the following databases for the SQDR control database.
- IBM Db2 for LUW 11.5.5pf1 or later
- SQL Server 2012 or later (any edition, including Express)
If you using SQDR Plus for incremental replication, we recommend using the copy of Db2 for LUW installed with SQDR Plus for the SQDR control database.
If you do not already have a SQL Server installation, you can obtain the SQL Server Express Edition free of charge from Microsoft; we recommend installing SQL Server Management Studio as well. Refer to the StarQuest technical document "Installing SQL Server for a Control Database" for more information about using SQL Server as a control database for the Data Replicator.
I have installed the ODBC driver and created an ODBC data source for my database but my ODBC DSN does not appear in the list of available data sources when I try to create a source or destination. How do I configure SQDR to use an uncertified ODBC driver?
Explanation
The list of supported DBMS systems are listed in the SQDR System Requirements.
When creating an SQDR source or destination, The default behavior of the Data Replicator Manager is to limit the display of available ODBC data sources to those supported by SQDR.
If you select Connection Method = ODBC Driver (rather than ODBC DSN), installed ODBC drivers that are not certified will appear with the Description "Driver not certified".
SQDR is designed to work with other databases if they can be accessed through an ODBC driver that complies with Level 2 of the ODBC 3.51 specification, but configuration is necessary if you want to try an ODBC driver that has not been certified.
Solution
To instruct Data Replicator Manager to display all ODBC data sources, change the advanced service property addUnsupportDrivers to true.
- Start the SQDR Service Properties application (sqdrprops.exe) from the StarQuest Data Replicator program group.
- Connect to the SQDR service (typically localhost)
- Change addUnsupportDrivers from False to True.
- Click Save.
It should not be necessary to restart the SQDR service. Restart Data Replicator Manager to see the new data sources.
The advanced service properties that can be modified by the SQDR Service Properties application are documented in the Advanced Settings topic under Reference in the SQDR Help File.
To use a connection string:
It is not necessary to set the addUnsupportDrivers property, as the driver already appears in the list of ODBC Drivers. Select the ODBC driver and enter a valid connection string and credentials. Select Advanced to verify that the connection is successful.