Sequence Restartability in case of Routines
Posted: Tue Feb 19, 2013 8:58 am
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
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