Stored Proc Stage
Moderators: chulett, rschirm, roy
Stored Proc Stage
Hello,
I am executing a stored procedure using a stored proc stage. The stored procedure fails (for example while inserting a duplicate primary key value), yet the DataStage job finishes with an OK status. How can I make the DS job finish with an abort status?
Thanks
AL
I am executing a stored procedure using a stored proc stage. The stored procedure fails (for example while inserting a duplicate primary key value), yet the DataStage job finishes with an OK status. How can I make the DS job finish with an abort status?
Thanks
AL
Didn't you already post this? Looks really familiar but I can't seem to find it, hmmmm...
Regardless, a couple of things to check. Does the proc actually raise an exception when it has a problem? Also, have you looked at the tab in the stage where you define custom error levels? Don't have anything in front of me to refer to but do recall some functionality there along those lines. One of those two should solve your issue.
Regardless, a couple of things to check. Does the proc actually raise an exception when it has a problem? Also, have you looked at the tab in the stage where you define custom error levels? Don't have anything in front of me to refer to but do recall some functionality there along those lines. One of those two should solve your issue.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hmmm... what database? I honestly don't recall having to do anything special for Oracle but perhaps things change in other databases. Or I'm wrong.
Also realizing that I've been thinking of the SP stage in a Server job, perhaps it being in a PX job also changes the rules. Hopefully someone can shed some light on this for us.
Also realizing that I've been thinking of the SP stage in a Server job, perhaps it being in a PX job also changes the rules. Hopefully someone can shed some light on this for us.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I created an output link from the stppx stage which gave me the ability to capture the return code from the stored procedure, (set to -1 in the sp if it fails). I can now do one of 2 things.
I can configure a transform having the output link of the sttpx stage as its input, and an output link to a peek stage with the condition that the value of the return code equals -1. Then if I can configure some way to interrogate the number of rows flowing thru that link to the peek stage, and for the job to fail if it is more than 0, that will do it. I don't know how.
The other option is to code for fatal errors in the "Error Codes" tab of the "Stage" tab of the stored proc stage. I don't know how.
I can configure a transform having the output link of the sttpx stage as its input, and an output link to a peek stage with the condition that the value of the return code equals -1. Then if I can configure some way to interrogate the number of rows flowing thru that link to the peek stage, and for the job to fail if it is more than 0, that will do it. I don't know how.
The other option is to code for fatal errors in the "Error Codes" tab of the "Stage" tab of the stored proc stage. I don't know how.
OK, here's the solution that I came up with.
In the parallel job described earlier, instead of writing to a peek stage, the return code (-1 for failure, 0 for success) is written to a text file only if the return code is less than 0.
In a sequencer the text file is interrogated for word count using an "Execute Command" activity, followed by a User Variable activity to capture the word count in a user variable. This is followed by a nested condition activity with 2 links. Link 1 (failure, abort) is triggered with a custom condition for the user variable value >= 0 and Link 2 with a custom condition for the user variable value < 0
Link1 leads to a failure notification activity
In the parallel job described earlier, instead of writing to a peek stage, the return code (-1 for failure, 0 for success) is written to a text file only if the return code is less than 0.
In a sequencer the text file is interrogated for word count using an "Execute Command" activity, followed by a User Variable activity to capture the word count in a user variable. This is followed by a nested condition activity with 2 links. Link 1 (failure, abort) is triggered with a custom condition for the user variable value >= 0 and Link 2 with a custom condition for the user variable value < 0
Link1 leads to a failure notification activity