Topic
1 reply Latest Post - ‏2014-05-13T22:12:53Z by HLHuang
LukeNumrych
LukeNumrych
49 Posts
ACCEPTED ANSWER

Pinned topic Exception handling in DSWC

‏2014-04-03T13:48:42Z |

I have a Data Studio Web Console job which invokes a stored procedure - a simple CALL PROCNAME(); is the only statement in my job.

I would like to be able to signal back to DSWC when the stored procedure encounters any warning or error conditions during its run so that DSWC would know to send me a notification...

However:

  1. DSWC does not have a "warning" condition for notifications, only succeed or fail... so I guess the "warning" is not going to work.
  2. DSWC seems to disregard the stored procedure return value.  I tried returning values like -1, -2 from my stored procedure, but DSWC just assumed "success" regardless of the stored procedure return value.
  3. The only way I seem to be able to trigger DSWC job fail condition is by SIGNALing a SQLEXCEPTION from the procedure. This, however, makes the whole CALL statement a failed (and thus eligible for a ROLLBACK) transaction, does it not?  This is not what I have in mind.

Is this pretty much it, or am I missing something?

 

 

Updated on 2014-04-03T13:51:01Z at 2014-04-03T13:51:01Z by LukeNumrych
  • HLHuang
    HLHuang
    28 Posts
    ACCEPTED ANSWER

    Re: Exception handling in DSWC

    ‏2014-05-13T22:12:53Z  in response to LukeNumrych

    Hi,

       I was trying a SQL only script job that calls a store procedure and  found out  DSWC does not get any value from the  store procedure because

    it was calling callablestatement.execute(), which only return true or false on whether f the calls execute or not.  I tried to give a bad call such

    as  'CALL SYSPROC.ADMIN_CMD('RUNSTATS TABLESPACE SD');'  and it threw exception, therefore, show failure on running the job.

    So it looks only if there is an exception that it will cause the job to show failure.