Anonymize Personally Identifiable Information (PII) Job fails if a custom attribute is added for auditing with PostgreSQL as Database

Products

• Clarity PPM SaaS

Issue/Introduction

SUMMARY: Anonymize Personally Identifiable Information (PII) Job fails if a custom attribute is added for auditing with PostgreSQL as Database 

 

STEPS TO REPRODUCE: 

1. Login to Clarity with administration rights
2. Navigate to Object --> Resource
3. Create a custom string attribute called Test
4. Enable the newly created attribute "TEST" for Audit
5. Add the newly created attribute Test to Resource Edit/list page and fill some values for one of resource
6. Now go to the resource and check ANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII) attribute and deactivate the resource by un checking the Active attribute
7. Run the Job ANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII) with ANONYMIZE ONLY INACTIVE RESOURCES WITH ANONYMIZE PII parameter SELECTED

Expected Results: The job AANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII)  should anonymize the data and complete successfully 

Actual Results: The job ANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII) anonymize most of the data but fails while executing the below statement 

 

UPDATE odf_ca_resource SET obj_anonymize_pii = 0, obj_last_anonymized = LOCALTIMESTAMP WHERE id = ' || v_resource_key; 

And in the bg-dwh log you will see the below error and also in table dwh_cmn_error_message in DWH Schema

ERROR 2020-12-29 07:06:39,804 [Thread-17018] dwh.event Reload Facts? - An error occurred executing this job entry : 
Couldn't execute SQL: CALL DWH_PII_ANONYMIZE_SP (0,0,'1','0');

ERROR: ENCOUNTERED EXCEPTION WHILE UPDATING ODF_CA_RESOURCE. relation "dual" does not exist
  Where: PL/pgSQL function dwh_pii_anonymize_sp(bigint,bigint,bigint,bigint) line 97 at RAISE

Environment

Release : 15.9

Component : CA PPM SAAS STUDIO

Cause

Due to DE59228

 

Resolution

Fix: DE59228 is fixed in 15.9.1

Workaround:

1. Disable audit for the custom attribute under resource object.
2. After the job ANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII) fails you need to delete the content from table dwh_cmn_error_message in DWH Schema and you can request Broadcom Support to run truncate dwh_cmn_error_message (This table Stores error messages that occurred during the data warehouse load and ANONYMIZE PERSONALLY IDENTIFIABLE INFORMATION (PII)) and it is safe to be truncated.

Note: If you don't truncate the table dwh_cmn_error_message  in dwh schema subsequent run of Load DWH Job will also fail for both Full Load and Incremental Load

Additional Information

There are two procedure called COP_PII_ANONYMIZE_SP & DWH_PII_ANONYMIZE_SP  and this works fine till there is no attribute enabled for auditing, once you enable the audit for a  custom attribute under resource object a trigger trigger_fct_t_resource_oodf_ca_resource gets created and it tries to execute the below statement 

INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID,  ATTRIBUTE_CODE,  TABLE_NAME,  COLUMN_NAME, VALUE_BEFORE , 
       VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID) 
       SELECT nextval('cmn_audits_s1'), 'resource', V_OBJECT_ID, 'test', 'odf_ca_resource', 'test',OLD.test, 
        NEW.test, OLD.test, NEW.test, 'U', NEW.LAST_UPDATED_DATE, NEW.LAST_UPDATED_BY, NEW.LAST_UPDATED_DATE, NEW.LAST_UPDATED_BY, V_TRANSACTION_ID FROM DUAL;

Note: The insert statement depends on the attribute selected for audit. The insert statement fails and the below error is printed on job logs 

ERROR: ENCOUNTERED EXCEPTION WHILE UPDATING ODF_CA_RESOURCE. relation "dual" does not exist
  Where: PL/pgSQL function dwh_pii_anonymize_sp(bigint,bigint,bigint,bigint) line 97 at RAISE

 

Attached the bg-dwh.log for correlation to this defect