Performance with Change Capture Stage

Raamc · Post by **Raamc** » Wed Nov 18, 2009 8:23 am

Hi,

I have a requirement to compare a table before update and after update.
I have 10 columns in each record and table is having around 20 millions of records.

I want to run a comparison over 20 millions of records.

In this case, is it feasible to use CHANGE CAPTURE stage to get the differences (insert/update/delete) records?

If I use CPC stage, how about the performance on 4 node config file?

Any rough idea how long it may take for comparisons over 20million records?

ArndW · Post by **ArndW** » Wed Nov 18, 2009 9:23 am

If you are doing the updates then you already have that information without having to go to the actual table, right?

Performance guesstimates are impossible at this time - are you running on a single-CPU 486 with 100 other users or do you have a 32 Processor SMP system all to yourself? Is the database local or remote and how tuned is it? Even with those answers it is impossible to guess (are your records 10 bytes or 10000 bytes wide?)...

Raamc · Post by **Raamc** » Wed Nov 18, 2009 9:32 am

Thanks Arndw,

I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.

Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?

Witgout using the CPC stage, is there any other way to find update/delete/insert records?

Raamc · Post by **Raamc** » Wed Nov 18, 2009 9:32 am

Thanks Arndw,

I am not doing the updates. Some other system will do the updates and that updates will flow into my system. I can find changes only by comparing before and after images.

Any performace tunning methods before using CHANGE CAPTURE stage. I am sorting the data other than this any other thing i need to do?

Witgout using the CPC stage, is there any other way to find update/delete/insert records?

ArndW · Post by **ArndW** » Wed Nov 18, 2009 9:50 am

It is best to avoid having to do a full table scan - how about adding an indexed column which has a different value when the external application does an update than when DataStage does it, then you could do a select based on that column's value and only get changed records back to DataStage.

RAJARP · Post by **RAJARP** » Sat Nov 21, 2009 8:01 am

HI RAAMC,
There is an alternative for change capture stage.use a join stage with join type as 'full outer join'.Hash partition both the input links(sort,null first and ascending).
You can easily figure out the deletes,inserts and updates in the new data.use a tranformer stage following the join stage.In the constraint give the following constraints
Inserts-->when the old_link.Key=''
Deletes-->when the new_link.key=''
Updates-->when old_link.key<>'' and new_link.key<>''.
This would help you.Actually this would help you .I have tried this logic for two tables each containing more than 20 million records.

Regards,
Raja R P[/code][/quote]

chulett · Post by **chulett** » Sat Nov 21, 2009 8:08 am

That doesn't sound substantially different than what the stage being discussed does. Have you run your 20M records both ways and compared the results?

ray.wurlod · Post by **ray.wurlod** » Sat Nov 21, 2009 5:41 pm

This stage type is not available in server jobs, yet you marked the job type as server. Are you looking for a server job solution?