DSXchange

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....

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.

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?

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 ...

we have to use only parallel jobs....

That's... silly.

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.

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.

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!

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

Thanks for all your responses....

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

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

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

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

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.