Handy method to know job dependencies?

[Anon Anon]

Hi Team,

Is there any handy query/any other way to know the job dependencies in clarity.

 

For example:

Datamart extraction is waiting in clarity schedule for the last two days.

Is there any query which would give me the list of jobs that is in progress/will be running - which is actually making Datamart extracting to be in waiting status?

 

Regards,

Naga

[Suman Pramanik]

HI Nagaragan,

 

This query can help to see what jobs are in processing state.

 

Select * from cmn_sch_job_runs where status_code ='PROCESSING'

 

Regards

Suman

[Deactivated User]

For starters see

https://communities.ca.com/message/98899493#98899493

https://communities.ca.com/message/44945321#44945321

Then use search

If a job is waiting for two days then something is fundamentally wrong.

If it really is just waiting for incompatible jobs to finish consider replacing your system with one that has adequate performance to handle you data volume.

The situation with jobs is that all jobs used to give log entries and it was easy to query what jobs ran and when they ran. Now if time slicing runs every minute, timesheets are posted every hour and so on there is going to be a lot of routine entries of jobs which most of the time run without problems. Queries still work OK, but the log pages are going to be full of indifferent data. So starting from  r 7.5. - r8.0 that was changed. Some of the jobs were hard coded not to give any log entry (which means the user cannto re-crete the job with logging enabled). That also meant that when you query for those jobs you do not get their schedule, but only see them in query results while they are running. Eg. If time slicing is run every minute and it runs for 5 secs you will see it in the query results for 5 secs every minute which is the same time it has processing status in the GUI. That was the long way of saying that you do not see all scheduled and ran jobs with queries.

[Legacy User]

This should list waiting jobs dependent on currently running jobs:

 

SELECT    CCN_WAIT.NAME WAITING_JOB_NAME, 

          CSJD_WAIT.JOB_CODE WAITING_JOB,

          CCN_RUN.NAME RUNNING_JOB_NAME,

          CSJD_RUN.JOB_CODE RUNNING_JOB,

          CSJR_RUN.START_DATE 

FROM      CMN_SCH_JOB_DEFINITIONS CSJD_RUN INNER JOIN

          CMN_SCH_JOBS CSJ_RUN ON

          CSJD_RUN.ID = CSJ_RUN.JOB_DEFINITION_ID INNER JOIN

          CMN_SCH_JOB_RUNS CSJR_RUN ON

          CSJ_RUN.ID = CSJR_RUN.JOB_ID INNER JOIN

          CMN_SCH_JOB_NONCONCUR CSJN_RUN ON

          CSJD_RUN.ID = CSJN_RUN.NON_CONCUR_JOB_DEF_ID INNER JOIN

          CMN_SCH_JOB_DEFINITIONS CSJD_WAIT ON

          CSJN_RUN.JOB_DEFINITION_ID = CSJD_WAIT.ID INNER JOIN

          CMN_SCH_JOBS CSJ_WAIT ON

          CSJD_WAIT.ID = CSJ_WAIT.JOB_DEFINITION_ID INNER JOIN

          CMN_CAPTIONS_NLS CCN_WAIT ON

          CSJD_WAIT.ID = CCN_WAIT.PK_ID INNER JOIN

          CMN_CAPTIONS_NLS CCN_RUN ON

          CSJD_RUN.ID = CCN_RUN.PK_ID

WHERE     CCN_WAIT.TABLE_NAME = 'CMN_SCH_JOB_DEFINITIONS' AND

          CCN_WAIT.LANGUAGE_CODE = 'en' AND

          CCN_RUN.TABLE_NAME = 'CMN_SCH_JOB_DEFINITIONS' AND

          CCN_RUN.LANGUAGE_CODE = 'en' AND

          CSJR_RUN.STATUS_CODE = 'PROCESSING' AND

          CSJ_WAIT.STATUS_CODE = 'WAITING' AND

          CSJN_RUN.JOB_DEFINITION_ID != CSJN_RUN.NON_CONCUR_JOB_DEF_ID