How to capture the failed job status in the control table

[venkataramana_paidi]

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.

[former_member208402]

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.

[venkataramana_paidi]

Hi Ravi Kiran,

I think not all type of errors are captured by try/catch. For example some error syntax errors.

Take any example of sql statement. If we write any error in the sql statement it will not throw any error while validation.

If any syntax issue is there it will throw while running the job only.

This is syntax error and we cannot catch from catch statement.

I need to capture this type of errors also.

Thanks & Regards,

Ramana.

[former_member208402]

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.