Clarity: Unable to add attributes on the audit

Products

• Clarity PPM SaaS

Issue/Introduction

STEPS TO REPRODUCE

1. Login to Clarity and Navigate to Object
2. Under the object search for application Object 
3. Navigate to Audit Trail under object (example Project or application)
4. Add attribute New Attribute 1 to Audited Attributes from Available 
5. Add the same New Attribute 1 to Audited Attributes for insert and delete operation
6. Click on Save

Expected Results: The newly added attribute New Attribute 1 should be saved 
Actual Results: The newly added attribute New Attribute 1  is never saved and page keeps on spinning. 

Cause

During analysis of this problem it was found that Audit maintenance was not done and Audit tables had lot of records, however with these records the record should get inserted and then we saw a lookup which was a static lookup and had 25k+ values 

    select * from cmn_audits where object_code = 'application'; - 34129 Rows
    select * from CMN_AUDITS; - 2801222 Rows
    select * from CMN_CAPTIONS_NLS; -6862873 Rows
    select * from ODF_AUD_LOOKUP_CAPTIONS - 3121202 Rows
    select count(*) audit_col_count from user_tab_columns - 32384 Rows

 

The problem is whenever there is audit creation we go to each lookup associated with the object and then go to each lookup value and execute a query on CMN_CAPTIONS_NLS table. Since the lookup has 25K+ values the query is executed 25k+ times, hence in order to fix this the query execution was optimized and its tracked as defect DE50027 

Environment

Release : 15.x

Component : CA PPM SAAS STUDIO

Resolution

The defect DE50027 fixed in Clarity 15.8 and as a best practice please purge the Audit data at regular intervals by setting the Number of Days you need the audit at every object level and then running the Purge Audit Trail Job.