I have a need to do analysis and trending of ODI execution logs. To this end I want to extract the relevant execution data from the ODI repository and report on it. I have worked out how to link the tables so I can pull out execution data from the SNP tables for a Load Plan down to the mappings contained within it (this is as far as I need to go). However, we have Load Plans that contain packages that call other Load Plans and here I get stuck. I cannot see where there is any data that links the execution of the child Load Plan(s) to the parent.
Example:
LP_PARENT contains a root step that executes a package PKG_START_MORE_LP.
PKG_START_MORE_LP contains two other load plans LP_CHILD1 followed by LP_CHILD2.
Execute LP_PARENT and you get an entry in the log for this load plan instance of LP_PARENT (e.g. SNP_LP_INST.I_LP_INST=5432) which then invokes LP_CHILD1 (e.g. I_LP_INST=5433) and LP_CHILD2 (e.g. I_LP_INST=5434).
How do I uniquely determine that the executions of 5433 and 5434 were started by 5432? I can't find anything in SNP_LP_INST or SNP_LPI_RUN (or anywhere else) that points to a "parent" job.
I've tried looking through the operator log in ODI for clues. The parent and child load plan instance executions are clearly shown in the operator log. Drilling down the operator log in ODI for LP_PARENT takes me as far as the ODI Command to start the child load plans, but there is nothing in that log to show the load plan instance id of the child load plans.
If I look at the execution log for the child load plans they don't tell me how they were invoked (unless I'm missing something).
There must be something linking them or ODI wouldn't be able to update the parent with the relevant execution timings and final status which are dependent on the child load plans.