It is a common thing, for a data integration process to have some kind of exception handling, when one of the parts in the flow fail.
The same can be said about ODI, usually when designing a package, in order to handle failing steps people tend to draw a red line from every step in a package to another step, which is supposed to handle the exception and by handle I mean, send an email informing about the failure using OdiSendMail tool. Some even go one step further and after that ODI mail tool they add another step which just throws an exception in order to be able to see those failures in the Operator.

Well, I used to do so and from experience I can tell you, that sending emails informing of failures with the ODI email tool is by far, not the best solution. Maybe it can work for a small flow, which does not consists of many parts, but when working on a big project containing different components and aiming to be able to work with large volumes of data, this will slow you down.

Why?

  1. The code – The last thing you want to do is to touch the code already in production just because you woke up one morning and decided to change the email list. Well, you gonna, unless you had taken some kind of actions to avoid it. There are ways to avoid this, by using a variable for the email list or maybe creating an email group, which could be controlled outside of ODI. OK, we are covered… Basically, we are not. What about changing the SMTP server, there are reasons beyond our control, that could lead to such an action. Maybe the subject because of a typo you have not noticed before? Basically, in order to be able to do those things, you will have to maintain as many variable as does the OdiSendMail accepts, total of 8, currently in 11.1.1.3.1 version. There are at least 2 ways, I can think of right now, how to store the values for those variables, but I will stop here and point out two problems. One, isn’t all this is too much? Just to inform of a failure? Two, no matter how you store the variables, this method still will impact any scenario you have deployed or will deploy, as it is an integrated part of them.
  2. The frustration – When I first started with ODI, I was developing packages like that. To tell the truth, I did not use the send mail tool, but a procedure that would insert the errors into the DB, because we had a little different requirements. Never the less, it was frustrating at the same level, since the concept remained. Each new step, demanded drawing a red line to that exception step. The process was supposed to handle big chunks of data, so basically we went into a loop of developing, testing and changing to meet the requirements, while there were plenty of times that somebody else forgot to draw that red line. To tell the truth, personally, I forgot to do it, every single time :). After doing a few loops like that, the conclusion was obvious.
  3. The looks – When using this method, the more components your flow will have, the less good looking your package will be. Eventually, It will look like a big spider web and make it harder to try and remember what is going on in there. Of course, you can hide the red lines, but after you do that, lets see how many people will remember to show them again or draw it for the new step they have just added into the package. A few weeks ago, I have tried to look into a package we developed like this, that was scary.
  4. Monitoring – From this point of view, I would say, that the scenario is not the part, which should be in charge of failure notifications. The monitoring process should be independent and not integrated into each scenario, more like a “Big Brother”.
  5. Reliability – There are many reasons, why the exception handling step could fail. This means, that the system could be down for a while, before anybody would notice.

Maybe, if I try harder, I would remember one or two more reasons, but lets continue to the alternative. Just with a small select on the repository, it is very easy to get the status of the scenarios that were executed lately. With this method, the monitoring process does not suffer from anything I discussed above. I have written about the repositories in my last article, which can be found here.
Also, I would like to comment, that I am not saying not to use the red line (KO) or the OdiSendMail tool, I think people should use those, but not in this way.

Well, this is becoming a much longer post than I thought. I could go and write about how to do monitoring in ODI, but this sounds like one of my next posts.