Sequences and Flow Control in Oracle Data Integrator

Most developers that start working with Oracle Data Integrator (ODI) and create their first mappings will stumble upon an error message like this pretty early in their ODI career:

ODI Sequences Error Message

In this blog post, I will first recreate this error with ODI and then show what you can do about it. For understanding this post, you should have some basic ODI knowledge (what mappings are, that they have a logical and a physical design, and that you can use different knowledge modules in the latter).

The Problem

In my example, I get files with revenue data from cinemas and load them into an Oracle database table. To make sure that I don’t get in any trouble when a cinema shows the same film more at the same time more than once (maybe everybody wants to see the new Star Wars film), I add an aggregator between source datastore (the file) and target datastore (the database table):

ODI Sequences Logical Design

For the target datastore, I chose the knowledge module IKM Oracle Incremental Update (MERGE) that uses an intermediate table (what ODI calls I$ table) and allows for Flow Control (validating the loaded data). Apart from that, I didn’t do any other changes in datastore properties or physical design.

When I run this mapping as it is, I can see in the Operator tab of ODI Studio that it fails:

ODI Sequences Execution with Error

When I open the task where the error happened, I see the aforementioned error message:

ODI Sequences Error Message

When I look at the target code, I find the reason for this error:

ODI Sequences Error Cause

The mapping selects the next sequence value for the primary key column of my target datastore in the same SELECT statement where I group by the source attributes. Unfortunately for me, Oracle database doesn’t accept that.

How can I deal with this problem? There is a relatively obvious and a not-so-obvious way. First, let’s talk about the relatively obvious one.

The Relatively Obvious Solution

I open the mapping again and click on the primary key attribute of the target datastore (the one where you select the next sequence value). As Execute on Hint, I select “Target”.

ODI Sequences Execute on Target

When I run the mapping again, I don’t get the error anymore but I still get a warning:

ODI Sequences Execution with Warning

When I look at the session details, I see the reason for this warning: The mapping completed its run but two erroneous records were found during flow control (in task 260 when looking for potential not null constraint violations, to be precise):

ODI Sequences Warning Message

The mapping has put these records into an error table (ODI calls them E$ table) that I can query using ODI Studio or the database development tool of my choice:

ODI Sequences Error Records

That way, I can understand what happened: I didn’t insert any values for the primary key column into my I$ table but I still checked if this column was null (which it was since I didn’t insert any values). This means I have to go back to the datastore properties.

For the primary key attribute of the target datastore, I have to un-check the box that says Check Not Null (Flow Control only):

ODI Sequences Check Not Null

When I run the mapping again after this change, it finishes without any errors or warnings.

ODI Sequences Execution Successful

That was the first, relatively obvious way of dealing with our ODI sequence error. The second, not-so-obvious way is changing the knowledge module I use in the mapping.

The Not-So-Obvious Solution

In the Global Objects section of ODI Studio’s Designer tab, I duplicate the knowledge module IKM Oracle Incremental Update (MERGE) and give it a new name like IKM Oracle Incremental Update (Sequence).

ODI Sequences IKM Duplicate

In the new knowledge module, I have to change two tasks: the one called Insert flow into I$ table where data is written into the intermediate table and the one called Merge Rows where I update the target datastore.

In the Insert flow into I$ table task, I have to make sure that I don’t insert any values for the primary key column (the one that is populated by the sequence). I achieve this by adding “and !PK” both to the column lists of the I$ table itself and the SELECT statement that is used to fill it:

ODI Sequences IKM Change Integration Table

The meaning of selectors like “TRG” and “PK” can be found in the ODI Substitution API Reference that is a valuable resource when working with knowledge modules.

In the Merge Rows task of my new knowledge module, I make a similar change so that the mapping won’t load the values for the primary key column from the intermediate table anymore:

ODI Sequences IKM Change Target Table

Sometimes it’s good to remember your Boolean logic: the negation of “and !PK” is, of course, “or PK”.

What have I achieved with these knowledge module modifications? Thanks to them, I won’t have to worry about the correct Execute on Hint settings anymore:

ODI Sequences No Execute on Hint

I just have to make sure that in the physical design of my mapping, the new knowledge module is set for the target datastore:

ODI Sequences New IKM

If I always fill my primary key columns with a sequence, I can even set this knowledge module as default so that all future mappings will use it:

ODI Sequences IKM Default for this Pair

When I run the mapping with the new knowlege module, it finishes without any errors or warnings:

ODI Sequences Execution Successful with New IKM

When I look into the generated code for the Insert flow into I$ table task, I see the difference the knowledge module changes made:

ODI Sequences Target Code with New IKM

As expected, the I$ table ignores the primary key column and everything works fine. That was the second, not-so-obvious way of dealing with our ODI sequence error.

Lessons Learned

Is there a lesson to learn from this? I think there are at least two:

  1. Almost always, there are multiple ways to solve an ODI problem.
  2. If the way you found doesn’t include fiddling around with knowledge modules, maybe it should.


Dieser Beitrag wurde unter Analytics & BigData abgelegt und mit , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s