Orphaned Job Log records are not removed through the job

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

There are scenarios reported where orphaned records found in cmn_sch_job_logs which are observed to lack corresponding parent entries in cmn_sch_job_runs. 

The clarity job 'Remove Job Logs and Report Library Entries' (CMN_JOB_LOGS_DELETE_SP) logic appears insufficient to delete these orphaned entries.

Environment

Clarity 16.3.3, 16.3.2 and 16.3.1

Cause

DE173438

Resolution

DE173438, fixed in Clarity 16.4.0

• The fix for this issue is improvement in the job to look for and remove orphaned Job Log records.
• The orphaned job records are also cleared once as part of the 16.4.0 upgrade script. 

Additional Information

Sample queries can be used to list Orphans:

1. select

   count(*)

   from

   CMN_SCH_JOB_RUNS JR

   where

   JR.JOB_ID not in (

   select

   SJ.ID

   from

   CMN_SCH_JOBS SJ

   where 

   SJ.IS_VISIBLE = 1

   and SJ.STATUS_CODE = 'COMPLETED'

   );

2. SELECT COUNT(*) 

   FROM cmn_sch_job_logs 

   WHERE JOB_RUN_ID NOT IN (

       SELECT id 

       FROM cmn_sch_job_runs

   );