Which will kill your performance but prove (if it then works) that your design is correct.
Sudhindra_ps wrote:As you suggested I just sent both Insert link and Delete link into single DRDBMS stage and also ensured link ordering was proper from Transformer stage so that Delete operation works first and then followed by Insert operation on the Target system. But, all records from Insert link got rejected saying "Unique Key Constraint" violation on the target table as Delete operation wasn't complete before Insert operation could start.
Not possible. The second link
cannot start before the first link is complete. So, two things. One - you may not be deleting all records records associated with the constraint in one statement. You'd have to determine if the keys being deleted matched the keys being inserted. Two - what
Array Size settings are you using in both links?
Do they match? Same question for Transaction Size on each link.
If they
don't match link to link, what you could be seeing are records that have been updated properly with work done in the proper order but that
have not been sent to the database in the proper sequence.
Something else to consider. If the DRS stage supports this Update Action, you might try the 'Replace Completely' option. That would allow you to have one link where every record processed first deletes itself and then inserts the latest information rather than doing an update. Or simply perform an update - is the delete really necessary? It can be a pretty expensive operation, I'd hate to see you do it for no good reason especially with 'huge' volumes.
