Troubleshooting
Problem
This article describes steps to set up Enterprise Replication (ER) on UNIX-based platforms.
Resolving The Problem
INTRODUCTION
The following article provides steps to set up a basic Enterprise Replication (ER) system between two database servers. The steps outline only the basic options for setting up ER on your systems. There are many other ways to configure ER. Please refer to Guide to IBM Informix Enterprise Replication for more information regarding definitions of the commands used below, and all the available options.
BEFORE YOU BEGIN
In order to follow the steps provided below:
- It is assumed that you have permission to administer all database server instances, including the ability to:
- Stop and restart the database server
- Add dbspaces and chunks
- Change the database servers networking settings
- Edit the ONCONFIG configuration file
- For ease of setup, use the same version of IBM Informix® Dynamic Server™ (IDS) on both systems. While using different versions of IDS is supported, it is not the intent of this document to explain or prepare you for implementing a mixed version ER environment. For more information about which versions are supported in an ER environment, please contact IBM Technical Support.
Preparing for ER
The following are requirements before starting ER on your systems:
- If using Conflict Resolution other than 'Ignore' for a replicate - each table will need to have the CRCOLS columns added:
- for existing tables, run the command:
ALTER table tabname add CRCOLS; - for new tables, specify "with CRCOLS:
CREATE table tabname (...) with CRCOLS;’ - If defining replicates using a select statement like ‘select * from tabname’, the base tables must be identical on all instances to be involved for this replicate.
- A smart blob space to hold spooled data needs to be created on each server, and listed in the ONCONFIG configuration file setting CDR_QDATA_SBSPACE.
- All databases that will take part in enterprise replication are logged, preferably with unbuffered logging.
- All tables involved with replication need a PRIMARY KEY constraint defined on at least one column, otherwise you must use the WITH ERKEY keywords in the table creation statement of any table that will be involved in replication and will not have a Primary Key defined.
create table "informix".customer
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18)
)
WITH ERKEY;
- If a table that is to be used in ER has already been created without a PRIMARY KEY and without using the WITH ERKEY keywords, then you will either need to recreate the table defining a PRIMARY KEY, or run an ALTER TABLE statement with the ADD ERKEY clause.
ALTER TABLE customer ADD ERKEY;
- All database servers to be involved in enterprise replication are already configured for general use and are operating correctly.
- All computers involved in ER have a trusted relationship with each other. To determine if your host is trusted by the other host(s) in the ER system, run:
dbaccess -> connection -> connect
You should be able to connect to the Group Name and the Servername of the other host(s) without using a password. - Time on all servers to be involved in ER must be synchronized with each other (specific to there own local time zone).
STEPS
1. Login as user informix.
2. Set your environment to administer the database server.
NOTE: Verify that the INFORMIXSERVER variable in your environment is set for the network connection and not the shared memory connection.
- Example: INFORMIXSERVER=unix1tcp
3. Set up the $INFORMIXDIR/etc/sqlhosts (or $INFORMIXSQLHOSTS) file:
The sqlhosts file must be modified for use with ER, defining server and group numbers for each server.
- Example prior to modification:
An example of an sqlhosts file from the UNIX server 'unix1' before modified for ER:
unix1shm onipcshm unix1 unix1shm
unix1tcp ontlitcp unix1 unix1tcp
unix2tcp ontlitcp unix2 unix2tcp
The unmodified sqlhosts file from UNIX server 'unix2':
unix2shm onipcshm unix2 unix2shm
unix2tcp ontlitcp unix2 unix2tcp
unix1tcp ontlitcp unix1 unix1tcp
NOTE: The two servers must be configured to have connectivity to each other. This means that the /etc/services file has entries for the TCP services listed in the sqlhosts file, and there may also be entries in the /etc/hosts or .rhost files for these servers.
Make modifications to define server numbers and group names for ER on each of your servers:
- Example after modification for ER:
The unix1 server:
unix1grp group - - i=1
unix1tcp ontlitcp unix1 unix1tcp g=unix1grp
unix2grp group - - i=2
unix2tcp ontlitcp unix2 unix2tcp g=unix2grp
unix1shm onipcshm unix1 unix1shm
The unix2 server:
unix2grp group - - i=2
unix2tcp ontlitcp unix2 unix2tcp g=unix2grp
unix1grp group - - i=1
unix1tcp ontlitcp unix1 unix1tcp g=unix1grp
unix2shm onipcshm unix2 unix2shm
4. Define the ER servers using the cdr define server command:
- Example:
On unix1, run: cdr define server -A atsdir -R risdir -I unix1grp
On unix2, run: cdr define server -A atsdir -R risdir -I unix2grp \ -S unix1grp
atsdir - path to a directory to store files that ER will create, example:
- /usr/informix/ats
- /usr/informix/ris
- check the output of the cdr list server command:
- Example output:
- SERVER ID STATE STATUS QUEUE CONNECTION CHANGED
- Check the message log for successful build messages:
- Run on unix1grp the command:
- Run on unix2grp the command:
-------------------------------------------------------------
unix1grp 1 Active Local 0
unix2grp 2 Active Connected 0 Nov 21 15:53:04
On unix1grp:
15:45:24 Building 'syscdr' database ...
15:45:28 'syscdr' database built successfully.
15:45:28 Loading Module <SPLNULL>
15:45:31 CDR queuer initialization complete
15:45:31 CDR NIF listening on asf://unix1grp
On unix2grp:
15:52:55 Building 'syscdr' database ...
15:52:59 'syscdr' database built successfully.
15:52:59 Loading Module <SPLNULL>
15:53:01 CDR queuer initialization complete
15:53:01 CDR NIF listening on asf://unix2grp
15:53:05 CDR GC: synchronization complete
NOTE: The message log for unix2grp shows an additional message
- CDR GC: synchronization complete
- onstat -g nif
Example output:
NIF anchor Block: b777470
nifGState RUN
RetryTimeout 300
CDR connections:
Id Name State Version Sent Received
---------------------------------------------------------
2 unix2grp RUN 7 14 6
- onstat -g nif
- Example output:
NIF anchor Block: b777470
nifGState RUN
RetryTimeout 300
CDR connections:
Id Name State Version Sent Received
---------------------------------------------------------
1 unix1grp RUN 7 15 6
5. Define the replicates using the cdr define repl command.
- Example:
The commands below are based on the state table from the stores_demo database. To create the stores_demo databases, run the command on both servers:
- dbaccessdemo7 -log;
To create a replicate named 'rep_state' using Update Anywhere syntax:
"stores_demo@unix1grp:informix.state" "select * from state" \
"stores_demo@unix2grp:informix.state" "select * from state"
- To create a replicate named 'rep_state' using Primary/Target syntax:
"P stores_demo@unix1grp:informix.state" "select * from state" \
"R stores_demo@unix2grp:informix.state" "select * from state"
- (The slash '\' characters are for line continuation.)
NOTE: Timestamp conflict resolution requires the addition of shadow columns to the replicated table. Refer to the 'Preparing for ER' section for details on how to add the shadow columns to new and existing tables. Conflict Resolution with any setting other than ignorewith a Primary/Target configuration will cause error 69.
To confirm the replicates are defined correctly, run the following command from both ER servers:
- unix1grp > cdr list repl
Example output:
CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE: rep_state
STATE: Inactive
CONFLICT: Ignore
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: stores_demo:informix.state
OPTIONS: transaction,ats,fullrow
6. Start the replicate using the cdr start repl replicate command on either server.
- Example:
cdr start repl rep_state
- Run the following command on either server:
- cdr list repl
Example output:
- CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE: rep_state
STATE: Active
CONFLICT: Ignore
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: stores_demo:informix.state
OPTIONS: transaction,ats,fullrow
- A replicate status of Active indicates that the replicate is defined and running on both servers.
- Insert a new record on the primary server if using Primary/Target syntax, or from either server if using Update Anywhere syntax.
[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"11.5;11.7;12.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
29 September 2018
UID
swg21153338