How To Load Data Directly from the Source to the Target Without Creating Any Temporary Table in an ODI 12c Mapping or ODI 11g Integration Interface (Doc ID 762723.1)

Before providing the answer to above question, it is useful to understand why and when ODI is 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 12c Mapping or 11g 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 two 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 Mapping / 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 a 12c Mapping or 11g 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