Roll Back in ODBC

kumar66 · Post by **kumar66** » Fri May 21, 2010 6:08 am

Hi All,,

I am loading data from Db2 to SQL Server and for SQL Server I am ODBC stage.

I want to roll back the rows if the job gets aborted in the middle.

Is there any way we can do that in Parallel in ODBC stage?? Or any other approach?

Please Advice.

Thanks & Regards,
Kumar66.

chulett · Post by **chulett** » Fri May 21, 2010 6:46 am

That should happen automatically as long as two things happen:

1. No intermediate commits are done
2. Job actually aborts rather than finishes with warnings

kumar66 · Post by **kumar66** » Fri May 21, 2010 7:04 am

Hi Chulett,

I am not doing any commit or setting any row commit interval and the job is getting aborted. Still the rows are not getting roll backed.

Please Advice.

Thanks & Regards,
Kumar66

chulett · Post by **chulett** » Fri May 21, 2010 7:08 am

Then they are getting comitted. Are any rolled back at all? What 8.x version? Have you asked your official support provider the same question?

kumar66 · Post by **kumar66** » Fri May 21, 2010 7:15 am

Hi Chulett,

Its version 8.0.1 .

Thanks ,
Kumar66

chulett · Post by **chulett** » Fri May 21, 2010 7:24 am

kumar66 wrote: Its version 8.0.1 .

Known to be... buggy. Contact your official support provider and see if there's a patch you're missing.

Seriously consider upgrading ASAP to the latest 8.1.x release.

kumar66 · Post by **kumar66** » Fri May 21, 2010 7:42 am

Hi Chulett,

Is there any other approach or design that can be done.
I have given Method = Write and Mode =Append.

When ever my job fails , it should not load the data already loaded.
For e,g if my source has 10 records and my job aborted at 6 record , If i am running my job it should load from the 7 th record.

Please Advice.

Thanks & Regards,
Kumar66.

chulett · Post by **chulett** » Fri May 21, 2010 11:00 am

You need to design that capability / smarts into your job. Sorry, no time for a proper explanation, perhaps others can chime in.

vinnz · Post by **vinnz** » Fri May 21, 2010 12:15 pm

kumar66 wrote: I have given Method = Write and Mode =Append.

When ever my job fails , it should not load the data already loaded. ...

Have you tried using the Upsert method?

probal · Post by **probal** » Fri May 21, 2010 8:26 pm

there are some confusions.
I don' know how 'Append' mode is checking for the previous data that was inserted.It shouldn't do this unless there is a unique constraint on that specific column.Use 'truncate' in that place.

Ideally in datastage if the job aborts the rows should be rolled back.but it is inconsistent(might be due to some specific patch).in that case u have to manually truncate the table.

p.s. use odbc enterprise stage.