This set-up will allow to restart ODI Load Plans running under a JEE agent.
1) Create a package to be used within the Exception Step of the Load Plan.
There are two mandatory steps within the package:
- The first mandatory step is to refresh a variable (I have named it “VN_LP_ID”) which captures the Load Plan ID . This variable will get the numeric Load Plan Instance ID by querying the repository table “SNP_LP_INST” and using the odiRef API method to get the Load Plan Global ID of the parent Load Plan.
SELECT I_LP_INST FROM ODI_REPO.SNP_LP_INST WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'
- The second mandatory step will execute the scenario of a package (I have named it “PKG_LP_RESTART_UTILITY”) in “Asynchronous Mode”.
2) PKG_LP_RESTART_UTILITY package description.
* Please note that the creation of this package is a one-off task. Once created, it can be directly used within the Exception package and it shouldn’t require any additional set-up.
– VN_LP_ID: this global variable has been declared to keep the ID of the Load Plan instance on failure. The value is coming from the “Exception Step” package.
– Sleep for 10 sec.: OdiSleep step to wait 10 seconds before calling the web services to make sure that the Load Plan has been set to “Error” status.
– Invoke Restart Load Plan: OdiInvokeWebService step to call the web services of the JEE agent.
Steps to set-up the OdiInvokeWebService:
- Click on “HTTP Analyzer” option.
- Click on “Select WSDL …” to input the WSDL URL of the agent:
- From the “Operations” option, select “invokeRestartLoadPlan(,)”.
- On the “Request HTTP Headers” side and “part1” section there are 5 parameters that need to be specified (OdiUser, OdiPassword, WorkRepository, LoadPlanInstanceId and LogLevel):
It will raise an error for “LoadPlanInstanceId” but it is actually an ODI bug because this variable will be substituted afterwards by a valid value. It can be ignored.
On the “Content Editor” tab we can see the actual XML code generated for the request.
3) Add the scenario of PKG_LP_RESTART_UTILITY to the Exception Package.
It has to be called in “Asynchronous Mode”.
On the tab “Additional Variables” we define how the variable “VN_LP_ID” refreshed in the first step will be passed to the “PKG_LP_RESTART_UTILITY” scenario.
In order to make things as generic and reusable as possible, I recommend to create the PLP_LP_RESTART_UTILITY package within a project named “UTILITIES” so you can reuse it in any project. The variable “VN_LP_ID” was created as a Global Variable for the same purpose.
- Optional steps:
– The ODI/Oracle error message can be captured and sent by email (for instance).
– Depending on the error message, it can be restarted or not by evaluating the error message against a pattern.
– The number of retry attempts can be controlled by using the odiRef API method to get the current attempt number.
– It can wait for X time before being restarted.