StarQuest: keeping data in motion

 

Stelo Technical Documents

SQDR Plus: Tips for Informix Data Replication

Last Update: 2 March 2023
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL042

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for data replication from an IBM Informix database.

The following technical documents provide additional information:

Contents:

Unable to create multiple SQDR Plus Agents

Due to the restriction that table names must be unique within a database, you cannot create multiple agents for the same database; attempting to do so will result in an error that the control tables already exist in the database.

However, if you have multiple databases in an Informix instance, you can create agents for each database.

Agent fails to start

Symptom: A newly created agent failed to start with the error:

Only a DBA can create, drop, grant, or revoke for another user.

Solution: This error can occur if the userID supplied during creation of the agent does not match the schema name, and the userID does not have full DBA authority to the database containing tables to be replicated. We recommend either using the same name (e.g. SQDR) for both the userID and the schema, or using the instance owner (informix) for the userID.

If the agent has already been created, this issue can be resolved by changing sourceDbSchema from sqdr (default) to the userID being used:

  1. Select the Agent
  2. Select Configuration
  3. Change sourceDbSchema from sqdr (default) to the userID being used
  4. Click the Save icon at the top
  5. start the Agent

Choosing an ODBC driver: SQDR Informix ODBC driver or IBM Informix ODBC driver

When configuring an Informix source for incremental replication in Data Replicator Manager, you can use either the bundled SQDR Informix ODBC driver or the IBM Informix ODBC driver. The following considerations apply:

  • Using the IBM Informix driver involves an extra installation, and updates to the driver may require uninstalling and reinstalling.
  • In some cases, using the bundled SQDR Informix ODBC driver may result in better performance.
  • You can also use the IBM Db2 ODBC (DRDA) driver, but some limitations (e.g. data types) apply.

Accessing Informix using IBM Db2 ODBC Driver through StarPipes or through Db2 LUW

SQDR can connect to Informix databases using either the IBM Informix ODBC driver, the IBM Db2 ODBC driver, or the SQDR-bundled ODBC driver. Each of these drivers have advantages and disadvantages.

However, an advantage of the IBM Db2 ODBC driver is that it uses the DRDA protocol to communicate with Informix, and thus can communicate through a StarPipes Gateway or through an intermediate Db2 LUW system acting as gateway.

To use StarPipes as a gateway, create a route to the Informix system in the StarPipes configuration e.g. RDB name=ifxdb, Host name=ifxserv, Port 9089, and configure SQDR to connect to the Informix database through StarPipes using the IBM Db2 ODBC driver, using the connection string HOSTNAME=mystarpipes;PORT=2000;DATABASE=ifxdb.

To use Db2 LUW as a gateway, catalog the node and database on the Db2 LUW system:

db2 catalog tcpip node ifxserv remote ifxserv server 9089
db2 catalog database ifxdb as ifxdb at node ifxserv authentication SERVER

and configure SQDR to connect to the Informix database through Db2, using the connection string HOSTNAME=mydb2server;PORT=50000;DATABASE=ifxdb.

To avoid a recv_socket timeout.error when connecting through StarPipes, right-click on the server name, select Properties, select Connection Pools panel, and set the Login timeout to 0. It is not necessary to restart the SQDR service.

When connecting through Db2 LUW, creating subscriptions and verifying criteria in the Subscription Wizard may fail with a syntax error. Right-click on the server name, select Properties, and uncheck Quote SQL identifiers on the Subscription Wizard panel. . It is not necessary to restart the SQDR service. After creating the subscriptions, you can revert Quote SQL identifiers to its default setting (checked).

These SQDR configuration changes are not required when connecting directly to the Informix server.

Publish and User Authorities

Symptom: If the agent userID does not have DBA authority to the database containing the tables to be replicated, publish will fail with the error

Only a DBA can create, drop, grant, or revoke for another user.

Solution: Use the Export to SQL Script checkbox and edit the resulting script, removing the (unnecessary) GRANT statements, and then invoke the script using the credentials of the agent user.

Publish and case sensitive table names

Informix schema and table names are case-sensitive. When using Publish, the search fields for schema and table names will fold the names to upper case as you enter the name, but the search will successfully find lower case and mixed case names matching the pattern.

ALTER or DROP and disabling full row logging

Symptom: Attempting to alter or drop a table that is being replicated will fail with the error

19816 - Cannot perform this operation on a table defined for replication

Solution: Delete the subscription and wait for pruning to run.

Deleting the subscription runs the command in the syscdcv1 database to disable full row logging:

execute function informix.cdc_set_fullrowlogging('<database>:<schema>.<table name>', 0)

However, it does not occur immediately - it happens when the agent prunes its staging tables, which happens at 30 minute (default) intervals - i.e. you may have to wait up until 30 minutes after deleting the subscription before the ALTER will succeed. you will see a message about pruning in the Diagnostics log. You can also force pruning to occur by restarting the Capture Agent in the Control Center - in that case, pruning happens one minute after agent startup.

In the case where you have already deleted the subscription and the operation did not occur), you can also use dbaccess to invoke the command
execute function informix.cdc_set_fullrowlogging('<database>:<schema>.<table name>',0)
Using a userID that has sufficient authority (on Windows, you can add the user to the Informix-Admin group); enter the command using the Query-Language/Use-editor function of dbaccess. A return code of 0 indicates success; a return code of -83703 error is the result of supplying incorrect database or table name.

If necessary, you can specify the user in dbaccess by using

  1. Select Connection
  2. Select Connect
  3. Select the server e.g. ol_informix1410
  4. Enter the credentials for user Informix

Syntax error on CREATE TABLE statement (Informix destination)

Issue: When creating a subscription with Informix as the destination, you may get a syntax error:

Error executing statement 'CREATE TABLE "qauser"."tabdeset" ("fld1" INTEGER NOT NULL , "fld2" CHAR (10) ) ':
ODBC message: SQLSTATE 42000, native error -201, [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.

This is due to using quoted identifiers.

Solution: Any of the following:

  • Enable quoted identifiers in the connection string by adding:
    DELIMIDENT=Y (IBM Informix driver)
    UseDelimitedIdentifier=1 (SQDR Informix driver)
  • Enable quoted identifiers on the server
    e.g. on Windows, set the following system environment variables in the System control panel:
    DELIMIDENT 1
  • Disable Quote SQL Identifiers in the Subscription Wizard panel of SQDR Service properties. You can re-enable this property after creating the subscriptions.

Install Fix for APAR IT29905

We recommend Installing the fix for APAR IT29905 to avoid a rare race condition that could stop the Informix server. The fix is available in 12.10.xC14 and 14.10xC3 and in future fix releases or special builds for other versions of Informix.

See the IBM APAR description IT29905: ASSERT CRASH SEEN IN SBT_DEACTIVATE_BEGIN OR SNOOPSESS_DTOR, MT_CLEAR_MUTEX WHEN RUNNING CDC AND CHANGING TO SINGLE USER MODE for details.

Unicode translation issues (SQDR/Linux)

Symptom:

When using SQDR on Linux to replicate to or from an Informix database, using the IBM Informix driver, Unicode code characters are not displayed properly.

example:

When replicating a table containing a varchar column with data 'CÔTE' from Informix to SQL Server, the destination table contained "C?TE" instead of "CÔTE DIVOIRE"

Solution:

Append Client_Locale=en_US.utf8 to the connection string:

Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y;Client_Locale=en_US.utf8

 


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.