Re: Abnormal behaviour of Enterprise Replication
Navaid Arif ran IDS 10 on AIX with Enterprise Replication: one replicate (dralways) worked, while rp72_merchtbl replicated nothing and reported no errors. Dropping and recreating merchtbl fixed it, and he asked whether missing table-level logging had been the cause and how to enable UNBUFFERED logging per table. Keith Simmons explained Informix has no per-table logging — in a logged database all normal tables are logged, the exceptions being temp tables and RAW tables. Art Kagel added that a RAW table can be converted with ALTER TABLE ... TYPE(STANDARD), and Keith noted a RAW table can't be replicated anyway since it can have no primary key. The underlying ER failure cause was never definitively confirmed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
My below mentioned was resolved after recreating the table merchtbl. I have a
doubt that I created database earlier and enabled UNBUFFERED logging on
database recently. Is there any possibility that I was facing problem because
logging was not enabled on merchtbl which was created earlier.
Does INFORMIX support table level logging? If Yes, then how can we enable
UNBUFFERED logging on a particular table?
Thanks is advance.
Regards,
Navaid Arif
----- Original Message -----
From: Navaid Arif
To: admin-tools@iiug.org ; informix-list@iiug.org ; peoplemix@iiug.org ;
classics@iiug.org ; ids@iiug.org
Sent: Saturday, April 14, 2007 10:02 PM
Subject: Abnormal behaviour of Enterprise Replication
Hi All,
I am running IDS10 UC5 on AIX5. And successfully configured ER-Replication on
both Primary Server (srvtest) and Target Server (eftdr). Replicates
rp72_dralways and rp72_merchtbl are successfully added but replication is
working only on rp72_dralways successfully, other replicate rp72_merchtbl is
neither showing any error nor replicating data. Kindly help me in finding the
problem.
###################
Primary Site Info
###################
###########
#/etc/hosts
###########
127.0.0.1 loopback localhost # loopback (lo0) name/address
172.16.1.72 srvtest
172.16.5.90 eftdr
##################
#/etc/hosts.equiv
##################
172.16.5.90 informix
+ eft
+ informix
+ tts
###############
#/etc/services
###############
testport 5126/tcp
svceft 5123/tcp
##############################
#/informix/ids10/etc/sqlhosts
##############################
#=========
#PRIMARY
#=========
grppri group - - i=2
srvtest onsoctcp srvtest testport g=grppri
#========
#TARGET
#========
grpdr group - - i=1
eftdr onsoctcp eftdr svceft g=grpdr
############################
create table dralways
(
name1 varchar(20),
name2 varchar(20),
primary key (name1) constraint ids_name3
);
create table merchtbl
(
est_id char(15),
mer_name char(50),
org_type char(3),
active char(1),
txn_curr char(3),
flr_lmt float,
com_rate integer,
txn_decpt smallint,
term_type char(1),
category char(4),
m_mcc char(4),
tcc char(1),
addr1 char(100),
addr2 char(100),
area_cd varchar(3),
settle_city varchar(30),
zipcode char(10),
num_country char(3),
site_code char(3),
time_zone char(3),
stand_in char(1),
alpha_country char(3),
mne_country char(2),
defer_flag char(1),
rps_flag char(1),
ec_indicator char(1),
self_srv_term char(1),
fleet_flag char(1),
cont_name char(32),
cont_phone1 char(32),
cont_phone2 char(32),
dba_mer_name varchar(50),
dba_addr1 varchar(100),
dba_addr2 varchar(100),
dba_area_cd varchar(3),
dba_set_city varchar(30),
dba_location varchar(30),
region_cd char(3),
cr_comm_type char(1),
cr_comm_type2 char(1),
cr_comm_rate float,
db_comm_type char(1),
db_comm_type2 char(1),
db_comm_rate float,
fc_comm_type char(1),
fc_comm_type2 char(1),
fc_comm_rate float,
tts_exp_bin_id char(8),
fc_serv_st_id char(8),
ut_flag char(1),
ut_nwk_id char(8),
utp_nwk_id char(8),
utp_access_no char(12),
utp_cust_no char(12),
utp_void_flag char(1),
ut_void_flag char(1),
rec_type char(1),
user_id char(10),
last_mdate char(8),
last_mtime char(6),
primary key (est_id) constraint merchtbl_estid_pk
);
EFT-Test >hostname
srvtest
EFT-Test >onstat -
IBM Informix Dynamic Server Version 10.00.UC5 -- On-Line -- Up 9 days 09:04:14
-- 46240 Kbytes
EFT-Test >cdr list server
SERVER ID STATE STATUS QUEUE CONNECTION CHANGED
-----------------------------------------------------------------------
grpdr 1 Active Connected 0 Apr 14 20:03:44
grppri 2 Active Local 0
EFT-Test >cdr list repl
CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE: rp72_dralways
STATE: Active ON:grppri
CONFLICT: Always Apply
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: orixeft:eft.dralways
OPTIONS: transaction,ris,ats,fullrow
REPLID: 131073 / 0x20001
REPLMODE: PRIMARY ON:grppri
APPLY-AS: INFORMIX ON:grppri
REPLICATE: rp72_merchtbl
STATE: Active ON:grppri
CONFLICT: Always Apply
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: orixeft:eft.merchtbl
OPTIONS: transaction,ris,ats,fullrow
REPLID: 131077 / 0x20005
REPLMODE: PRIMARY ON:grppri
APPLY-AS: INFORMIX ON:grppri
EFT-Test >cdr list repl brief
REPLICATE TABLE SELECT
----------------------------------------------------------------------------
rp72_dralways orixeft@grpdr:eft.dralways select * from dralways
rp72_dralways orixeft@grppri:eft.dralways select * from dralways
rp72_merchtbl orixeft@grpdr:eft.merchtbl select * from merchtbl
rp72_merchtbl orixeft@grppri:eft.merchtbl select * from merchtbl
EFT-Test >
#################
TARGET SITE INFO
#################
###########
/etc/hosts
###########
127.0.0.1 loopback localhost # loopback (lo0) name/address
172.16.5.90 eftdr
172.16.1.72 srvtest
##################
/etc/hosts.equiv
##################
172.16.1.72 informix
+ eft
+ informix
+ tts
###############
/etc/services
###############
svceft 5123/tcp # DR server INFORMIX port
testport 5126/tcp # Pri Server INFORMIX port
##############################
/informix/ids10/etc/sqlhosts
##############################
#=======
DR-SITE
#=======
grpdr group - - i=1
eftdr onsoctcp eftdr svceft g=grpdr
#========
ORIX-HO
#========
grppri group - - i=2
srvtest onsoctcp srvtest testport g=grppri
##############################
create table "eft".dralways
(
name1 varchar(20),
name2 varchar(20),
primary key (name1) constraint ids_name3
);
create table merchtbl
(
est_id char(15),
mer_name varchar(50),
org_type char(3),
active char(1),
txn_curr char(3),
flr_lmt float,
com_rate integer,
txn_decpt smallint,
term_type char(1),
category char(4),
m_mcc char(4),@@NL
On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > Hi All, > > My below mentioned was resolved after recreating the table merchtbl. I have a > doubt that I created database earlier and enabled UNBUFFERED logging on > database recently. Is there any possibility that I was facing problem because > logging was not enabled on merchtbl which was created earlier. > > Does INFORMIX support table level logging? If Yes, then how can we enable > UNBUFFERED logging on a particular table? > > Thanks is advance. > <SNIPPED> No, Informix does not support table level logging (unless the table is temporary and created with no log, or unless the table is TYPE RAW when you cannot even create indexes on it,just use it for loading), If a database is logged then all 'normal' tables within it ware logged. Keith
Hi All, I created the database having table "merchtbl" with "no logging" option two years back and enabled UN-BUFFERED Logging on this database recently. My question is, how can I enable unbuffered logging on this table without recreating it. Regards, Navaid Arif ----- Original Message ----- From: "Keith Simmons" <smiley73@googlemail.com> To: <ids@iiug.org> Sent: Tuesday, April 17, 2007 4:43 PM Subject: Re: Abnormal behaviour of Enterprise Replication [8929] > On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > > Hi All, > > > > My below mentioned was resolved after recreating the table merchtbl. I have > a > > doubt that I created database earlier and enabled UNBUFFERED logging on > > database recently. Is there any possibility that I was facing problem > because > > logging was not enabled on merchtbl which was created earlier. > > > > Does INFORMIX support table level logging? If Yes, then how can we enable > > UNBUFFERED logging on a particular table? > > > > Thanks is advance. > > > <SNIPPED> > > No, Informix does not support table level logging (unless the table is > temporary and created with no log, or unless the table is TYPE RAW > when you cannot even create indexes on it,just use it for loading), If > a database is logged then all 'normal' tables within it ware logged. > > Keith > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > Hi All, > > I created the database having table "merchtbl" with "no logging" option two > years back and enabled UN-BUFFERED Logging on this database recently. My > question is, how can I enable unbuffered logging on this table without > recreating it. > > Regards, > Navaid Arif > > ----- Original Message ----- > From: "Keith Simmons" <smiley73@googlemail.com> > To: <ids@iiug.org> > Sent: Tuesday, April 17, 2007 4:43 PM > Subject: Re: Abnormal behaviour of Enterprise Replication [8929] > > > On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > > > Hi All, > > > > > > My below mentioned was resolved after recreating the table merchtbl. I > have > > a > > > doubt that I created database earlier and enabled UNBUFFERED logging on > > > database recently. Is there any possibility that I was facing problem > > because > > > logging was not enabled on merchtbl which was created earlier. > > > > > > Does INFORMIX support table level logging? If Yes, then how can we enable > > > UNBUFFERED logging on a particular table? > > > > > > Thanks is advance. > > > > > <SNIPPED> > > > > No, Informix does not support table level logging (unless the table is > > temporary and created with no log, or unless the table is TYPE RAW > > when you cannot even create indexes on it,just use it for loading), If > > a database is logged then all 'normal' tables within it ware logged. > > > > Keith > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Sorry, I obviously didn't make myself clear enough. If a database is logged then ALL normal tables, whenever created, will be logged as well. You CANNOT switch logging on and off at table level. Are you getting any error message on the receiving server when trying to replicate data? Have you configured ris/ats and are you getting any files generated? Keith
You can ALTER a RAW table into a standard type table:
ALTER TABLE merchtbl TYPE ( STANDARD );
Art S. Kagel
----- Original Message -----
From: Navaid Arif <ids@iiug.org>
At: 4/17 7:52:48
Hi All,
I created the database having table "merchtbl" with "no logging" option two
years back and enabled UN-BUFFERED Logging on this database recently. My
question is, how can I enable unbuffered logging on this table without
recreating it.
Regards,
Navaid Arif
----- Original Message -----
From: "Keith Simmons" <smiley73@googlemail.com>
To: <ids@iiug.org>
Sent: Tuesday, April 17, 2007 4:43 PM
Subject: Re: Abnormal behaviour of Enterprise Replication [8929]
> On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote:
> > Hi All,
> >
> > My below mentioned was resolved after recreating the table merchtbl. I
have
> a
> > doubt that I created database earlier and enabled UNBUFFERED logging on
> > database recently. Is there any possibility that I was facing problem
> because
> > logging was not enabled on merchtbl which was created earlier.
> >
> > Does INFORMIX support table level logging? If Yes, then how can we enable
> > UNBUFFERED logging on a particular table?
> >
> > Thanks is advance.
> >
> <SNIPPED>
>
> No, Informix does not support table level logging (unless the table is
> temporary and created with no log, or unless the table is TYPE RAW
> when you cannot even create indexes on it,just use it for loading), If
> a database is logged then all 'normal' tables within it ware logged.
>
> Keith
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Actually Keith, you can by altering the table into and out of RAW mode. RAW mode tables are not logged regardless of the database logging status. Art S. Kagel ----- Original Message ----- From: Keith Simmons <ids@iiug.org> At: 4/17 8:04:23 On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > Hi All, > > I created the database having table "merchtbl" with "no logging" option two > years back and enabled UN-BUFFERED Logging on this database recently. My > question is, how can I enable unbuffered logging on this table without > recreating it. > > Regards, > Navaid Arif > > ----- Original Message ----- > From: "Keith Simmons" <smiley73@googlemail.com> > To: <ids@iiug.org> > Sent: Tuesday, April 17, 2007 4:43 PM > Subject: Re: Abnormal behaviour of Enterprise Replication [8929] > > > On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > > > Hi All, > > > > > > My below mentioned was resolved after recreating the table merchtbl. I > have > > a > > > doubt that I created database earlier and enabled UNBUFFERED logging on > > > database recently. Is there any possibility that I was facing problem > > because > > > logging was not enabled on merchtbl which was created earlier. > > > > > > Does INFORMIX support table level logging? If Yes, then how can we enable > > > UNBUFFERED logging on a particular table? > > > > > > Thanks is advance. > > > > > <SNIPPED> > > > > No, Informix does not support table level logging (unless the table is > > temporary and created with no log, or unless the table is TYPE RAW > > when you cannot even create indexes on it,just use it for loading), If > > a database is logged then all 'normal' tables within it ware logged. > > > > Keith > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Sorry, I obviously didn't make myself clear enough. If a database is logged then ALL normal tables, whenever created, will be logged as well. You CANNOT switch logging on and off at table level. Are you getting any error message on the receiving server when trying to replicate data? Have you configured ris/ats and are you getting any files generated? Keith ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
On 17/04/07, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote: > Actually Keith, you can by altering the table into and out of RAW mode. RAW > mode tables are not logged regardless of the database logging status. > > Art S. Kagel > > ----- Original Message ----- > From: Keith Simmons <ids@iiug.org> > At: 4/17 8:04:23 > > On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > > Hi All, > > > > I created the database having table "merchtbl" with "no logging" option two > > years back and enabled UN-BUFFERED Logging on this database recently. My > > question is, how can I enable unbuffered logging on this table without > > recreating it. > > > > Regards, > > Navaid Arif > > > > ----- Original Message ----- > > From: "Keith Simmons" <smiley73@googlemail.com> > > To: <ids@iiug.org> > > Sent: Tuesday, April 17, 2007 4:43 PM > > Subject: Re: Abnormal behaviour of Enterprise Replication [8929] > > > > > On 17/04/07, Navaid Arif <navaid.arif@access.net.pk> wrote: > > > > Hi All, > > > > > > > > My below mentioned was resolved after recreating the table merchtbl. I > > have > > > a > > > > doubt that I created database earlier and enabled UNBUFFERED logging on > > > > database recently. Is there any possibility that I was facing problem > > > because > > > > logging was not enabled on merchtbl which was created earlier. > > > > > > > > Does INFORMIX support table level logging? If Yes, then how can we > enable > > > > UNBUFFERED logging on a particular table? > > > > > > > > Thanks is advance. > > > > > > > <SNIPPED> > > > > > > No, Informix does not support table level logging (unless the table is > > > temporary and created with no log, or unless the table is TYPE RAW > > > when you cannot even create indexes on it,just use it for loading), If > > > a database is logged then all 'normal' tables within it ware logged. > > > > > > Keith > > > > > > > > > > > > > > Sorry, I obviously didn't make myself clear enough. If a database is > logged then ALL normal tables, whenever created, will be logged as > well. You CANNOT switch logging on and off at table level. > Are you getting any error message on the receiving server when trying > to replicate data? Have you configured ris/ats and are you getting any > files generated? > > Keith > Art Thanks, I thought I had that covered in my first response, anyway, if he had a RAW table then ER on it is a nonstarter (no Primary Key !) Keith