Where Can I Find Job Notification Attributes in the Database?

Products

• CA Workload Automation AE - Scheduler (AutoSys)

Issue/Introduction

In the WAAE database, what table(s) contain all of the information pertaining to a job's notification attributes if they are set?
We would like to be able to extract the Jobs that has notification enabled

Environment

Autosys 11.x 12.x

Resolution

The various notification attributes for a job are spread out over three tables in the database.
The job name and the "send_notification" attribute value are in ujo_job.

All of the notification* attributes are in ujo_uninotify.
In that table, the "notification_emailaddress" is set to an ID that maps over to the ujo_strings table where the actual email address is stored.

Here is an example of a query that produces a basic list of jobs that have notification enabled along with their respective "notification_msg" and "notification_emailaddress" attributes...

select a.job_name,c.value,b.notification_msg
from ujo_job a,ujo_uninotify b,ujo_strings c
where a.joid=b.joid and a.job_ver=b.job_ver and a.is_currver=1 and a.has_notification=1 and b.notification_emailaddress=c.str_oid;

It is possible for a job to have multiple rows in the ujo_job table due to job versioning.
When you edit a job, a new row is created for it with the updated attributes and it is assigned a different job_ver value.
The original stays in the table for 7 days by default before it is archived.

Therefore, your query has to account for that so that it only shows the most current version of the job.
That is the purpose of the "a.job_ver=b.job_ver" and "a.is_currver=1" parts of the where clause.

Additional Information

Note:
For users with Oracle back-end databases, the FROM statement will need to be modified to include the schema name.
Example:
from <SCHEMA_NAME>.ujo_job a, <SCHEMA_NAME>.ujo_uninotify b, <SCHEMA_NAME>.ujo_strings c 

if the schema name is not known please engage your oracle dba to provide this information.