Load DWH Access Rights Job fails on table: DWH_SEC_USER_GROUP

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) Access Rights fails with the below error on the bg-dwh logs  

ERROR 2021-06-25 11:30:04,987 [Dispatch 25JUN - Load Data Warehouse Access Rights : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below: 
2021/06/25 11:29:32 - audit table name -  & DWH_CFG_AUDIT
2021/06/25 11:29:32 - audit table name -  & DWH_CFG_AUDIT
2021/06/25 11:29:34 - audit table name -  & DWH_CFG_AUDIT
2021/06/25 11:30:04 - no Records to Process? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2021/06/25 11:30:04 - no Records to Process? - Couldn't execute SQL: CALL DWH_CFG_TRUNCATE_TABLE_SP('DWH_SEC_USER_GROUP'::character varying);
2021/06/25 11:30:04 - no Records to Process? - 
2021/06/25 11:30:04 - no Records to Process? - ERROR: Unable to get a lock on DWH_SEC_USER_GROUP for truncation.  Aborting the DWH load.
2021/06/25 11:30:04 - no Records to Process? -   Where: PL/pgSQL function dwh_cfg_truncate_table_sp(character varying) line 47 at RAISE
2021/06/25 11:29:32 - audit table name -  & DWH_CFG_AUDIT
2021/06/25 11:29:32 - audit table name -  & DWH_CFG_AUDIT

Environment

Release : 15.9.2

Component : Clarity  SAAS DATA WAREHOUSE

Cause

Based on the stack, the Load Data warehouse job is executing a stored procedure DWH_CFG_TRUNCATE_TABLE_SP which is trying to truncate table DWH_SEC_USER_GROUP.  At the same time, there is a lock held on the table by another user causing the job to fail. 

Resolution

• Work with your database administrator to check if the locks can be explicitly removed 
• Additionally, educate your users trying to read the data in batches via any BI tools while using SaaS Odata connection (or even direct database read using any other tools)

Additional Information

• Query to check if lock on PostgreSQL database 
• Query to check lock on Oracle Database 
• Query to check lock on MS SQL Server Database.

Impact: If the Load data warehouse rights job continues to fail, then it will continue to copy the security data into DWH tables and it will be bloated and will have incorrect data as the job in process does truncate to clean up the data. So, if you were doing reporting using ODATA you might see your reports are timing out.