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:
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.
CLRT-73338: Resolved in CA PPM 14.1