Validation

• ODI Version 12.2.1.4.200721.1540
• Oracle DB System 19.7.0.0

Introduction

ODI processes are typically at the mercy of the entire IT infrastructure’s health. If source or target systems become unavailable, if network incidents occur, ODI processes will be impacted. When this is the case, it is important to make sure that ODI can notify the proper individuals. This post will review the techniques that are available in ODI to guarantee that such notifications occur reliably.

Exceptions Handling and Notifications in ODI

When you create an ODI package, one of the ODI Tools available to you for notifications is OdiSendMail:

Figure 1: OdiSendMail

Specify the SMTP server, to and from parameters, subject and message for your email and you can alert operators, administrators or developers when errors occur in your packages.

One challenge though is that if you want an email to be sent for any possible error in your package, each and every step of the package must be linked to this tool in case of error. This can get to be quite overwhelming, and there is no guarantee that one step will not be forgotten by a developer along the way.

Figure 2: Sending an email for every step that ends-up in error

Another caveat with sending a notification email from the package itself is that the final status of the package will be successful as long as sending the email is successful. If you want the package to fail, you now have to raise an exception after sending the email to force the package to end in an error state.

If we look back at the original objective, all we really want is to send a notification no matter what error occurs. So ideally we want the package to actually fail. Only after it has failed should we send the notification email. This is exactly what we can perform with the Load Plan Exceptions.

Load Plans and Exceptions

If we handle the exceptions at the load plan level there is no need to send the notification emails from the original package. What must do now is to create two separate packages: one to process the data, another one to handle the exceptions.

Figure 3: package without notification emails

Figure 4: package dedicated to notification emails

There are two main aspects to a load plan:

• Steps: a set of scenarios that must be orchestrated, whether executions are serialized of parallelized

• Exceptions: a series of scenarios that can be used in case one of the steps fails to execute properly.

Figure 5: Load plans steps

For instance in Figure 6 below, we have created an exception called Email Notification. This exception will run a dedicated scenario whose sole purpose is to send a notification email. The scenario here is generated from the package represented earlier in figure 4.

Figure 6: Load Plans Exception

When you edit the properties of the steps of the load plans, you can choose whether they will trigger the execution of an exception scenario or not. For each step, including the root step, you can define what the behavior will be in case of error:

• Choose the exception scenario to run (or leave blank)

• Run the exception scenario (if one is selected) and stop the execution of the load plan at this point

• Run the exception scenario (if one is selected) and proceed with the rest of the load plan.

Figure 7: selection of an exception scenario

If you select an exception at the root level, this will be the default exception for the entire load plan unless you decide to overwrite this default with a different selection in the properties of individual branches or step of the load plan.

This will guarantee that no matter what fails in the scenarios used in the load plan, the notification email is always sent. This includes corner cases where the scenarios would not even start, for instance in case of errors in Topology definitions such as missing physical schema definitions.

In figure 8 below we can see steps of the load plan where the scenario that was executed fails, raising an exception:

Figure 8: scenario step raising an exception

We can also look at the scenario sessions to see the original scenario failure and the execution of the notification scenario:

Figure 9: scenario sessions for failed scenario and notification scenario.

Conclusion

Leveraging exceptions handling in Load Plans is a very straightforward and efficient way to ensure that no matter what fails in your ODI processes, notifications are sent reliably and consistently.

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-Team Chronicles for ODI”.