Load Dataware house Job fails if the Column TABLE_NAME is null for table DWH_CMN_ERROR_MESSAGE

book

Article ID: 207342

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Load Dataware house Job fails if the Column TABLE_NAME is null for table DWH_CMN_ERROR_MESSAGE

SUMMARY: Load Dataware house Job fails if the Column TABLE_NAME is null for table DWH_CMN_ERROR_MESSAGE

  1. Run the Load Dataware house house and ensure its successful
  2. In Order to reproduce that Load DWH fails run the below update (Note: The column name TABLE_NAME  can get null due to some other factors too but that is yet to be found) 

 UPDATE DWH_CMN_ERROR_MESSAGE 
 SET TABLE_NAME = NULL 

    3. Re Run the Load Dataware house Job and it will fail with below error 

 [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO HISTORY TABLES. SQLERRM : ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME")
 ORA-06512: at "PPM_DWH.DWH_CFG_POP_HISTORY_SP", line 81
 ORA-06512: at line 2

Expected Results: The Load Dataware house Job should be successful as column TABLE_NAME can accept NULL 

Actual Results: The Load Dataware house Job fails with below error 

 [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO HISTORY TABLES. SQLERRM : ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME")
  ORA-06512: at "PPM_DWH.DWH_CFG_POP_HISTORY_SP", line 81
  ORA-06512: at line 2

 

Cause

This is a logged as defect DE59585 and currently in review with our engineering team. 

Environment

Release : 15.9

Component : CA PPM DATA WAREHOUSE

Resolution

Workaround 

For On Premise Customer 

  • Pause any scheduled jobs and ensure any running jobs to be completed 
  • Stop the BG services 
  • Back up the table DWH_CMN_ERROR_MESSAGE
  • Delete any record from table DWH_CMN_ERROR_MESSAGE  where TABLE_NAME IS NULL ( Below is the sample from one of problematic environment) 
TABLE_NAME ERROR_MESSAGE ERROR_NUMBER SQL_COMMAND
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015092, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:20:12','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015096, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 05:09:12','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015094, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:37:31','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
  ORA-01403: no data found 100  
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015093, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:27:51','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015095, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:56:02','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)

For SaaS Customer - Please raise a support case and request to perform the below steps.