replication troubles

[J-W Reuling]

Hi,

I'm using replication on linux and aix for a while now. I've set up several
replications from aix to aix, from linux to aix and vice versa. It was
running just fine until a few days ago. I noticed that some target tables
differ from the sourcetables. Some have more records than it's source others
have less records. The majority of the tables is still in sync.

I'm using one way replication for all tables, and all changed data for
partitioned keys is captured as deletes and inserts.
When records are updated, the cd tables show delete statements of the old
records and insert statements for the new records as expected. But the
applytrail table often shows that there were several transaction reworked
meaning that inserts statements fail and are then turned into updates and
updates are turned into inserts when that particular record does not exist
and thus cannot be updated.

I can't figure out why this happens!
I'm expecting the old record to be deleted and the new record to be
inserted. This insert statement seems to fail and is reworked to do an
update. But why??? Can this result in differences between the source and the
target tables??

Hope anyone can help me out here.
Thanks in advance.

J-W Reuling

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.

http://mail.spaminspector.com

[Jim Stewart]

Re: replication troubles

J-W,

Typically the problem with source and table mismatch comes about from
differences in the Is_Key = Y columns in the subs_cols table compared
to the index for the target table.

Apply builds a SQL DELETE statement putting is_key = Y columns into the
where clause. If that where clause overqualifies the row so that nothing
matches, Apply ignores the -100 SQL code figuring that the row is already
deleted, no problem.

The insert uses the unique index and if that index differs from the is_key
columns you can get a case where Apply hits an -803 and flips the insert to
an update. With the update you're back to the is_key columns again.

The best way to see if this is your situation is to run the analyze program
or if you have V8, asnanalyze. Check the section called "target key synopsis"
where the analyze tool compares sysindex to is_key columns and notes any
mis-matches.

Best regards,
Jim Stewart
DataPropagator Support

[J-W Reuling]

Re: replication troubles

Hi Jim,



Thanks for your reply, i've been out of office so I did not see it right
away.

I've experienced the is_key "problem" before, but this time it was
different. I tried the analyze tool and everything turned out to be OK. I've
checked the replication subscription and all key columns and everything was
configured the way it should be.

The next thing I did was call IBM support and after they had done a little
research it turned out to be a bug. So an APAR was setup (APAR IY53184) and
they supplied a fix for it. We're testing it right now.



This was their reply:

- - - 8< - - -

The devloper confirmed that the problem is that of APAR IY53184. The fix
for this APAR has been placed at the ftp site. Can the customer please
install this fix. Please let us know if the problem still persists.

- - - >8 - - -



The title of the APAR in question is :

DPROPR Capture not processing small endian on intentseq correctly on Linux.





Best Regards,

Jan-Willem Reuling


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.

http://mail.spaminspector.com

"Jim Stewart" <jgstew@nospam. us.ibm.com> wrote in message
news:402a989f$0 $64523$45beb828 @newscene.com.. .[color=blue]
> J-W,
>
> Typically the problem with source and table mismatch comes about from
> differences in the Is_Key = Y columns in the subs_cols table compared
> to the index for the target table.
>
> Apply builds a SQL DELETE statement putting is_key = Y columns into the
> where clause. If that where clause overqualifies the row so that nothing
> matches, Apply ignores the -100 SQL code figuring that the row is already
> deleted, no problem.
>
> The insert uses the unique index and if that index differs from the is_key
> columns you can get a case where Apply hits an -803 and flips the insert[/color]
to[color=blue]
> an update. With the update you're back to the is_key columns again.
>
> The best way to see if this is your situation is to run the analyze[/color]
program[color=blue]
> or if you have V8, asnanalyze. Check the section called "target key[/color]
synopsis"[color=blue]
> where the analyze tool compares sysindex to is_key columns and notes any
> mis-matches.
>
> Best regards,
> Jim Stewart
> DataPropagator Support
>[/color]