2 Replies Latest reply: May 8, 2014 11:27 AM by BWall RSS

    How to stop a schedule after X exceptions

    BWall

      Using ODI 12c on Windows Server 2008 R2 Standard SP1

       

      I have a scheduled load plan configured to run continuously (Repetition = Many times, Interval between Repetitions = 5 Seconds). I would like to configure this schedule so that, if any of the steps in the load plan raises an exception, the load plan session will automatically be restarted (not start a new execution of the load plan, but restart the error'ed session). But if the load plan fails 5 times consecutively (for any reason), the schedule should become disabled and require manual intervention.

       

      Is this possible?

       

      NOTE: In the schedule, I tried setting Number of Attempts on Failure = 5, but this doesn't work. What is this setting supposed to do?

        • 1. Re: How to stop a schedule after X exceptions
          mRainey

          Hi Brian,

           

          I think you would need to incorporate the execution of the Load Plan into a loop within a Package. Your loop could use variables to track how many times it has executed, or failed, during a process. To restart the load plan, it looks like you can still use the restartloadplan.sh command from the command line "http://docs.oracle.com/middleware/1212/odi/ODIDG/running_executions.htm#BABIJCGJ" - though I haven't tested it in 12c. A great resource for setting up a loop in a Package for CDC is the "Oracle Data Integrator 11g Cookbook", Chapter 8: Package Loops and File Processing.

           

          A couple of things you'll need to handle -

          • The ODIStartLoadPlan tool does not wait for the load plan to complete before moving on to the Package step. You'll need to setup some sort of tracking of the load plan session to determine if it is still active or not. (That's my 11g experience, may have changed in 12c).
          • The documentation mentions the need for the standalone agent in order to use the restartloadplan.sh command line tool. It's not clear if the standalone agent is a requirement, but it does seem like it would be. Your other option, then, would be to restart from a web service: http://docs.oracle.com/middleware/1212/odi/ODIDG/running_executions.htm#BABICCIJ.

           

          Hope this helps!

           

          Regards,

          Michael Rainey

          • 2. Re: How to stop a schedule after X exceptions
            BWall

            Thank you, Michael! This is good information. I'm surprised the ODI scheduler doesn't support this out of the box . I thought we we were just missing a setting somewhere.

             

            I see how your solution would work, but I think this is a bigger effort than we're ready to take on right now. Since we're not using SCD Type 2's, re-running the entire load plan shouldn't cause any data integrity issues. And if it keeps running and errors repeatedly, we'll just get a plethora of e-mail notifications! Until that really proves to be a problem, we're going to hold on to this info and try it later.

             

            Take care,

            Brian