When searching through CMN_SCH_JOBS, want to find out which processes the 'Execute a Process' job is running?

[Legacy User]

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.

[navzjoshi00]

Check if this helps:

 

https://communities.ca.com/message/98569877

 

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

AND B.job_definition_id=50560

 

 

NJ

[Legacy User]

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.

 

Chris_Hackett

[System]

Hi JonathanDeane76040996 - Did any of the responses help answer your question? If so please mark as Correct Answer. Thanks!

[System]

Hi JonathanDeane76040996 - Did navzjoshi00's response help answer your question? If so please mark as Correct Answer. Thanks!

[Legacy User]

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.

 

SELECT

 

 

sysdate, job_info.id job_id, job_info.name job_name, job_info.created_by,

job_info.status,

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

FROM

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

 

 

WHERE

 

 

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%'

 

 

ORDER BY

job_runs.end_date desc;

[Jagadeesh Maddineni]

Hi Jonathan,

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

FROM

CMN_SCH_JOBS CSJ,

CMN_SCH_JOBS_V CSJV

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'

 

Best Regards

Jagadeesh

[Legacy User]

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')