I have a job which has multiple tables involved in transaction .
1>How do I use commit and rollback, Basically on which place to be used, as I have so many sub jobs connected.
2>Where to use on component OK & where to use On sub job OK. My job is working fine. But I need to know the particular use.
Example how to implement commit -
Example how to implement rollback -
ad 2) OnComponentOK - for a single component check, OnSubJobOK - when you need to check that all components in the subjob finished successfully.
Ok. Thanks. But how to design when there are multiple transformation on a same job.
Here I need all table load happens once or none. from youe suggestion I can go for individual commit & rollback but what about whole thing.
There is no easy solution of this task. The only correct solution is to do it in one transaction to be sure that data will stay consistent. As far as I know, there is no possibility to commit several transaction as a one. It is always one by one and this can cause inconsistency.
I recommended one solution/workaround for MySQL in the following thread:
Please check it and let me know.