StarQuest Technical Documents

SQDR Plus: Tips for Informix Data Replication

Last Update: 18 June 2019
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:

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:

  • An issue using the IBM Informix driver (where the ability to perform incremental replication was not detected) was present in some versions of SQDR prior to SQDR 5.03; in this case, either update SQDR (recommended) or use the SQDR Informix ODBC driver.
  • Use the IBM Informix ODBC driver when connecting to an Informix 14.10 server.
  • You can also use the IBM DB2 ODBC (DRDA) driver, but some limitations (e.g. data types) apply.

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 and disabling full row logging

Symptom: Attempting to alter 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 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.

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.



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.