Autocommit

dr.murthy · Post by **dr.murthy** » Fri Mar 06, 2015 10:20 pm

HI,

i have job to get the data from oracle connector and doing some tranformations then finally it loads into SQL server using ODBC connector.This job is working fine as expected, but only concern is incase if the job fails in middile it inserting the partial data into SQL server table, have to delete the data manually for next run.

is there any option in datastage to restrict once the job completes succesfully then only load data into table.

i tried with auto commit off and transaction size 0 but it behaves like if my job fails in the middle of the run it loads the partial data into table.

my job design soemthing like similar to Source --> transformer --> SQL Server , here lets say in my source have 100 records in transformer i set a condition abort the abort after pass 10 records to next link(SQL Server) finally 10 records loading into target table but i dont want to load these 10 records if my job abots.

can any one please suggest on this?

chulett · Post by **chulett** » Sat Mar 07, 2015 8:23 am

Looks like the official answer is to set Autocommit mode to off and Record Count to zero. If that's not working, then I would involve support.

dr.murthy · Post by **dr.murthy** » Sat Mar 07, 2015 10:52 am

Thanks for responding Chulett.

I tried above option but its not working

chulett · Post by **chulett** » Sat Mar 07, 2015 11:44 am

Make sure you have all the latest fixpacks for your version and if so, involve support.

chulett · Post by **chulett** » Sat Mar 07, 2015 11:45 am

Also note the docs specifically say Record Count rather than Transaction Size should be set to zero...