Issue with Transaction Commit

dxk9 · Post by **dxk9** » Thu May 17, 2012 12:06 am

Hi,

I have a server job which fetches data from a DB2 table based on the Flag value & inserts into another DB2 table. Also, one field (flag) in the source table is updated as and when data is loaded to the target DB2 table.

Db2 table (X) ---> Transformer -----> Db2 table (Y) & Db2 table update (X)

The issue is, if the jobs completes successfully, then the flag logic works fine. But if the job aborts in between, then the flag update done during the run is not reflected in the table X. And once we restart the job, the job starts from the beginning processing all the 'n' records. The implementation of the Flag logic is not working in this scenario.

I tried different settings of transaction Isolation & transaction size, but it didn't help.

Please help me with the exact settings that needs to be done.

Thanks,
dxk9

Kryt0n · Post by **Kryt0n** » Thu May 17, 2012 12:55 am

Don't update X until Y has completed... write the records off to a file or implement some kind of logic that will look in to Y to see what should update in X

dxk9 · Post by **dxk9** » Thu May 17, 2012 2:14 am

Hi,

Thanks for your response.

The issue here is, we fetch rows from X where flag = 'N', and when it loads into Y, we also update the flag = 'Y' in X.

The update cannot be done in different job cause everytime we have to run the File to update X job as well. So if the actual job fails, then the update job has to be run manually before re-starting the actual job. This is not feasible.

Plz let me know how to implement the fetch & update on the same table in the same job. Also, needs the table to be reflected with the updates even if the job fails in between.

Regards,
dxk9