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 NOVEMBER 08, 2018
Applies to:Oracle Data Integrator - Version 4.1.01.01 to 220.127.116.11.99 [Release 4.1.01 to 11gR1]
Information in this document applies to any platform.
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:
- Only one source Datastore, not journalized
- Loading strategy = Truncate/Insert or basic Insert (no updates or deletes)
- 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?
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|C$ Collect tables|
|I$ Integration tables|
|E$ Error tables|
|Solution 1: Use the existing IKM SQL to SQL Append|
|Solution 2: Write your own IKM, using database load utilities (Oracle SQL*Loader, Microsoft SQLServer BCP...)|