StarQuest Technical Documents

SQDR Plus Troubleshooting Tips

Last Update: 14 May 2019
Product: SQDR Plus
Version: 4.50 or later
Article ID: SQV00PU010

Abstract

This document contains generic troubleshooting tips for SQDR Plus. Most of these tips apply to all source DBMS types; please refer to the appropriate technical document for DBMS-specific issues.

Additional troubleshooting hints can be found in these technical documents

The following topics are covered:

The following topics apply to specific versions of SQDR Plus and can be resolved by updating:

Unable to login to SQDR Control Center after an upgrade of SQDR Plus

Symptom: Credentials are rejected with the error "Login request failed. The response could not be deserialized."

Solution: Refresh the webpage in the browser.

Unexpected Control Center Behavior

Symptom: After upgrading SQDR Plus, SQDR Control Center is not behaving as expected - e.g. new functionality is not working, or you get the error "Request failed. 500 the call failed on the server; see server log for details" and the jetty wrapper log C:\ProgramData\StarQuest\sqdrplus\jetty\logs\wrapper.log contains GWT (Google Widget Toolkit) serialization errors.

Solution: Refresh the Javascript cache in the browser by using control-F5.

Shortcut to Control Center opens wrong port

Symptom: If you remove SQDR Plus and reinstall specifying a different port for the Control Center service, the shortcut attempts to use the original port.

Solution: Log off and log on.

Invalid Data Conversion Error after Schema Change on the Source

Symptom: The following error was encountered after a schema change (increasing the width of a character column) was made on the source, and the agent was continuously restarting to recover from the error:

SEVERE: [sqv][Thread-122][09.09.2013 15:43:50] CaptureAgentLog: ReplicationWorker.run:
com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10404][3.65.77] Invalid data conversion: Parameter instance ?1?0.?00 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815
at com.ibm.db2.jcc.am.cd.a(cd.java:698)

Solution: Set validateMetadata=true in the agent configuration and restart the agent. All altered tables will be flagged for a new baseline.

Configuring extended_row_sz in the DB2 LUW staging database

Symptom: The following issue was encountered when performing incremental replication between two SQL Server databases. A snapshot replication of the same table encountered no problem.

Failed to add incremental subscription at Capture Agent for source table 'dbo.mytable'. Snapshot could not be started. Error: Stored procedure SQDR.ADDSUBSCRIPTION 04.22.20140314 returned error 11. RemoteException occurred in server thread; nested exception is:

java.rmi.RemoteException: Failed to add subscription: DB2 SQL Error: SQLCODE=-670, SQLSTATE=54010, SQLERRMC=32677;;71136, DRIVER=3.66.46 SQLSTATE=54010

The error message indicates that the source table exceeds the capacity of the intermediate staging database. This was confirmed by examining the CREATE TABLE statement in the details of the subscription).

Solution: Modify the staging database configuration used by the staging agent to use "extended_row_sz" ENABLE setting. This will permit the staging database to accomodate the larger row size required by this subscription.

  1. Identify the DB2 LUW staging database being used by the staging agent by examining the controlDbUrl parameter in the SQDR Plus configuration panel. controlDbUrl is of the form jdbc:db2://localhost:50000/SQDRPnn:driverType=4;deferPrepares=false; and we are interested in the value of SQDRPnn, where nn is a one or two digit number, 0 through 99.
  2. Stop the agent
  3. Using the DB2 Command Window (Administrator) issue the following commands:

db2 connect to SQDRPnn
db2 update db cfg using extended_row_sz ENABLE
db2 connect reset

  1. Start the agent.
  2. Run the subscription and confirm that it succeeds.

Note: This tip applies only to Staging agents created with versions of SQDR Plus earlier than 4.50. The staging databases associated with agents created with SQDR Plus v4.50 and later are already configured with the "extended_row_sz" ENABLE setting.

Agent fails to start with "Port already in use" error (SQDR Plus 5.00 & earlier)

Symptom: An agent fails to start, and an error like this appears in the Diagostics (the port number will vary based on your configuration):

CaptureAgent.main:
java.rmi.server.ExportException: Port already in use: 50008; nested exception is:
java.net.BindException: Address already in use: JVM_Bind

This condition is intermittent, and the agent will often start if you reboot the system or restart the SQDR Plus Launch Agent service. It has been observed on systems with a large number of agents.

Solution: SQDR Plus is using TCP/IP ports starting at 50005 for RMI communications. This range is also part of the default dynamic port range on Windows, and another process may have already opened that port for outbound communcations before the agent in question started.

To discover which process is using the port:
C> netstat -a -b
and search the output for port in question.

Short term solution:

Identify and stop the application using the port and start the agent. The other application will then use a different port when it is restarted.

In one case, we discovered that the conflicting application was another SQDR Plus agent - netstat -a -b displayed java.exe as the application, and that the port was in use by a database connection to the second host system. After stopping the second agent, we were able to start the problem agent, and then restart the second agent.

Long term solution:

Install SQDR Plus 5.01 or later (which implements the following)

OR

Use the following command to display the dynamic port range currently in use:

C> netsh int ipv4 show dynamicport tcp

Protocol tcp Dynamic Port Range
---------------------------------
Start Port : 49152
Number of Ports : 16384

Then change the starting point of dynamic port range to higher port (above the range used by SQDR Plus and DB2):

C> netsh int ipv4 set dynamicport tcp start=51000 num=14536

This change is persistent.

 

Data out of range error (SQLCODE=-302, SQLSTATE=22001) for DB2 LUW Staging Table

Symptom: The following error appears, referencing the DB2 LUW staging database, and is related to particular data in a CHAR or VARCHAR column

com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001

Solution: The error indicates data out of range (too large to fit in the corresponding column of the table).

This error may occur if a CHAR or VARCHAR column contains data that (when converted to UTF-8) is too large to fit in the destination column in the DB2 LUW staging table. For example, a SQL Server source table may include columns containing the x8D character, which converts to a 3 byte sequence when converted to UTF-8.

This is a rare condition; the default inflation of 100% (i.e. CHAR and VARCHAR columns in the DB2 LUW staging table are double the size of the corresponding columns of the source table), is usually more than sufficient. If you are affected by this issue, we recommend working with StarQuest support, who will verify that this is the cause of the problem, and will either alter the DB2 LUW staging table, or temporarily change the SQDR Plus inflation property from 100 to 200, drop the subscription, wait for pruning to remove the DB2 LUW staging table (this may take up to 30 minutes), recreate the subscription, and change the inflation property back to 100.


AGENT_STACK_SZ error from DB2 LUW staging database

Symptom: When running an incremental group with a large number of tables, the following error was received:

Stored procedure SQDR.GETCHANGE3 04.91.20171002 returned error 11. com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=57011, SQLERRMC=AGENT_STACK_SZ, DRIVER=4.19.66
Last GetChanges call: 2017-10-23 07:15:34.668000
Last Acknowledged: X'000000000000042E3FB3'
Pending Acknowledged: X'000000000000042E3FB3'

Solution: This is known issue which may occur when a large number of tables are involved in a single cycle of obtaining changes. This will be resolved in a future version of SQDR Plus.
Workaround: reduce the maximum number of transactions (an "advanced" group property) from 0 (no limit) to a number such as 100.

Start Agent menu item disabled (DB2 LUW and Oracle sources)

Symptom: After creating a new agent to a DB2 LUW or Oracle source, the Start Agent menu item is disabled. This is a temporary problem that was introduced in SQDR Plus v4.82 and was resolved in v4.90.

Solution: Upgrade to v4.90 or later, or select the agent, disable it, and then start it.

Using JRE 9 and later

We recommend using the certified version of the Java Runtime Environment (a recent version of JRE 8) that is installed with SQDR Plus. The following considerations apply when using JRE 9 or later; however, this is supplied for informational purposes and is not a supported or recommended environment.

  • Use v4.96 or later of SQDR Plus.
  • Create a text file C:\ProgramData\StarQuest\sqdrplus\conf\wrapper-local.conf (/var/sqdrplus/conf/wrapper-local.conf on Linux) containing the following directive:

set.JDK_JAVA_OPTIONS=--add-modules=java.xml.bind

Failure to do this will result in the following error in the Jetty wrapper log:

Exception in thread "main" java.lang.NoClassDefFoundError: javax/xml/bind/JAXBException



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.