Six Stars

How to Commit and Rollback in all the target tables simultaneously

Hi i am using TOS 6.2.1 licensed version.
i have made a job in which i am extracting data from a single source and and inserting into 3 different target tables depending upon conditions.
i want to commit all the 3 target tables if all the appropriate data are being populated successfully and if from 3 targets the data are not being populated in any of the target tables then rollback will be executed in all the 3 tables.
i need suggestion as i am confused hoe to do so.. any kind of suggestion is appreciated.
thanks in advance.
regards,
nabarun
5 REPLIES
Fifteen Stars

Re: How to Commit and Rollback in all the target tables simultaneously

Use a t{database}Connection component to provide your connection. Then at the end of your job use a t{Database}Commit or t{Database}Rollback depending on your success/failure criteria.
Rilhia Solutions
Six Stars

Re: How to Commit and Rollback in all the target tables simultaneously

Hi rhall,
thanks for your response. but the solution you are saying is to commit or rollback on a single target table. let me explain my conditions again..
row 1 : taget1 , target2 , target3 , all sucess-------> COMMIT
row 2: target 1 sucess , target 2 sucess: target 3 failed,then need to roll back on target 1 and target 2 for row 2 along with target 3.
regards,
nabarun
Fifteen Stars

Re: How to Commit and Rollback in all the target tables simultaneously

The t{Database}Connection component would be used for ALL of your tables, not just one. Likewise the t{Database}Commit and Rollback would used for all tables. The (very rough) layout is shown below....
Connection
|
|
|               ---> Target1
Source -------> Target2
|              ----> Target3
|
|
Commit/Rollback
Rilhia Solutions
Six Stars

Re: How to Commit and Rollback in all the target tables simultaneously

In the layout you have shown, the commit and rollback will be executed once the data will be passed through the source. but if the data will be passed through the source but before loading to target it got failed, then also it will be committed, which i want to avoid.
i want the data to be committed or rolled-back depending upon the loading in the target tables not on the source.
thank you for your response though. Smiley Happy 
Fifteen Stars

Re: How to Commit and Rollback in all the target tables simultaneously

Use a tPostJob component. It is not connected to the main flow and is always executed even if there is a Java error. Connect your Commit and Rollback to that component. You will also need to use some logic based on the number of rows input and the number of rows output at each target. This is easy enough using the component standard globalMap variables.
Rilhia Solutions