We upgraded from 10.00.FC8 to 11.50.FC6. We had a replicate built on the following table:

CREATE TABLE "informix".my_history (
mydate VARCHAR(20,0),
user CHAR(20),
program CHAR(20),
recordtype CHAR(20),
data CHAR(255),
mykey CHAR(20)
) WITH CRCOLS
IN dbs1 EXTENT SIZE 1580 NEXT SIZE 156 LOCK MODE ROW;

REVOKE ALL ON "informix".my_history FROM public;
CREATE UNIQUE INDEX "informix".uix_my_history ON "informix".my_history (
mydate ASC,
user ASC,
program ASC,
recordtype ASC,
data ASC,
mykey ASC
) USING btree IN dbs1;

ALTER TABLE my_history ADD CONSTRAINT PRIMARY KEY (
mydate,
user,
program,
recordtype,
data,
mykey
) CONSTRAINT pk_history;


We had to rebuild the replicate and now it fails. The command which we usedwas:


cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select
* from my_history"* "my_db@grp229:informix.my_history" "select* * from my_history"
Verification of my_db@grp200:informix.my_history started
Verification of my_db@grp200:informix.my_history is successful
Verification of my_db@grp229:informix.my_history started
Verification of my_db@grp229:informix.my_history is successful
command failed -- unsupported SQL syntax (join, etc..) (40)


We figured the "USER" column was causing the problem so we explicitly defined it with the table name prepended. Still didn't work.


cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select
my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history"* "my_db@grp229:informix.my_history" "select* my_history.mydate, my_history.user, my_history..program, my_history.recordtype, my_history.data, my_history.mykey from my_history"
Verification of my_db@grp200:informix.my_history started
Verification of my_db@grp200:informix.my_history is successful
Verification of my_db@grp229:informix.my_history started
Verification of my_db@grp229:informix.my_history is successful
command failed -- unsupported SQL syntax (join, etc..) (40)


Suggestions?

Hello!
Why don´t you try to run it using OAT?

It´s easier, faster, and you don´t worry about command lines.

Since you´re on 11.50 you already can do it.

I suggest you to try it, certainly it´s better than using some huge
command lines (old versions) ok?

Best regards!

Em 30/11/2010 12:08, Link, David A escreveu:

--

Alexandre Marini

Tecnologia da Informação - DBA

msn: alexandre_marini (AT) hotmail (DOT) com

SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix

Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>

IBM Informix Dynamic Server Certified Professional V10 / V11

On Nov 30, 10:08*am, "Link, David A" <DAL... (AT) west (DOT) com> wrote:
Making my life difficult, eh??? ;-)

Looks like you ran into a bug. Yes 'user' is the culprit - in
conjunction with a mastered replicate. If you can afford to remove
the -M option then it should work. Otherwise, we'll have to rename
the column. (ugg...)

I opened a bug (219876) on this and do have a hacky patch working, but
before I can finalize the patch, I'll need to make sure that it
doesn't cause a regression with other ER functionality.


M.P.

On Nov 30, 10:44*pm, mpruet <mpru... (AT) verizon (DOT) net> wrote:
Oops. Found out that 219876 is a duplicate of 219863. We'll be
working this issue as 219863.

On Nov 30, 11:28*am, Alexandre Marini <amar... (AT) fazenda (DOT) ms.gov.br>
wrote:

I fully agree with your suggestion that they use OAT. It makes ER
much simpler to use.

But the basic issue would not have been resolved by using OAT since
replicates created by OAT are always mastered replicates.