A running instance of Datamart Extraction Job fails on restart of the Background (BG) services. After restart all the new instances start to fail as well.
We found out that the issue was with the Stored Procedure 'NBI_RF_SP'. This Stored Procedure fetches information about the Index 'NBI_R_FACTS_U1' on the Table 'NBI_R_FACTS', drops the index and then recreates the same. During this process if BG is restarted and the Index creation fails, the later instances of the Datamart Job start failing due to the NO_DATA_FOUND exception in the SELECT query. (because the index does not exist any longer). For a workaround we recreated the Index separately before executing the job again, and the issue was resolved temporarily. The Stored procedure should check if the index exists before attempting to execute the 'drop' action.
Steps to Reproduce:
- On customer-specific dataset, execute the 'Datamart Extraction' job (it will be known to take some amount of time to process)
- During the processing of this job, the Background Services may stop suddenly at a point when it is executing the 'NBI_RF_SP' procedure
- The 'NBI_R_FACTS_UI' index will not be recreated due to the interruption in service
- Once the BG service is back up, execute the 'Datamart Extraction' job again
- It will fail due to the missing 'NBI_R_FACTS_UI' index - check BG log file for full error message
Expected Result: Job should not fail due to missing index because the procedure is going to drop it and recreate it anyway.
Actual Result: Job fails due to missing index
Manually create the index prior to executing the 'Datamart Extraction' job after the BG services are up.
- Get the Table Space Name
WHERE table_name = 'NBI_R_FACTS'
AND index_name='NBI_R_FACTS_U1' ;
- Recreate the Index
CMN_CREATE_INDEX_SP('NBI_R_FACTS','NBI_R_FACTS_U1','UNIQUE','FACT_DATE, RESOURCE_ID',<tablespace_name>) ;
CLRT-73338: Resolved in CA PPM 14.1