Skip to Content

How to capture the failed job status in the control table

Hi,

I want to capture the job execution details in the target table.

If the job executed successfully , we can capture the status,start time & end time properly.

If the job fails with errors that is not captured by try/catch block, how can we capture the end time & status in the control table.

Please suggest me this.

Thanks & Regards,

Ramana.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 31, 2017 at 09:19 PM

    Hi Venkata Ramana Paidi,

    Write a script inside catch to insert/updates values in the control table for failed status.

    If a job fails and if it has try and catch block in it, it will surely enter catch and execute this script.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Venkata Ramana Paidi,

      you can achieve this using multiple try and catch blocks in your job. Suppose you have only 1 dataflow in your job then design your job like below

      Try---> DataFlow---->Script---->Catch----->Try------>Script_email------>Catch.

      If you use multiple Try and Catch blocks then if your 1st dataflow fails then job will throw error but it will not get terminated, it will continue its execution and executes the object that is in between next try and catch.

      So 1st create a Global variable $G_ERROR. Then in the script after 1st Dataflow in the above flow, set $G_ERROR='N';

      In the Catch take a script and set $G_ERROR='Y', if dataflow fails then job enters Catch and sets

      $G_ERROR='Y' (even if Catch doesn't capture all types of errors it will execute this script which is inside catch and vairable value will be set), Now we can write script outside try and catch for error email notifications or for updating control table for failed status.

      Now in the Script_email write a if condition like

      If($G_ERROR='Y')

      begin

      sql('DS','update control_table set STATUS='FAILED' ....');

      end

      else

      begin

      print('Job completed successfully');

      end

      How this works is,

      if dataflow fails, then script will not get executed and $G_ERROR value will be set to 'Y' in catch and job starts executing script_email which is in 2nd try and catch and here if $G_ERROR='Y' means the previous dataflow failed so you can insert or update status here.

      Thanks,

      Ravi kiran.