Finding unique jobs defined has been challenging if you don't know how to work with the definitions in the CLOB, which I don't.
We have a lot of jobs defined in multiple flows and we want to account for each of those so I run the sql matching up
appl name, job name, job qualifier. The below SQL isn't 100% accurate but it gets us pretty close.
Down falls to sql against generic job and application tables
*if applications/job are defined but turned off and if they have no run history in ESP_APPLICATION or GENERIC_JOB they are not accounted for
*retired application/jobs that still have run history will be accounted for
*you would have to decide if you want to run again ESP_ and/or H_ tables, if you want to run again both there is are views set up V_
SQL
select distinct WA.ESP_APPLICATION.APPL_NAME,WA.ESP_Generic_job.job_name,WA.ESP_Generic_job.job_qualifier
from WA.ESP_Generic_job,WA.ESP_APPLICATION
where WA.ESP_APPLICATION.APPL_ID=WA.ESP_GENERIC_JOB.APPL_ID;
Sharon