Clarity

 View Only
Expand all | Collapse all

Some of the clarity jobs are going to waiting status

  • 1.  Some of the clarity jobs are going to waiting status

    Posted Aug 08, 2017 12:29 PM

    All,

    Some of my clarity jobs are going to waiting status, even if there is no incompatible jobs running also. Its keep on in waiting status, never process any time?

     

    Could anyone please help me on this?

     

    Thanks,

    Agin



  • 2.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 08, 2017 07:49 PM

    Hi,

    I would assume you have already checked the status of BG and even did a restart of the BG services. If all that has been done, please run the queries in the below tec doc and follow the steps in it

    https://comm.support.ca.com/?legacyid=TEC586757

     

    Let us know how it went.

     

    Thanks,

    Jerin



  • 3.  Re: Some of the clarity jobs are going to waiting status

    Posted Aug 09, 2017 12:28 AM

    Hi Jerin,

     

    Thanks.. But the first query doesn't any row and there are no jobs in lock state. I have restarted the bg and app, however it didnt work.

     

    Thanks,

    Agin



  • 4.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 09, 2017 12:38 AM

    Hi,

    Are these OOTB jobs which are going into Waiting status? If so, which are those jobs? 

    If you are not getting any results for the first query  in the tec doc or if you dont have any cancelled jobs, then check for any queries from the jobs running at the backend. 

    For Oracle, run an AWR report and check it and for SQL Server run sp_who2 'active. 

    If there are any active sessions from the job which are not running in PPM, kill it, restart BG and then try running the job again.

     

    Thanks,

    Jerin



  • 5.  Re: Some of the clarity jobs are going to waiting status

    Posted Aug 09, 2017 11:54 AM

    Hi Jerin,

     

    Update aggregate data job is going to wait state. I have AWR report handy. How i can identify whether any active session from the job is running in DB?

    Is any easy way there?

    Thanks,

    Agin



  • 6.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 09, 2017 08:41 PM

    Hi,

    Update Aggregate job does not have any Incompatible jobs in the OOTB configuration unless you have any custom jobs which are made incompatible. So any ootb jobs shouold not be causing the Update Aggr job to go into a waiting state. 

    This job basically updates the hierarchy tables in the investments so check for queries having tables with hierarchies like inv_hierarchies, inv_flat_hierarchies etc. 

     

    If you are an On Premise customer, the dirty way to get this resolved would be to stop all PPM services and restart the DB services. This will kill all orphaned records in the DB. 

    Let us know how it went. 

     

    Thanks,

    Jerin



  • 7.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 08, 2017 11:13 PM

    Hi 

     

    Did you cancelled any processing Jobs. If yes, better to delete cancelled Job from log and check the waiting Job status. 

     

    the job is waiting status after you deleted the cancelled one, then you need to restart app ad BG services

     

    Thanks

    Senthil



  • 8.  Re: Some of the clarity jobs are going to waiting status

    Posted Aug 09, 2017 12:29 AM

    Thanks Senthil. I have restarted all services, but didn't work.

     

    Thanks,

    Agin



  • 9.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 09, 2017 08:08 AM

    Check which jobs are going in waiting status.

    I assume will not be all and possibly only some.

    Time slicing might go on waiting, but Post timesheets might run correctly.

    If you determine which job is in waiting, but there are some running, it means that CA PPM believes there is an incompatible job to the waiting one which is running, even if you might not see it in the UI.

    Go to Admin > Reports and Jobs

    Find the job that goes on waiting and go the incompatible jobs tab.

    Note which ones are set as incompatible.

    Back to the Home > Jobs menu

    Pause all the jobs from the incompatible list

    Delete all the instances associated to these jobs that are on status of cancelled, completed, waiting ...

    Once the cleanup has been done, resume the jobs previously paused.



  • 10.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 09, 2017 08:30 AM

    Hi Agin,

     

    I believe there is a support case and the problem is isolated to one job and not all jobs. We will work with you via case.


    Regards

    Suman Pramanik



  • 11.  Re: Some of the clarity jobs are going to waiting status

    Posted Aug 10, 2017 01:31 PM

    We had a similar problem not too long ago and it was because another job will killing the DB, and incompatible jobs were starting to back up. 



  • 12.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 09, 2017 08:33 AM

    Hi Agin

     

    Could you please check with DBA is there any long running active session at the database which is related to any of the job?

     

    Thanks

    Senthil



  • 13.  Re: Some of the clarity jobs are going to waiting status

    Broadcom Employee
    Posted Aug 11, 2017 06:34 AM

    Hi,

     

    Check if there is any lock on application side. Please run the below queries may me that help. Also check if there is any timeslice rollover going on. In past we face same issue and found timeslice rollover was happening and it put all job on hold. Once it completed other job will run. 

     

    How to clear process engine locks on timesheets  --------------------------------------------------------------- 
     
    1) First determine if any of the timesheets are in an aborted or errored status.   These timesheets can be unlocked with low risk.      a) Run the following query:    select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk,         prtimesheet.prid, status_code, object_type_code from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes where object_code = 'timesheet' and attribute_code = 'prstatus' and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid    and odf_locked_attributes.odf_pk = bpm_run_objects.object_id and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES' and bpm_run_objects.object_id = odf_locked_attributes.odf_pk  and odf_locked_attributes.object_code = bpm_run_objects.object_type_code and bpm_run_objects.pk_id = bpm_run_processes.id  and status_code in ('BPM_PIS_ABORTED', 'BPM_PIS_ERROR')     b) Run the following to delete all aborted or error based locks.    delete from odf_locked_attributes where odf_locked_attributes.id  in (  select odf_locked_attributes.id from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes where object_code = 'timesheet' and attribute_code = 'prstatus' and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid    and odf_locked_attributes.odf_pk = bpm_run_objects.object_id and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES' and bpm_run_objects.object_id = odf_locked_attributes.odf_pk  and odf_locked_attributes.object_code = bpm_run_objects.object_type_code and bpm_run_objects.pk_id = bpm_run_processes.id  and status_code in ('BPM_PIS_ABORTED', 'BPM_PIS_ERROR') )  
     
    2) If no results are found in step 1a or not enough results are found, continue by running the following query:    select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk,         prtimesheet.prid, status_code, object_type_code from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes where object_code = 'timesheet' and attribute_code = 'prstatus'
    and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid    and odf_locked_attributes.odf_pk = bpm_run_objects.object_id and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES' and bpm_run_objects.object_id = odf_locked_attributes.odf_pk  and odf_locked_attributes.object_code = bpm_run_objects.object_type_code and bpm_run_objects.pk_id = bpm_run_processes.id    Note: This query shows all timesheets that are locked by the process engine.   Some of these timesheets are legitimately locked, but others may be ones that you will want to unlock.       2a) Note down the value of the id field in query 2.  For any id's identified in query 2, you should verify by the resource name and time period id that the timesheet is indeed one you want to unlock.                                        3) Next, you will need to verify the locks for deletion associated with the timesheets from query 2.  You can do this by manually specifying the id's obtained in step 2.  You will take these id's and insert them into the id list in the following query.   select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk,         prtimesheet.prid, status_code, object_type_code from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes where object_code = 'timesheet' and attribute_code = 'prstatus' and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid    and odf_locked_attributes.odf_pk = bpm_run_objects.object_id and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES' and bpm_run_objects.object_id = odf_locked_attributes.odf_pk  and odf_locked_attributes.object_code = bpm_run_objects.object_type_code and bpm_run_objects.pk_id = bpm_run_processes.id  and odf_locked_attributes.id in (nnnnn,mmmmm,ooooo,ppppp)   Note: You will replace nnnnn,mmmmm,ooooo,ppppp, etc. with the id's of the locks gathered in the step 2 query.      eg.    select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk,         prtimesheet.prid, status_code, object_type_code from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes where object_code = 'timesheet' and attribute_code = 'prstatus' and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid    and odf_locked_attributes.odf_pk = bpm_run_objects.object_id and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES' and bpm_run_objects.object_id = odf_locked_attributes.odf_pk 
    and odf_locked_attributes.object_code = bpm_run_objects.object_type_code and bpm_run_objects.pk_id = bpm_run_processes.id  and odf_locked_attributes.id in (5000123,5000124,5000125,5000126,5000127,5000128)  
     
    4) Using the list specified in step 2, you will modify the following query to delete the locks.    delete from odf_locked_attributes where odf_locked_attributes.id  in (nnnnn,mmmmm,ooooo,ppppp)   eg.   delete from odf_locked_attributes where odf_locked_attributes.id  in (5000123,5000124,5000125,5000126,5000127,5000128)  
     
    query 5) (Manually Pick the ones that are incorrectly locked) select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk,         prtimesheet.prid from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources where object_code = 'timesheet' and attribute_code = 'prstatus' and odf_locked_attributes.odf_pk = prtimesheet.prid and prtimesheet.prtimeperiodid = prtimeperiod.prid and srm_resources.id = prtimesheet.prresourceid;       query 6)  delete from odf_locked_attributes where odf_locked_attributes.id  in (nnnnn,mmmmm,ooooo,ppppp)   nnnnn, etc. will come from query 5 above