Posted by : Arjun Lagisetty Monday, December 16, 2013

I have had an interesting problem at work today (This post was long time in draft mode, so this was technically a long time ago, but I would still like to keep maintain this tone). I have been asked to create a report on all the instances of failure of a couple of scenarios for the previous day. For some strange reason automatic load monitoring was not built into the original code. (tch.. tch..). Instead manually drugging though the entire operator listing the scenarios failed, I set out to automate the process. 

There are several ways to monitor ODI loads. Most novices use the technique, illustrated in this diagram from Oracle website to monitor the ODI load. Red cross was added by me for dramatization. I am not a big fan of reinventing the wheel, so I will point you to some good blog-posts out there which describe the dangers of this techniques (Click here. Also in my case, following this technique involved changing hundreds of existing packages (which was a no go).
 



Also, some recommendations like this, alternatives to above process which only works in some situations.

Here is my method of monitoring the load processes. 

 I assume that you at-least have a read only  permissions to work repository tables, which stores the session state. Easy method is to create a Data server in ODI topology and a physical schema for work repository and logical schema, In the designer, create a model for the work rep and reverse engineer only required tables/data stores.

We had a very simple requirements to indicate the admin about the failed scenarios every day at a certain time. Please use your creativity to fit it to your own needs.

I created a procedure which reads the state of all the sessions which ran today, and email the admin if there is an error in any of the scenarios. We generated a scenario based from this procedure and scheduled it in the morning just before the backups..

I used SNP_ tables to get the data and generate a report. Yeah it was  a pain in the neck to guess what each column means. It would be considerate of oracle to release description of each column along with the release.


Query used:
SELECT   COUNT ( * ) FAIL_COUNT-- Number of failed Scenarios
     FROM   SNP_SESS -- Table which contains the status of the scenarios
    WHERE   SCEN_VERSION IS NOT NULL -- Gives us only Scenario execution steps
            AND SESS_STATUS = 'E' -- Gives us the steps which have Error status
            AND TRUNC(SESS_BEG) = TRUNC(SYSDATE) -- All the scenario which ran today.

Alternative

If you are using load plans.. You can define the email in exception step. See more details here. We can define email in case of exception for each load plan step, where each load plan step is the scenario we want to monitor.


{ 2 comments ... read them below or Comment }

  1. You might be interested in looking at my web site. I have several ODI tricks...
    http://www.data-warehouse.ca/ODI_Expertise.html

    ReplyDelete
  2. You might be interested in looking at my web site. I have several ODI tricks...
    http://www.data-warehouse.ca/ODI_Expertise.html

    ReplyDelete

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty