StarQuest: keeping data in motion

 

StarQuest Technical Documents

Troubleshooting SQDR Usage Issues

Last Update: 22 November 2021
Product: StarQuest Data Replicator
Version: v4.50 and later
Article ID: SQV00DR027

Abstract

This technical document describes some of the issues that may be encountered when using the StarQuest Data Replicator.

The following topics are covered:

Errors Creating Incremental Subscriptions with Criteria

Symptom

Creating an incremental subscription with criteria fails with the following error:

Failed to add incremental subscription at Capture Agent for source table 'myschema.mytab'. Snapshot could not be started. Error: Stored procedure SQDR.ADDSUBSCRIPTION 04.20.20131218 returned error 11. RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: Failed to add subscription: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=ID, DRIVER=3.66.46 SQLSTATE=42703

Solution

Place double quotes around the column names used in the critera e.g. WHERE "ZIP">89999 AND "ZIP"<95000.

Note: using criteria when configuring an incremental subscription from a Microsoft SQL Server source or from a Db2 for i source (where the tables are journaled with *AFTER images) is supported by SQDR 4.50 & later and SQDR Plus 4.50 & later.

Unexpected Invalid journaling/logging error

Issue: Running an incremental subscription results in the error below when these conditions are met:

  • Source DBMS is Db2 for i
    The source table is journaled with IMAGES(*AFTER)
    The source table contains either a Primary Key or a Unique Index

Failed to add incremental subscription at Capture Agent for source table '"MYLIB"."MYTAB"'. Snapshot could not be started. Error: Stored procedure SQDR.ADDSUBSCRIPTION 05.07.20190321 returned error 18. Invalid journaling/logging options

Solution: Incremental replication of a table journaled with IMAGES(*AFTER) must use RRN.

Go to the Columns tab of the subscription and select the checkbox Create target unique index using source table RRN, select OK to save the change, and run the subscription again.

Note that this checkbox is automatically selected (and enforced) when the source does not contain a primary key or a unique index.

Creating a Dump File

If you are experiencing problems such a hung subscription, StarQuest Support may request that you create a dump of the SQDRSVC.EXE process using Task Manager.

  1. Start Task Manager by right-clicking on the Taskbar and selecting Start Task Manager, or entering the command taskmgr. Note that if you are running the 32-bit version of SQDR on a 64-bit system, it may be necessary to start the 32-bit version of the Task Manager by running C:\Windows\SYSWOW64\taskmgr.exe.
  2. Select the Processes panel.
  3. Select the checkbox Show processes from all users.
  4. Locate the entry for the SQDRSVC.EXE process, select and right click, and select Create Dump File.
  5. The selection will produce a dialog with the exact location of the sqdrsvc.dmp file - e.g. C:\Users\myuser\AppData\Local\Temp. Locate the file and send a compressed copy to StarQuest.

Subscription Wizard: Unexpected list of tables (SQL Server source; incremental subscription)

Issue: A user created a Staging Agent for a SQL Server source using SQDR Control Center. That definition includes the database name of the SQL Server database, and creates a schema in that database called SQDR, containing some control tables and the stored procedure TableInfo().

Next, the user wished to create incremental subscriptions residing in a different database residing on the same SQL Server. He chose the second database when configuring the source in Data Replication Manager, but the table list in the Subscription Wizard continued to return a list of tables from the first database.

Solution: It is necessary to create an SQDR Plus Staging Agent for each SQL Server database. After creating and starting the second Staging Agent with SQDR Control Center, return to Data Replication Manager and either create a new Source or modify the existing Source; go to the Advanced Panel and select the appropriate value from the Capture Agent Schema dropdown. Now the Subscription Wizard will invoke TableInfo() from the correct database.

Destination Timeout

Issue: A subscription failed with the error:

Replication failed. The insert thread timed out waiting for a filled fetch buffer.

Solution: The problem was a resource wait on the destination SQL Server; the default timeout of 30 seconds was too short. Using Data Replicator Manager, right-click on the Service name and select Properties. Increase the MutliThread Timeout from the default of 30 seconds to 300 seconds.

Problems caused by SQL Server Auto_Close

Issue: If a SQL Server database being used by SQDR is configured for Auto_Close, problems may arise. This may occur when the SQL Server database is being used for any purpose: control database, source, or destination. In addition to slow performance, a typical error that has been seen is

[Microsoft][SQL Server Native Client 11.0][SQL Server] Database 'ControlDB' is being recovered. Waiting until recovery is finished.

Solution: Confirm that Auto_Close is enabled and disable it.

To identify SQL Server databases that are configured for Auto_Close, use one of the following methods:

  • The Application Event Log contains many informational messages that state Starting up database 'dbname'.

  • The following SQL statement will show whether Auto_Close is enabled for a particular database:
    SELECT DATABASEPROPERTY('dbname','IsAutoClose')

  • The following SQL statement will list all databases configured for Auto_Close:
    SELECT name, is_auto_close_on FROM master.sys.databases WHERE is_auto_close_on = 1

  • Using SQL Server Studio, right-click on a database, select Properties, and select the Options page. Note that you may need to use the scroll bar to view the beginning of the Options list.

To disable Auto_Close, use one of the following methods:

  • Using SQL Server Studio, change the value of Auto Close to False.
  • Issue the following SQL statement:
    ALTER DATABASE <dbname> SET AUTO_CLOSE OFF

"value of a host variable too large" error

Issue: When replicating from a non-Unicode database to a UTF-8 target, you may encounter the following error for certain data:

ODBC message: SQLSTATE 22001, native error -302,
[StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.

Solution: This error may occur when some characters are converted into multi-byte characters and the size of the target table's character columns is inadequate. Modify the subscription and increase the size of the affected columns on the target table. You can use the Character data destination precision heuristic to specify an inflation percentage; see the SQDR Help for the Service Properties Subscription (Wizard) Tab.

Incremental Group failing with Bad IP address error

Issue: An incremental group is failing with the following error:

Failed to enable Change Data Notification for incremental group 'MYGROUP'. Error: Bad IP address: N.N.N.N:2728 (Winsock bind returned error. System error 10049: The requested address is not valid in its context. )

Solution: This error can occur if the IP address of the system has changed since the incremental group was created; for instance, you are using DHCP rather than a static IP address.

  1. Select the Source associated with your subscriptions, right click and select Pause. This will pause all the IR Groups for the source.
  2. Select the same source, right click and select Properties... and select the Advanced panel.
  3. Select a valid IP address from the dropdown field for IP addresses associated with the notification address and click OK. If both SQDR Plus and SQDR (tiers 2 and 3) are installed on the same machine, we recommend using 127.0.0.1.
  4. Right click on the Source and select Resume. All your IR groups should resume normally.

Db2 Connect and LOB Data

If you choose to use the IBM Db2 ODBC driver (Db2 Connect) to connect to a Db2 source or destination and plan to replicate LOB data, create a Db2 Connect ODBC data source, add the LONGDATACOMPAT parameter from the Advanced Settings tab of the CLI/ODBC Settings and set this value to “As LONGVAR data”. Set the Maximum LOB column size value if desired, or accept the default value.

"UPDATE or DELETE statement does not include a WHERE clause" error (Db2 for i target)

Issue: When replicating to a Db2 for i destination, the snapshot or baseline replication failed with the following error:

Delete of existing rows in destination table failed.
[StarSQL][StarSQL ODBC Driver][DB2/400]The UPDATE or DELETE statement does not include a WHERE clause.

Solution: This (misleading) error message was the result of an isolation level mismatch in the StarSQL ODBC data source and the journaling characteristics of the destination library. In this case, the DSN was configured for IsolationLevel=2 (Read Committed), but the destination library was unjournaled. The issue was resolved by either changing the IsolationLevel in the DSN to Default, or by replicating to a journaled collection.

SQDR service terminates unexpectedly (MySQL source or destination)

Symptom: Intermittent crash of the SQDR service when using the MySQL ODBC driver, and an examination of the crash shows:

Unhandled exception at 0x776EA899 (ntdll.dll) in xxxxxxx.7356.dmp: 0xC0000374: A heap has been corrupted (parameters: 0x77725910).

Solution: This error can occur due to a bug related to SetLocale() in the Microsoft Visual C++ 2013 Redistributable package (used by the MySQL ODBC driver). Use the Programs and Features control panel to examine the version of Visual C++ 2013 Redistributable installed and, if necessary, update it from 12.0.21005.1 to 12.0.40649.5.

See Update for Visual C++ 2013 and Visual C++ Redistributable Package for details.

Note: SQDR v5.01 and later is built using Visual C++ 2017, so this hint is applicable only to earlier versions of SQDR.

Error loading recently-installed ODBC driver

Problem: SQDR may be unable to use a recently-installed ODBC driver. For example:

ODBC message: SQLSTATE IM003, native error 160.
Specified driver could not be loaded due to system error 126:
The specified module could not be found.
(SQDR Salesforce, C:\Program Files\StarQuest\SQDR_ODBC_XE\rpsfr27.dll)

Solution: Restart the SQDR service.

The installer for some ODBC drivers, such as SQDR Salesforce, modify system environment variables such as PATH, and it is necessary to restart the SQDR service so it is using the modified environment.

SQDR SQL Server driver as destination: Error loading library rpsqls28.dll

Problem: The following error occurs when using the SQDR SQL Server driver as destination:

Error loading library rpsqls28.dll (or rpsqls27.dll)

Solution: 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.

 

Data Replicator Manager is Unresponsive

Symptom: On starting Data Replicator Manager immediately after starting the service (or if the service is started by starting Data Replicator Manager), the application waits at the splash screen for an extended period of time.

Solution: Examine the Application Event Log for issues that may be delaying the startup of the service. Typical issues are

  • Delays in contacting a StarLicense license server.
  • Inability to reach legacy source or destination databases. A change to SQDR was made in May 2017 that requires at least one connection to each source and destination to gather database information, even if there are no active subscriptions, and SQDR will continue to try to contact these database servers at startup until a connection is made. We recommend removing obsolete sources and destinations.

Symptom: Response in the Data Replication Manager is sluggish, for instance when expanding and collapsing.

Solution: This condition can happen if you have a large number of subscriptions and history logging grows beyond a certain limit.

Right click on the server name, select Properties, and select the Logging panel. Confirm that Limit replication history logging is enabled, and that the values for Maximum total number of history records and Maximum records per subscription are at a suitable level; the recommend values will depend on the number of subscriptions you have, and how much history you want to save. For instance, you may choose to use something like Total records=5000 and Max records per subscription=10.

Remote Data Replicator Manager Errors

Symptom: When using Data Replicator Manager to connect to a remote SQDR service, the following mesage appears:

Item cannot be found in the collection corresponding to the requested name or ordinal.

Solution: This error can occur if there is a mismatch of versions between Data Replication Manager and the remote SQDR service. Update Data Replicator Manager to the appropriate version.

Group Polling Error due to Trigger Error on Destination

Symptom: A customer encountered a Group Polling Error, even though the replication was successful. It was determined that the customer had created a trigger on the destination table, and a logic error in the trigger was causing an error to be returned to SQDR.

Solution: Triggers should not interfere with the return code or row count associated with the SQDR operation being attempted.

Example: For a SQL Server destination, see the following Microsoft reference documentation regarding trigger error handling (@@ERROR and the TRY...CATCH construct):

@@ERROR (Transact-SQL)

ALTER DROP Processing
(Db2 for i destination; SQDR 5.10 & later)

Symptom:

Automatic processing of ALTER DROP COLUMN fails with:

Processing of the SQL statement ended. Reason code 10.

Solution:

When an ALTER DROP COLUMN is issued, the IBM i system issues a message that needs to be answered:

Message ID . . . . . . : CPA32B2
Change of file TAB1 may cause data to be lost. (C I)

In an interactive environment, such as running STRSQL in a 5250 terminal session, a user can reply I (Ignore).

Because there is no interactive communication when SQDR issues a SQL statement, the above error may occur. Set the default reply for message CPA32B2 to Ignore with the following command:
 
ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY(I)

Use the WRKRPYLE (Work with System Reply List Entries) command to verify that this command has been issued.

Incorrect Row Count Information (Oracle source)

Symptoms

A replication from an Oracle source encountered an issue where TargetChecker indicates that the source and destination are not in sync. In this case, Targetchecker reported that the destination had more records than the source, even after running a new baseline.

The following SQL returns a lesser number of rows than the actual count of rows on the Oracle source table:

select count(1) from MYSCHEMA.MYTABLE

The following SQL statements display the correct number on the source table:

call dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE')
select num_rows from sys.all_tables where table_name = 'MYTABLE'

Solution:

Recreate the indexes in the source table.


Using deprecated types option (example)

Symptom: A customer replicating from Informix to SQL Server encountered an issue with columns that were defined as DATE on the source system. When replicated to SQL Server, SQDR created datetime2 columns on the destination. However, this disrupted downstream processes that compared this data, expecting datetime fields.

Solution: Select Enable deprecated types in Service Properties and recreate the subscriptions. Datetime now appears as an option on the Column mapping dialog when creating the SQL Server destination.

Data Truncated Error (LOB column)

Symptom: The following error applying change data for a table containing a LOB column:

Error processing LOB change data for subscription MYSUB1. SQL attempted: 'SELECT "MYCOL" FROM "MYSCHEMA"."MYTABLE" WHERE RRN("MYSCHEMA"."MYTABLE")= ? '. Keys: '<RRN>41</RRN>'.
ODBC message: SQLSTATE 01004, native error 0, [StarSQL][StarSQL ODBC Driver]Data truncated.

This error may occur for any source type - for instance, the error might be:
ODBC message: SQLSTATE 01004, native error 0, [StarQuest][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in column 1.

Solution:

On the general panel of Service Properties, increase the Intedeterminate-size column limit from its default of 1024 (KB) to a larger number. This value is used to allocate the maximum buffer associated with LOB data for Apply. The optimal value is dependent on the size of the data in your LOB columns.

DELETE vs TRUNCATE for Destination

In most cases, using TRUNCATE where supported is recommended and will result in best performance.

However, in some cases SQDR is not correctly detecting that TRUNCATE is available for a destination DBMS and the radio button for Truncate on the Destination panel is greyed out. For example, this the case for Exasol as of SQDR 5.24; this will be addressed in a future version of SQDR.

Workaround: If the destination DBMS does support TRUNCATE but SQDR is not offering it as an option, you can perform a TRUNCATE on destination in a pre-processing step. After that, SQDR will still perform a DELETE but it will be quick.


"Invalid string or buffer length" error for BLOB column (Oracle source)

Symptom:

The following error occurred when replicating a table with a BLOB column from Oracle to Postgres. This error may occur for other destination DBMS types.

Invalid string or buffer length

Solution:

Increase the Indeterminable Size Column Limit parameter (service->property->replication) to the largest size of the BLOB + 10% (growth factor).

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.