Temporary tables naming issue

ODI 12c works in a different way compared to ODI 11g in terms of naming convention for temporary tables (C$_, I$_, etc). We need to take extra care when running processes in parallel, mainly in Load Plan parallel steps and even with ODI projects running in parallel.

 

The generated name of the C$ temporary table is usually based on the alias of the object directly connected to the target table.

For instance, if we have a filter before the target table, ODI will run the following statement:

“create table ODI_TEMP.C$_0FILTER …”

This will create a non-unique table name and it can be easily messed up by any other ODI process running in parallel.

 

There are different approaches to work around the issue, I suggest one of these two:

  1. To rename the aliases of the objects directly connected to the target tables to a unique and meaningful name.
  2. To use the “Use Unique Temporary Object Names” and “Remove Temporary Objects on Error” properties of the “Physical” tab from mappings:cap

 

The name of the temporary table won’t be meaningful (alphanumeric string randomly generated) but it will be 100% unique.

 

 

For more information on this topic, please refer to:

https://www.rittmanmead.com/blog/2016/05/ditips-odi12c-table-names/ https://blogs.oracle.com/dataintegration/entry/odi_12c_parallel_target_table

Advertisements

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 )

w

Connecting to %s