Clarity

Expand all | Collapse all

Handy method to know job dependencies?

Jump to Best Answer
  • 1.  Handy method to know job dependencies?

    Posted 12-09-2014 12:27 AM

    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



  • 2.  Re: Handy method to know job dependencies?

    Broadcom Employee
    Posted 12-09-2014 01:11 AM

    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



  • 3.  Re: Handy method to know job dependencies?

    Posted 12-09-2014 04:50 AM

    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



  • 4.  Re: Handy method to know job dependencies?
    Best Answer

    Posted 12-09-2014 03:33 AM

    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.