This section describes the replicate database issues and considerations specific to the Informix Dynamic Server data server in a Sybase replication system.
As a replicate data server, Informix interacts with the DirectConnect for Informix database gateway. The DirectConnect for Informix is responsible for accepting commands from the replicate Replication Server, and applying those commands to an Informix database.
To apply transactions in a replicate database, Replication Server requires a Maintenance User ID that you specify in the Replication Server create connection command. The Maintenance User ID must be defined at the Informix data server and granted authority to apply transactions in the replicate database. The Maintenance User ID must have the following permissions in the Informix replicate database:
create table authority to create tables used for Replication Server processing
update authority on all replicate tables and execute authority on all replicate stored procedures
The only significant intrusions or impacts to the Informix replicate database are the database objects created by the hds_informix_setup_for_replicate.sql script to support Replication Server replicate database operations.
The hds_informix_setup_for_replicate.sql script creates two tables in the replicate database to support Replication Server operations:
rs_info, which contains information about the sort order and character set used by the replicate database.
You should confirm that the insert statements
for this table (in the hds_informix_setup_for_replicate.sql script)
specify the proper character set and sort order for your Informix
data server.
When using Replication Server version 12.0 or later, the replicate database sort order and character set must be recorded in the rs_info table.
The Replication Server rs_get_charset and rs_get_sortorder functions retrieve the character set and sort order from the rs_info table in the replicate database.
rs_lastcommit, which contains information about replicated transactions applied to the replicate database.
Each row in the rs_lastcommit table identifies the most recent committed transaction that was distributed to the replicate database from a primary database. Replication Server uses this information to ensure that all transactions are distributed.
The Replication Server rs_get_lastcommit function retrieves information about the last transaction committed in the replicate database. For non-Sybase replicate databases, the rs_get_lastcommit function is replaced in the database-specific function-string class by the query required to access the rs_lastcommit table in the replicate database.
A Replication Server database connection name is made up of two parts: a data server name (server_name) and a database name (db_name). The server_name is the name of the DirectConnect for Informix database gateway server, and the db_name is the name of the Informix replicate database.
The replicate Replication Server looks for an interfaces file entry for the database gateway server_name specified in the Replication Server database connection. The replicate Replication Server logs in to the replicate data server using the user_name and password specified in the database connection.
You must make an entry in the Replication Server interfaces file to identify the host and port where the DirectConnect for Informix database gateway server is listening. The interfaces file entry name must match the server_name portion of the Replication Server database connection.
The following replication limitations exist with an Informix replicate data server:
Replication of large object (LOB) datatypes (BLOB, CLOB, and LVARCHAR) is not supported directly from Replication Server to DirectConnect for Informix.
For information about a possible work-around for this
LOB data replication limitation, see “Large object replication”.
The Informix INT datatype allows values ranging from -2147483647 to 2147483647, and the Informix SMALLINT datatype allows values ranging from -32767 to 32767. Most data servers support integer datatypes with values ranging from -2147483648 to 2147483647 and small integer datatypes with values ranging from -32768 to 32767. Replication Server does not provide user-defined datatypes for Informix INT and SMALLINT datatypes to restrict the lower boundary.
Informix supports up to 32 significant digits of precision/scale in exact numeric datatypes (DECIMAL, and NUMERIC), not 38 significant digits as does Sybase Adaptive Server Enterprise. Replication Server does not provide user-defined datatypes to support the Informix numeric datatype restriction.
Replication Server does not provide a class-level translation for Adaptive Server to Informix to handle the Adaptive Server bit datatype. If you use class-level translations to handle bit data replicated to an Informix replicate database, Replication Server returns the following error message:
E. 2001/12/14 15:19:57. ERROR #1028 DSI EXEC(136(1) dock723_dev_db.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|SQLState=37000|Native Error= -201|Message=[Visigenic][ODBC Informix 7.2 Driver][Informix]-201: A syntax error has occurred.] <DCA>'
The problem is that without a translation, the bit value goes out as 0x00 or 0x01—syntax that Informix does not understand.
Using Replication Server user-defined datatypes and default translations, you can not directly insert or update the BYTE binary datatype in Informix. If you need to replicate this type of data to an Informix database, you must capture the binary literal value as a char or varchar datatype, or use the Component Integration Services (CIS) feature of Adaptive Server, rather than a DirectConnect database gateway, to connect the replicate Replication Server to the Informix replicate database.
The heterogeneous datatype support (HDS) feature of Replication Server provides a number of sample SQL scripts that help you set up the HDS feature in the replicate Replication Server and the Informix replicate database. These include:
hds_informix_setup_for_replicate.sql – a script to be applied to the Informix replicate database.
The hds_informix_setup_for_replicate.sql script creates the rs_info and rs_lastcommit tables in the replicate database. The script includes grant statements that need to be changed before execution to reference the correct user ID name of the Maintenance User defined in the Replication Server database connection for the replicate database. The Maintenance User ID must have update authority on the tables.
Sybase recommends using the Maintenance User ID to execute
this script.
hds_informix_funcstrings.sql and hds_informix_udds.sql – scripts to be applied to Replication Server System Database (RSSD).
The hds_informix_udds.sql script adds the user-defined datatypes (UDDs) that define the attributes of Informix native datatypes to the RSSD. The UDDs are required to ensure datatypes received from primary transactions are properly formatted for application to the Informix replicate database.
You might need to modify the hds_informix_udds.sql script
to reference the correct RSSD database name.
The hds_informix_funcstrings.sql script replaces several default Replication Server function strings with custom function strings designed to communicate with Informix and access the tables and procedures created by the hds_informix_setup_for_replicate.sql script. These function strings are added to the Replication Server default rs_informix_function_class.
You might need to modify the script to reference the
correct RSSD database name.
Class-level datatype translation scripts to be applied to RSSD.
Class-level translations identify primary datatypes and the replicate datatypes the data should be translated into (for example, DB2 CHAR FOR BIT DATA should be translated to Informix BYTE).
These translations can affect Replication Server performance.
Only the translations needed for your specific primary database
and replicate database should be applied to the RSSD.
Class-level translation scripts supplied for the Informix replicate database are:
hds_clt_ase_to_informix.sql – translates Adaptive Server datatypes to Informix datatypes.
hds_clt_db2_to_informix.sql – translates DB2 for OS/390 datatypes to Informix datatypes.
hds_clt_udb_to_informix.sql – translates DB2 Universal Database (for UNIX and Windows) datatypes to Informix datatypes.
hds_clt_msss_to_informix.sql – translates Microsoft SQL Server datatypes to Informix datatypes.
hds_clt_oracle_to_informix.sql – translates Oracle datatypes to Informix datatypes.
hds_informix_connection_sample.sql – a script to be applied to the replicate Replication Server.
The hds_informix_connection_sample.sql script provides a template for creating the Replication Server database connection for an Informix replicate database using the pre-defined Informix function-string class provided with Replication Server.
You must modify the hds_informix_connection_sample.sql template to
include your actual server, database, and Maintenance User names before
executing the script.
The following system management issues are specific to an Informix replicate data server:
The create connection command’s dsi_sql_data_style parameter was used in previous versions of Replication Server to provide some data translations for the replicate database.
With the introduction of the heterogeneous datatype support (HDS) feature in Replication Server version 12.0, the create connection command’s dsi_sql_data_style parameter is no longer valid. Do not use this parameter with Replication Server version 12.0 or later. The default setting should be " " (blank space).
The Component Integration Services (CIS) feature allows users to access both Adaptive Server databases and non-Sybase databases on different servers, through a local Adaptive Server connection.
CIS allows Replication Server to connect to an Adaptive Server database, using default Adaptive Server function strings and connection properties, while CIS handles the data conversion and manipulation, and all communications with the DirectConnect database gateway.
See “Component Integration Services” for a description of the advantages and disadvantages of this approach.
