ODI(Oracle Data Integrator) Load Plan Issue
ODI (Oracle Data Integrator) is an ELT tool, and it's a very powerful tool to load the data into a Data warehouse. “But great power comes with great responsibility”, yes spiderman!!. where ODI sometimes lack in responsibility.
I was working on Load PLan and when we were trying to execute the same, It was going into Waiting Mode rather than executing and loading the data.
In order to make it work, have one and manual option which is, recreate the whole new Load Plan, but if the load plan is too complex and has so many steps inside, it's almost very time-consuming and error-prone. So what all option we have?.. yes ODI work repository tables.
Load Plan metadata can de find in the ODI work repository table which is mainly SNP_LOAD_PLAN and SNP_LP_STEP
SNP_LOAD_PLAN stores all LP ID and SNP_LP_STEP stores all the steps information in it. Once you get the LP ID from the SNP_LOAD_PLAN table for which LP is going into waiting mode, query the SNP_LP_STEP table and find out if any child step has a null value in the PAR_I_LP_STEP column, except the root step. So this issue due to when we have a null value in PAR_I_LP_STEP column except for root steps. While executing the LP ODI fails to find the parent of that child steps , and it goes to waiting mode.
Once you have identified the row which has no value in PAR_I_LP_STEP, all it's required to resolve the issue, just delete the entry from the table
delete TAD_ODI_WORK.SNP_LP_STEP where i_load_plan=839 AND LP_STEP_NAME<>’root_step’ AND PAR_I_LP_STEP IS NULL
Hurray !!! now ODI got its power back and when you run the LP next time, it will execute without any error!!!
