Commit after the stop of a job

eoyylo · Post by **eoyylo** » Wed Jul 07, 2004 4:45 am

Hi,

Sometimes it is necessary to stop a running job that is writing in an oracle table. In this case the job do a COMMIT before to stop all activities.

In our job we have TRANSACTION HANDLING set to 0 because we want the commint at the end of the run.

Is it possible to configure the job or the DataStage Server to avoid the commit when someone stop it ?

Thanks in advance

Mario Loreti

chulett · Post by **chulett** » Wed Jul 07, 2004 7:01 am

How are you stopping the job? You need to do it in a way that causes the job to end 'abnormally' so the commit does not happen. I think you can do that from the command line but I don't know the syntax off the top of my head.

I'll double-check when I get into the office.

One sure fire way is to tackle it from the Oracle side... kill the Oracle process! The job will stop and no commit will occur.

eoyylo · Post by **eoyylo** » Wed Jul 07, 2004 7:35 am

Hi,
unfortunately the same parson that run the job would like to stop it clicking the "stop" button in Director. In this case is impossible to avoid the commit, right?

thanks

kcbland · Post by **kcbland** » Wed Jul 07, 2004 7:44 am

Stopping the job via Director will cause the job to abort, which means that the COMMIT at end of processing will NOT occur. As long as you have it set to 0, no rows should commit by using the STOP button in Director.

cecilia · Post by **cecilia** » Wed Jul 07, 2004 11:26 am

Hi
When I was developing in a customer two years ago with Oracle DB, I had jobs with transaction size set in 0. When I run these jobs in Dev environment, if they abort, they rollback. When I run them in Prod environment, if they abort, the rollback doesn't happen and the inserted record, keep in the table.
I told with Oracle DBA and he said that both environments had same configuration but I think he was missing something.
My conclusion is that database configuration has priority over DS settings.
Regards, Cecilia

trobinson · Post by **trobinson** » Wed Jul 07, 2004 1:54 pm

I would counsel against ever stopping a job via Director especially in a production environment. I liken it to executing a kill statement on a process in UNIX. My experience with it has not been a happy one. I don't believe it to be a method that has been coded to a production level of robustness. It is okay for developing jobs but should not be incorporated into standard operating procedures. Others might disagree. My prime example is that it won't always adequately stop a real-time job using MQseries. The job stops but more often then not, an orphaned child process will live on until killed manually.