Stored Proc Stage

aluthra48 · Post by **aluthra48** » Tue Oct 12, 2010 3:04 pm

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

chulett · Post by **chulett** » Tue Oct 12, 2010 4:59 pm

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.

aluthra48 · Post by **aluthra48** » Wed Oct 13, 2010 9:02 pm

I don't raise an exception in the proc, but I am not sure how to configure the stp stage to capture it if I did. I have seen the custom error definition tab, but I am not sure how to define it.

Any description will be helpful.

Thanks

chulett · Post by **chulett** » Thu Oct 14, 2010 5:27 am

Raise an exception and it will be captured automatically.

aluthra48 · Post by **aluthra48** » Fri Oct 15, 2010 2:03 pm

I just changed the stored proc and raised an exception.

The Datastage job does not capture the error. It still finished status OK, while the the stored proc when executed independently aborted and raised the error.

Any ideas? Do I have to set up the custom error feature (and how?)

chulett · Post by **chulett** » Fri Oct 15, 2010 2:17 pm

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.

aluthra48 · Post by **aluthra48** » Fri Oct 15, 2010 2:22 pm

It's SQL Server.

Thanks

aluthra48 · Post by **aluthra48** » Fri Oct 15, 2010 2:28 pm

One other thing, how would one capture a return code from the sp? If the return code is set to -1, and if it can be captured by the sp stage (how?), then the next step would be to make the job fail if the return code is -1 (how?)

Thanks
Anil

aluthra48 · Post by **aluthra48** » Sat Oct 16, 2010 10:12 am

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.

aluthra48 · Post by **aluthra48** » Sat Oct 16, 2010 10:17 am

I am looking into the use of DSGetLinkInfo

aluthra48 · Post by **aluthra48** » Sun Oct 17, 2010 1:03 am

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