Is there any handy query/any other way to know the job dependencies in clarity.
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?
This query can help to see what jobs are in processing state.
Select * from cmn_sch_job_runs where status_code ='PROCESSING'
For starters see
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.
This should list waiting jobs dependent on currently running jobs:
SELECT CCN_WAIT.NAME WAITING_JOB_NAME,
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