ODI Alert Mechanism

Today, I have read an article about recommendation about not using red lines (ko) in ODI packages. I just want to explain how I manage alert mechanism in ODI.
This is a sample package that I use in extraction phase.

Sample Package Alert

Generally we all want to use parallel execution (Asynchronous Mode) for extractions. I_EXTR_1, I_EXTR_2 and I_EXTR_3 are all in Asynchronous Mode. ODIWaitForChildSession has the property of 1 for Max. Number of Failed Child Sessions. If any of my scenarios fail, ODIWaitForChildSession will continue on ko (red line).

V_ERROR_DESC is a variable refresh based on a SQL Statement which prepares ODISendMail’s body section. You can find or create your own query based on Repository or using Groovy.

OdiSendMail is tool completely runs on variables.

  • Mail Server : #V_MAIL_SERVER_IP (refreshes from a parameter table at the beginning of ETL) is a variable. When IP address of mail server is changed (nothing in Information Technology is static) admin only need to change the IP address in a parameter table.
  • From : This is static, since we have a LDAP user authenticated in active directory. Nearly impossible for us to change.
  • To : V_ERROR_MAIL_TO (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
  • CC : V_ERROR_MAIL_CC (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
  • BCC : V_ERROR_MAIL_BCC (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
  • Subject : ODI Error – Session : <%=odiRef.getSession(“SESS_NAME”)%>; Agent : <%=odiRef.getSession(“AGENT_NAME”)%>. This option should be set thinking of people can implement in their rules or alerts.
  • Attachment : For alert purposes I generally leave this option empty, but for information or DQ purposes I write here a “fix file name + variable name” (create_script1.txt => 1 is coming from variable) to attach a file to the mail. I will write this usage to another post in detail.
  • Message Body : #V_ERROR_DESC variable that is refreshed before executing OdiSendMail.

Final step is a variable named RAISE_ERROR, which contains a dump raise error statement to refresh to make you see a red exclamation in Operator to see the error. This is a workaround since OdiSendMail sends mail successfully, you cannot see red ERROR exclamation in Operator. To see it, just put something (that will fire an error) here to receive an error at the end of package.

10 thoughts on “ODI Alert Mechanism

  1. Hi Gurcan,
    Very good article and in my opinion, the setup you have described is done very professionally.
    Just a quick comment, about what my article http://bahchis.com/2011/03/21/do-not-draw-that-red-line/ is about.
    I do not encourage people not to use the KO steps. I do believe that they are there to serve a purpose. I just think, that this purpose is not being used as a monitoring solution. In my opinion, it might be fine to send some notifications in case of an error, but there are just too many factors outside of the process control. For example, network errors, Repository DB down or even an error on the exchange server.

    I think, that for effective monitoring, no system or process should be monitoring itself. We should use reliable tools that are made just for that.

    Thank you,
    I enjoy reading your articles.

    • I think I misunderstood your post then. What I want to talk about was using ko’s to inform people about ETL related errors. Source column size maybe enlarged and large data (than target column) is already inserted in source, this will return an ETL error or similar other matters. For your point of view you may write some Groovy code whether agent/repository db is up or not and install agent and repository different machine.
      But to me, availability keeping up of database, operating systems, agents are not our business. These should be handled by infrastructure or application admins and there are variety of tools for them to use. When CPU is over 95% admin should receive a message and it is not ODI’s job to send this message. You’re right.! But for small companies with a small budget, this should be considered. I would probably say don’t use ODI, it is expensive. 🙂

      Thanks for reading and I’m glad that you are enjoying. I started reading your articles as I find some spare time.
      Hope to see you somewhere, sometime and have some chat about ODI. 😉

  2. Sure thing, maybe one day. 🙂
    I see your point and mostly agree.
    For each it’s own I guess, but I still believe that the alerts should be more centralized and less integrated into each and every process.


  3. hi,

    the work around for marking the entire process as failure. I have a requirement like that wherein after failure i need to log the status of the session and cause of failure onto database foe error detection and resolving purpose. However, for restarting i need to mark the entire session as failure as odi allows only those processes to be restarted which are failed. Can you please elaborate.

  4. […] 6.Pack these stuff up shown below (click to enlarge picture). All the scenarios starting with “I_ARC” are in “Asynchronous Mode”. If any of the scenarios fail, exception is generated. You may take a look throwing exceptions in ODI in my other post named “ODI Alert Mechanism“. […]

  5. I am using yellow interface as source in other interface to enable it as table, “USE TEMPORARY AS DERIVED TABLE(Sub-select)” is grayed out…. my source and target are in different data servers is this the problem? if how can i achieve this..
    Please help

    • Hi Sandeep,
      You can only use temporary interfaces (yellow interfaces) if your sources are in the same database. Otherwise it cannot run a subquery within a query.
      To me, better to move the records (in a I$ table) and use temporary interface, but this depends on the situation.
      If both your sources are Oracle, then you can customise your KM and go for DBLink option.


  6. if the 3 mapping in the package are serial and i want to send the email when the second mapping only fails.

    should i make the KO arrow out from it or out from the last mapping ?

    • It will only work (as you mentioned) that KO arrow out from mapping #2.

      In the meantime, you can connect many KO arrows to same SendMail command.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.