Load Plan Auto-Restartability through JEE Agent Web Services

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.

cap7

– 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:

http://host:port/oraclediagent/OdiInvoke?WSDL

  • 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):

cap8

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”.

cap6

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.

cap9

 

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s