Controlling Data Commit While Loading Multiple Tables

[jerome_rajan]

Hi,

This was a question that came in a brain storming session and I was wondering if any of you have encountered this before.

Assume, we load 10 different tables in 10 parallel jobs all within the same sequencer. Let us also assume that all these laods happen sequentially because of integrity requirements.

Now, if my last table load fails / aborts, I want to ensure that all the previous loads are undone / rolled back.

Is there any way to ensure that the commit is held back until the last job?
To the best of my knowledge, the connector seems to commit a transaction individually.

Any assistance in this matter would be very helpful. Thanks!

[chulett]

Across jobs? Can't be done, at least nothing automatic. That's not to say you could build something to go back and "undo" a partial load but you would need to code the deletes / updates needed to reverse the load to that point.

[qt_ky]

There used to be a Distributed Transaction stage you could use for some purpose like that within one job to insert/update/delete into multiple tables then commit.