DWH job is stuck - Error: Couldn't execute SQL: DO $$

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse (DWH) job is stuck in processing. We see the following error in the dwh logs:

Execute SQL script - org.pentaho.di.core.exception.KettleStepException: 
Error while running this step!

Couldn't execute SQL: DO $$
DECLARE
    V_SQL_TEXT text;
    V_EXCEPTION varchar(4000);
    V_SQLERRM varchar(4000);
    V_SQLSTATE varchar(5);
BEGIN
      update DWH_TME_ENTRY set TASK_NAME = (  select TASK_NAME  from DWH_INV_TASK lkp inner join ( select TIMEENTRY_KEY as dim_pk, TASK_KEY as dim_key, TASK_NAME as dim_string, 'en' as language_code  from DWH_TME_ENTRY dim ) iv  on (iv.dim_key = lkp.TASK_KEY )  where DWH_TME_ENTRY.TIMEENTRY_KEY= iv.dim_pk and lkp.dw_updated_date >= to_timestamp('2021-06-06 01:03:11.000000000', 'yyyy/mm/dd HH24:MI:SS') )  where exists (  select TASK_NAME  from DWH_INV_TASK lkp inner join ( select TIMEENTRY_KEY as dim_pk, TASK_KEY as dim_key, TASK_NAME as dim_string, 'en' as language_code  from DWH_TME_ENTRY dim ) iv  on (iv.dim_key = lkp.TASK_KEY )  where DWH_TME_ENTRY.TIMEENTRY_KEY= iv.dim_pk and lkp.dw_updated_date >= to_timestamp('2021-06-06 01:03:11.000000000', 'yy

You may also see the following error: FATAL: terminating connection due to administrator command

Cause

This may be due to a DB session being hung

Resolution

1. Cancel and delete the Load DWH Job. 
2. Run a new instance of the Load DWH job.
3. If that job instance completes successfully, you can then reschedule the job that was previously deleted.
4. If the above doesn't help, please contact Broadcom support 

Important: Prior to canceling the job, if it's a scheduled instance of the job, note the reoccurrence set up for the job so the job can be rescheduled with the same frequency.

Additional Information

See also:

• DWH Load Fails - Errors: "Get Record Counts - Unexpected error" / "FATAL: terminating connection..."
• Load DWH job frequently reported issues