ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH Access Rights Job fails on table: DWH_SEC_USER_GROUP

book

Article ID: 218252

calendar_today

Updated On:

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

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. 

Environment

Release : 15.9.2

Component : Clarity  SAAS DATA WAREHOUSE

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

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.