Rollback the data if the datastage job get aborted

pandujoy · Post by **pandujoy** » Mon Dec 21, 2009 11:16 am

Hi,

I have designed a DS job where i will be inserting /updating data into 6 different tables. Since being a webservice i have to develop the service as one single job.

The problem i am having is if the job gets aborted ....few of the initial tables are being inserted with some data...instead since the job got aborted we have to rollback the transaction.

Appreciate your advices....

chulett · Post by **chulett** » Mon Dec 21, 2009 12:01 pm

I've done this in a Server job using a single ODBC stage and issuing an explicit 'commit' or 'rollback' when needed. No clue how you would manage that in a PX job where you have to use multiple stages.

pandujoy · Post by **pandujoy** » Mon Dec 21, 2009 12:10 pm

Thanks Chulet.....I am a bit confused how to manage this in PX jobs

Is there way where i can write a script to roll back if the jobstatus code is aborted?

JRodriguez · Post by **JRodriguez** » Mon Dec 21, 2009 12:51 pm

How about using a server job instead? Unless your shop is parallel jobs only.

By definition webservice jobs shouldn't be processing a lot of records ...

pandujoy · Post by **pandujoy** » Mon Dec 21, 2009 2:07 pm

we have to use only parallel jobs....

chulett · Post by **chulett** » Mon Dec 21, 2009 2:23 pm

That's... silly.

kduke · Post by **kduke** » Mon Dec 21, 2009 5:26 pm

You could blow out your rollback segment in the database. I thought if the transaction size was 0 then it commits all or nothing. You could also commit one row at a time. That may solve your problem.

chulett · Post by **chulett** » Mon Dec 21, 2009 6:24 pm

Problem is it's deployed as a service, which usually means "always on" and so never commits until you shut it down or commits every X records regardless of what's going on. That's why we went with Server jobs and a single ODBC stage since it supports multiple links and doesn't care if all columns are bound into the SQL and thus allows links that just say "commit" or "rollback". You then trigger whichever is appropriate for each "unit of work" you need to either make stick or go away.

And before I take any credit for that approach, it came from Ernie. Down from the mountain on stone tablets.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 22, 2009 4:02 am

pandujoy wrote:we have to use only parallel jobs....

So how come you posted in the Server Edition forum?

Moderator: please move to Enterprise Edition forum

Resist stupid requirements!

eostic · Post by **eostic** » Tue Dec 22, 2009 7:29 am

This is one of those things that EE historically cannot do without a custom Operator. The new "DTS" helps, but there are still scenarios, such as non-Oracle and DB2 sites that need explicit Rollback and Commit functionality. If you need this, use a Server Job with ODBC and a single Transformer if you can.

Ernie

pandujoy · Post by **pandujoy** » Tue Dec 22, 2009 9:11 am

Thanks for all your responses....

If it is in the case of server jobs...can anyone guide me how to do that....

chulett · Post by **chulett** » Tue Dec 22, 2009 9:12 am

So... have you not been reading the reponses? That guidance is there.

pandujoy · Post by **pandujoy** » Tue Dec 22, 2009 9:25 am

oh ya i just came to work.....

"You could blow out your rollback segment in the database. I thought if the transaction size was 0 then it commits all or nothing. You could also commit one row at a time. That may solve your problem."

do you mean this would work for me if i work on server jobs

pandujoy · Post by **pandujoy** » Tue Dec 22, 2009 9:31 am

Chulett....in the server job ODBC stage we have

Read Uncommitted
Read committed
repeatable read
serializable
versioning
auto commit

if i am not if i specify "0" for read commit the rollback feature should work but being multiple stages used ...how can i solve my prob. even it is a server or parallel...i did investigate about DTS stage but i think 8.0.1 doesnt have DTS stage features....i have seen it in 8.1 version but not in 8.0.1

kduke · Post by **kduke** » Tue Dec 22, 2009 10:39 am

If this job was running as a normal job and not a service and you had lots of rows then to commit these all at once means the database has to store them in the rollback segment. The rollback segment is limited in size. The DBA would need to increase this size to handle the way you are running this job.

All these things need to work together. The ETL and the database have to work together. So how you commit your records impacts the DBA.