Abort job when a Routine of the type Transform Function fail

[dsedi]

Hi,
We have a routine which connects to a Oracle Database and calls an Oracle Function. If the routine fails becuase of reasons like failing to connect to database then the routine fails. When this routine fails, it is required that the job, in which this routine is being called in a Transformer, also aborts.

Can anyone suggest the way to abort the job while the routine fails?
As such tried DSLogFatal but from the help, we were able to conclude that this can be done in the Before/AFter job subroutine and not in the Transformer.

Any help would be highly appreciated

Thanks

[Klaus Schaefer]

The only reason I see why not using DSLogFatal in a transform is that it is "not a good practice". You have, of course, to take care of the abort situation of the job and the further processing of your sequences...

However, in very rarly cases I do so and I don't see technical reasons in not doing so.

Any one may correct me who is aware of other reasons why not using DSLogFatal in a transform...

Klaus

[ray.wurlod]

If you are in a before/after subroutine, the "recommended" way to stop the job is to set the ErrorCode argument to a value other than zero.
This has the effect of stopping the job when the subroutine returns.

While DSLogFatal can be invoked from either a before/after subroutine or a transform function, using DSLogFatal causes an immediate abort (this is how it is documented). Cleaning up afterwards becomes a tedious manual process; further, depending on the arguments to DSAttachJob, the parent job and its controller(s) may not detect the aborted job and wait forever for it to finish, showing a status of "running".

Best practice is to set some kind of a flag, and detect that it has been set before proceeding with the next step, so that an orderly shutdown of the hierarchy of controlled jobs, and possibly the ability to notify someone who needs to know, can all be accomplished. There are several ways in which this can be achieved; a search of the Forum will reveal most of them.

[dsedi]

Thanks a lot for the responses.

Firstly, we are calling the routine in the derivation part of the Transformer.

Moreover when we use DSLogFatal, indeed we need to do a manual clean up, then is there anyway we can stop the job and still have it in the reset state. More importantly we need to do this when the routine fails which is called in the derivation part of the Transfomer and not in the BEFORE/AFTER job subroutine.

Hi,
We have a routine which connects to a Oracle Database and calls an Oracle Function. If the routine fails becuase of reasons like failing to connect to database then the routine fails. When this routine fails, it is required that the job, in which this routine is being called in a Transformer, also aborts.

Can anyone suggest the way to abort the job while the routine fails?
As such tried DSLogFatal but from the help, we were able to conclude that this can be done in the Before/AFter job subroutine and not in the Transformer.

Any help would be highly appreciated

Thanks

[ray.wurlod]

No.

As soon as you call DSLogFatal that job aborts immediately. Therefore any after-stage or after-job subroutine can not be executed.
That's why we recommend not to use this function.

The only way you can detect an aborted job (and, possibly, reset it) is in its controlling job.

Why not use an OCI or ODBC stage to invoke a stored procedure, rather than try to do it in code?

[roy]

Hi,
If Ray's last sujestion is not applicable in your case, you could set a user status to this job and in the controling job/sequence branch acordingly.

IHTH,