One Star

Rollback and Commit for each records

Hi All,
We are design a job using the Talend for the below requirment.

1) Record will process one by one.
2) If any error occured for one record it should rollback the same record.
3) All the successful records should commit.

We have design the job like below. But we are not able to achive the result.
One record is errorign out in the 2nd level, but it is not rolling back the
error records in the 1st level.

We are using the same connection in all the place.

Could you please let me know how we can achive it?

Thank you,
RKP
7 REPLIES
Seventeen Stars

Re: Rollback and Commit for each records

1. Disable in the advanced properties of the output component the batch mode
2. use the main flow from the output comp to send it to a db row comp and write as SQL "commit"
3. use the now available reject flow from the output component to send it to a db row (e.g. tOracleRow) component and write as SQL here "rollback".
Seven Stars

Re: Rollback and Commit for each records

Hi jiolling,
I have one doubt,
if we Disable the batch mode then performance of the job will be slow.then how can i solve my problem.
I want high speed of inserting and also none of the record should miss.every record should insert.
please please help me out in this.
Appreciate for quick response
regards,
rekha.
Twelve Stars

Re: Rollback and Commit for each records

if we Disable the batch mode then performance of the job will be slow.then how can i solve my problem.

Exactly, 
and it clear - it will be the same like if You make single INSERT of 1000 values or open cursor and run 1000 inserts
Batch - it batch, and If You do not need them, will be ready for drop speed
-----------
Seven Stars

Re: Rollback and Commit for each records

hi vapukov,
Thank you for your response
So if i need all the records,then i need to compromise with the speed.
Twelve Stars

Re: Rollback and Commit for each records

right
but! all depends - why You expect unsuccessful records?
What must happens for database level reject? (this is a question for You)
If You know this - You can reject this records before INSERT, than use batch insert for all successful
-----------
One Star

Re: Rollback and Commit for each records

Yeah. Ideal approach will be do do this process in 2 steps:
1. Run flow to identify reject records. Remove them or mark them
2. Process all the successful records in batch mode. 
Going at the record level will decrease the performance of your job. 
One Star

Re: Rollback and Commit for each records

How do you do that?
can you post a screenshot?