Clarity DB Maintenance Job for Orphaned Records (On Premise)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

You find a large amount of orphaned records and would like to address this in Clarity. I.e. you see many million records in CMN_SEC_ASSGND_OBJ_PERM table that are orphaned.

There is an OOTB hidden job in Clarity that can be used, called DB Maintenance. 

Note: This job is only to be used for troubleshooting purposes under the recommendations of Broadcom Support

Environment

Clarity 16.x

Resolution

Enable and run DB Maintenance job in Clarity:

1. Run the queries: 
   update cmn_sch_job_definitions set is_admin_visible =1 where job_code='NMC_DB_MAINT'
   commit
   update cmn_sch_jobs
   set is_visible =1
   where job_definition_id in (select id from cmn_sch_job_definitions where job_code='NMC_DB_MAINT')
   commit 
   update cmn_option_values set value='10000000' where option_id = (
           select
             id
           from
             cmn_options
           where
             option_code = 'NMC_DB_MAINT_INST_RIGHTS_CAP'
         );
   commit
2. Run the command:
   admin toggle-feature NMC_DB_MAINTENANCE_JOB 1
3. Restart services
4.  Go to Home – Reports and Jobs – Scheduled Jobs
5. Filter for Scheduled instance of DB Maintenance – set the job to Paused for now if Scheduled
6. Go to Available Jobs and run the Job DB Maintenance
7. Wait until it completes it may take a few hours (we set to 10M records to be removed)
8. Run the job a few times until all the orphans are deleted
9. Once done check the export of either the below tables. You can provide it to Broadcom Support if requested, in Excel format or text if it’s too long:
   • First table is on newer releases, second is on older releases
   • SELECT * FROM ORPHAN_DEL_RECORDS_LOG;
   • SELECT * FROM DEL_ORPHAN_ASSGND_OBJ_PERM_LOG
10. You can leave the job scheduled if instructed by Broadcom Support