DEV EPM

Hi all! In today’s post we will talk about how you may create automatic processes to stop ODI sessions. But first let’s think why/when we should automate this kind of task.

Everybody knows that the basic way to stop an ODI session would be to go to ODI Operator, right click in a running Session and select “Stop Normal”/”Stop Immediate”. Obviously it works just fine, but it requires someone to log in, select the jobs and stop them. There will be cases that you will want to stop those scenarios without any human intervention.

So let’s imagine that you have a critical ODI job that must make sure that some other secondary ODI jobs are not running before it actually starts. Maybe you could add an OdiSleep object in the critical ODI job, wait a little bit, check if the secondary jobs are still running, sleep again and so on. It is safe approach, but sometimes this critical ODI job is also top priority and it could have permission to stop all other secondary ODI jobs before it actually starts.

Or maybe you could have an execution window that must be respected and all ODI jobs that crosses a specific range of time should be stopped no matter what. I could write some other examples, but you already got the idea. So, how do we accomplish that in ODI?

If we take a look on ODI Toolbox panel, we are going to find things like OdiStartScen and OdiStartLoadPlan, but nothing related to stop, cancel or kill a session.

I’m not sure why Oracle didn’t put this kind of objects in the Toolbox, but if we go to the ODI agent bin folder (oracledi\agent\bin) we are going to see some interesting .bat (.sh on linux) jobs there:

• Restartloadplan.bat
• Restartsession.bat
• Startloadplan.bat
• Startscen.bat
• Stoploadplan.bat
• Stopsession.bat

We may right click/edit each of those to get more information about them. Today we are interested in the last one “Stopsession.bat”. Its syntax is pretty simple:

stopsession <session_number> “-AGENT_URL=<agent_url>” [“-STOP_LEVEL=<normal(default)|immediate>”]

Pretty cool and easy to use. We may just add this call to an ODI procedure and create some logic to stop all sessions that we want. Let’s build an example and see how it would look like. Imagine that you have an ODI_JOB_A that needs to stop ODI_JOB_B and all its children (if there is any of those running) before it continues its tasks. To accomplish that, we would need to create an ODI procedure and use the command on source/target technique to select ODI_JOB_B and its children that are currently running. It would look like this:

On “Command on Source” we would write a SQL against the ODI metadata repository checking for all ODI_JOB_B sessions (and its children) that are currently running. Of course that this SQL is just an example, you may tweak it to fit your own requirements. Here we are just querying the SESS_NO that belongs to a running session of ODI_JOB_B and UNION that to all running children of a running ODI_JOB_B session.

On “Command on Target” it should be just a matter to add the stopsession cmd on “Operating System” technology, but it is not that easy. Let’s analyze the stopsession cmd again:

stopsession <session_number> “-AGENT_URL=<agent_url>” [“-STOP_LEVEL=<normal(default)|immediate>”]

session_number is a value that will return from our “Command on Source” tab, so we are good.

stop_level may be set as normal or immediate, so we are also good here.

The problem that we have is the AGENT_URL. A valid AGENT_URL would look like this:
-AGENT_URL=http://ODISERVER:9001/oraclediagent
This URL is composed with the information that is set in our Topology information, like the one below:

The problem here is that we don’t have any ODI substitution API that return this kind of information. The closest that we have is <%=odiRef.getSession(“AGENT_NAME”)%> that just returns its name, nothing more. To get around this situation, we will need to query ODI metadata repository again and compose this URL using a SQL against SNP_AGENT table. Let’s create one ODI variable for that like the one below:

Here we using the AGENT_NAME API function to get the right information for the running agent. Now we are able to finish our procedure with the “Command on Target” command:

And that’s it! Just add the AGENT_URL refresh variable and this procedure in the very beginning of ODI_JOB_A package and you will have it stop session ODI_JOB_B and its children before it moves on.

Hope you liked it! See you soon!

This entry was posted on September 4, 2015 at 3:54 pm and is filed under Hacking, ODI, ODI 11g, ODI Architecture with tags Architecture, EPM, ODI, ODI Operator. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.