Sequence Restartability in case of Routines

[rohit.agarwalin]

Hi,

I have a scenario where I am running few routines and these routines are calling oracle sql execution utility (this utility just submits the SQL what ever we passed to oracle with proper credentials and return back the value by oracle).

Using this utility I am submitting INSERT or MERGE commands etc. so the return value of the routine is something like '200 rows merged'.

Now DataStage understands that '200 rows merged' is return value of routine and so it logs warning that routine does not finished successfully since it is expecting successful return value as 0.

So when we restart sequence after some failure point then it re-run this routine though it is already finished. Since DataStage does not get it's expected return value (0) that is why it is running routines on restart.

We tried using terminate stage etc but nothing is working since return value is not as expected.

Could you please suggest how we can handle this situation and if submitted SQL finished successfully then it returns the proper value (0).

Thanks & Regards,
Rohit

[chulett]

It only automatically handles the routine if it thinks you did not, this is explained in the documentation. Run two links from the Routine, one "OK" and one "Otherwise" to a Sequencer set to "any" and it won't consider the failure unhandled.

[ray.wurlod]

If Craig's suggestion doesn't work, try an explicit Failure trigger.

[rohit.agarwalin]

It only automatically handles the routine if it thinks you did not, this is explained in the documentation. Run two links from the Routine, one "OK" and one "Otherwise" to a Sequencer set to "any" and ...

Thanks Craig. But I am not able to see entire content. Could you or any one please post it as normal content again.

Thanks again.

Regards,
Rohit

[chulett]

Sorry but that's not the way it works around here. You can see enough right now to solve your problem... and you could always try looking through the documentation. As noted this is all explained there with specific examples.

[rohit.agarwalin]

Hi,

I gone through the documentation and I read how to handle errors or return values from routine using exclusive triggeres etc.

But here the issue is when we restart sequence after some failure point then it re-run this routine though it is already finished. Since DataStage does not get it's expected return value (0) and it stores the non zero value returned by routine.

We tried using terminate stage etc but nothing is working since return value is not as expected.

Could you please suggest how we can handle this situation and if submitted SQL finished successfully then it returns the proper value (0).

Or may be you could please let me know the exact link of the documentation if you think this problem is described.

Regards,
Rohit

[chulett]

OK... why not simply change the routine so it returns a zero when everything goes ok and a non-zero when something goes wrong? I don't see any reason for it to return text like "200 rows merged" but then we don't know what exactly the routine does. Is this command line sqlplus? You have complete control over what any routine returns so it shouldn't be difficult to correct this.

Still don't think you absolutely need to do this but it would make your Sequence handling easier. There's no reason for the sequence to think the routine "failed" and thus rerun it on a restart if you get the trigger handling correct - even with a non-zero return. I've done this very thing going back to before the 7.x release so I know it can be handled.