request suggestion on data replication

[saltbits]

my requirement:
- i extract db2 data into flat files and send to remote
- remote transforms and loads to oracle and confirms it
- on confirmation of replication, i set a flag on every record of my table that says replication was successful

my question:
after generating the flat files, there is one of 2 ways i am looking at handling the flag - i need suggestion on which is better:
#1. right at the time of extraction, mark flag for each record as 'processing', then wait for replication to complete successfully, then update all flags that are 'processing' to 'success'
#2. after extraction, dont do anything to the flag, wait for replication to complete successfully, then read my flat file for PK or record number and then set flags to 'success'.
---- i cannot simply change all records in table to 'success' becos during the process of sending my flat file and waiting for confirmation, new records could be added to the table which have not yet been replicated.-------

Dumb as the question seems, I am no expert. I do not know if the extra step in #1 that does an update on the table is expensive (we will have a few hundred thousand records each on some 10 tables). But #2 seems a round-about. Could people share their views.

Thanks a lot.
saltbits.

 

[johnherman]

I like the idea of marking that you have selected and sent the records with one value, and then another value for success at the remote end. It more accurately describes and controls the process. For instance, if the file becomes corrupt during the transfer or is accidentally deleted on the other side, you can easily recreate the exact extract by reading the rows with the "pending" flag value.

Is there some reason why you are not using the built-in Oracle replication? I know that the module costs additional money....

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[saltbits]

john,

thanks much! the reason u've given helps - i kind of overlooked it.

not using oracle replication cos:
there is way too much transformation happening between the extraction and loading. also, the db2 and oracle repositories belong to two totally different groups and we are kind of 'required' to not wonder how the other group does their thing!!

thanks again.

 

[skiflyer]

I think the answer is both...

I think you should mark all the rows you put in your flat files as "processing", and then when finished, you should look at all the PK's you replicated and mark those as "replicated".

Then, if you have any flags still set to "processing" you know you had some troubles. Otherwise your flag is really more of a "I did the work and it should have gotten this one

 

[saltbits]

Thanks, skiflyer. I'll remmbr the advice.