SQL Job remains active when connection to DB encounters an issue

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Error Message :
N/A

SQL Jobs remain active even when an issue with the database is encountered such as the TNS listener not found or a DB userid account is locked.  The SQL job does not fail and the only way to determine there is a problem is to look in the Job report where you will see the database error and then cancel the Job.  

If the SQL itself fails, then the Job will fail as expected.

 

Environment

OS Version: N/A

Cause

Cause type:
Configuration
Root Cause: Database connection errors do not cause SQL jobs to fail.

Resolution

Use the "Runtime" tab of the SQL Job to set a maximum runtime (MRT).  If the MRT is reached, execute a tasks to check for connectivity to the DB and send an email/msg/Notification. Then cancel the SQL Job.

Fix Status: No Fix

Fix Version(s):
N/A

Additional Information

Workaround :
N/A