Netezza Stored Procedure Return Value

nvalia · Post by **nvalia** » Mon Apr 20, 2015 8:59 am

Hi All,

Since there is No Direct way of calling a Netezza SP from Datastage as the Stored Procedure stage does not support Netezza yet, so I am calling it from After SQL in a Netezza connector stage and it works thus far.

But I want to capture the return value from the SP and based on which conditionally halt the ETL cycle post this SP run via a Sequence job

How can I do this, has any one achieved this, appreciate any suggestions/solution

Thanks,
NV

chulett · Post by **chulett** » Mon Apr 20, 2015 9:42 am

What kind of condition? What you want to do isn't directly possible as far as I know - either the After SQL runs without error and your job succeeds or it "fails" and your job aborts.

You may need to break it out as a separate step in the Sequence, run after the job completes... perhaps called from the command line? Then you should be able to interrogate the results and handle your condition, I would think.

nvalia · Post by **nvalia** » Tue Apr 21, 2015 1:09 pm

For the scenario where I wanted to use a Return Code to halt the ETL cycle, I am now throwing an Exception in the Stored Procedure itself (instead of using a separate return code) and so now the SP fails conditionally..hencethe job fails and I can then halt the next processes in the Cycle using this in the Trigger condition in the Job Activity Stage

chulett · Post by **chulett** » Tue Apr 21, 2015 1:48 pm