How To Load Data Directly From The Source To The Target Without Creating Any Temporary Table in an ODI Integration Interface

(Doc ID 762723.1)

Last updated on SEPTEMBER 29, 2017

Applies to:

Oracle Data Integrator - Version 4.1.01.01 to 11.1.1.9.99 [Release 4.1.01 to 11gR1]
Information in this document applies to any platform.

Goal

How to force Oracle Data Integrator (ODI) to load data into a target table / datastore without creating C$, I$, E$ temporary objects?

Before providing the answer to above question, it is useful to understand why and when ODI 10g and ODI 11g are creating temporary tables. The descriptions below apply to standard cases.

C$ Collect tables

A C$_ table is created by an LKM in order to transfer data from a source server to the staging area - which, by default, is on the target server.

This will allow ODI to join data sets coming from heterogeneous source data servers. For example, if as sources you are using an Excel spreadsheet and an Oracle table to load a Teradata database, ODI will retrieve the content of the Oracle table and the Excel file into 2 collect tables, both hosted on the staging area. This will allow ODI to join the data coming from Excel and Oracle.

If the Integration Interface contains several source Datastores which are joined on the Source, the C$_ table will contain the result of the join operation. If the join is set to be executed on the staging area, ODI will create 2 distinct C$_ tables.

Notice that when the source Datastores are on the same server as the target Datastore or same server as the staging area, there is no LKM to set at the Integration Interface level.

I$ Integration tables

Most of the time, the I$_ tables are created by the IKMs whenever there is a need for updating a target table or running a flow control.

If this is not required, the creation of the I$_ table is of no use since ODI can retrieve the data directly from the collect tables. The structure of the I$_ table is to have an almost identical structure as the target table in order to contain the data that ODI is about to insert or to use to update the target table.

E$ Error tables

From an Integration Interface, E$_ tables are created and populated by the CKM if the IKM option called Flow Control is set to yes.

So, only under the following conditions:

    1. Only one source Datastore, not journalized
    2. Loading strategy = Truncate/Insert or basic Insert (no updates or deletes)
    3. No flow nor static control is required

In a situation where the C$, I$, E$ tables are of no use... or the user lacks privileges on the staging area... it would be beneficial to directly load data into the target table, without creating any of above mentioned temporary objects. 

How to achieve this goal?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms