Roll back is not working

[sid19]

Hi All,

We have following job design in server.


Some Processing ----> Transformer. Two out put links from transformer
1) Db2 Stage (writes into DB2 database)
2) Aggregator - It has one out put link to 2nd Db2 Stage (Writes into Iseries file)




Transaction size in both the DB2 stage is set to zero. Our job gets aborted due to access issue in Iseries file (2nd Db2 Stage). But even that Job committed the Data in Db2 database (1st Db2 stage). It didn't rollback the Data. Please explain why it is happening.

Is there any way to set the commit after the job successful completion other wise rollback?

[chulett]

The first DB2 stage gets its last row and thus closes/commits before the second one does due to the Aggregator. No, there's no way to defer commits until everything completes.

[greggknight]

Thats a database thing.
They are two seperate processes. If all the rows in the first process completed correctly then they will commit regardless of what the second stage does.

If this cannot happen then I would load the tables in two seperate jobs.
I would then put some logic in so that if job two fails it clears out the data that job one inserted before the seq or batch does an abort.


Thats one way.

[sid19]

Thanks Craig/Gregg,

Splitting the job will work. Or we can use Update then insert while loading. Is there any overhead?

[chulett]

I don't see how insert/update plays any role here.