When querying the d/b, trying to find out what process is being run by the above job. This is purely in the d/b not through the UI.
We were able to find the answer:
Select a.name, Case When c.attribute_id = 121005 Then 'Process Name' When c.attribute_id = 121004 Then 'Process ID' End as Description, c.attribute_value from CMN_SCH_JOBS a Join cmn_attribute_value_sets b on a.id = b.business_object_instance_id Join cmn_attribute_values c on b.business_object_instance_id = c.attribute_set_id where c.attribute_id in ('121005','121004')
Check if this helps:
select distinct job_definition_id from niku.CMN_SCH_JOBS
where name='Execute a Process'
select * from niku.CMN_SCH_JOB_RUNS A, niku.CMN_SCH_JOBS B
where A.job_id =B.id
Unfortunately this only gives us information on what process is being run by the 'Execute a Process' Job once it has started. What we want to do is build up a list whilst these jobs are still 'scheduled'.
Surely the information should be held somewhere whilst the job is still scheduled? You can schedule jobs years in advance, it won't hold it in memory all that time.
The reason we are looking to do this is to try to build a temporary table with all the scheduled jobs which our job monitoring can use to build its scheduling.
Hi JonathanDeane76040996 - Did any of the responses help answer your question? If so please mark as Correct Answer. Thanks!
Hi JonathanDeane76040996 - Did navzjoshi00's response help answer your question? If so please mark as Correct Answer. Thanks!
JOB.PARAMS.VALUE will give Process Code. You can do further joins with (BPM_DEF_PROCESSES and CMN_CAPTIONS_+NLS), if you need Process names.
sysdate, job_info.id job_id, job_info.name job_name, job_info.created_by,
job_runs.start_date, job_runs.end_date, (job_runs.end_date - job_runs.start_date)*24*60*60 duration,
job_runs.status_code, scheduler_id, job_runs.output_path
, job_params.attribute_id, job_params.value
cmn_sch_jobs_v job_info, cmn_sch_job_runs job_runs, cmn_sch_job_definitions job_defs, cmn_sch_job_run_params job_params
job_runs.job_id = job_info.id and language_code = 'en'
and job_info.job_definition_id = job_defs.id
and job_params.job_run_id = job_runs.id
and job_info.name like 'Execute a Process%'
Below query will give you the requested details.
Basically we need to use a View(CMN_SCH_JOBS_V) along with the table in order to get scheduled information.
SELECT CSJ.NAME EXEC_PROC, CSJV.NAME SCHEDULED_PROCESS_NAME, CSJV.SCHEDULE_DATE SCHEDULEDDATE
WHERE CSJ.JOB_DEFINITION_ID = CSJV.JOB_DEFINITION_ID
AND CSJV.LANGUAGE_CODE = 'en'
AND CSJV.SCHEDULE_DATE IS NOT NULL
AND CSJ.NAME='Execute a Process'
Retrieving data ...