Strategies with the Staging Area Different from the Target

• File to Server Append
• Server to Server Append
• Server to File or JMS Append

File to Server Append Process in Oracle Data Integrator (ODI)

1. Identify the Source File Type:
• The source is a single file that can be directly loaded into the target table without complex transformations.
2. Use of Staging Area (Default Case):
• Default behavior in Oracle Data Integrator (ODI):
• The staging area is located on the target server.
• A LKM (Loading Knowledge Module) is used to stage the file into a loading table.
• After staging, an IKM (Integration Knowledge Module) is used to move the data from the loading table to the target table.
3. No Transformation:
• If no transformation is needed on the data, skipping the loading phase is possible.
4. Direct File Load to Target:
• If no transformation is required, use an IKM that directly loads the file data into the target table.
• This requires configuring the staging area on the source file's logical schema.
5. Automatic Use of "Multi-Connection" IKM:
• By setting up the staging area on the source file logical schema, ODI will automatically suggest a Multi-Connection IKM.
• This IKM facilitates data transfer between the remote staging area (source file) and the target.
6. Execution of the IKM:
• The IKM will use a loader utility to manage the data movement.
• The following steps are included in the process:

1.               Generate the load utility script appropriate for the target system.

2.               Run the loader utility to load the data into the target table.

7. Example of IKM:
• A typical example is the IKM File to Teradata (TTU), which directly loads a file into a Teradata database without staging.

This approach is efficient when transforming data isn't necessary and you need to load large files directly into a target system.

Server to Server Append Process in Oracle Data Integrator (ODI)

1. Identify the Staging Area:
• The staging area is located in a different RDBMS (Remote Database Management System) than the target database.
2. Use of IKM for Data Movement:
• An IKM (Integration Knowledge Module) is used to move transformed data from the staging area to the remote target.
• This IKM behaves similarly to a LKM (Loading Knowledge Module), following the same rules for data movement.
3. Steps Involved Using the Agent:
• Step 1: Delete or Truncate Target Table:
• Before inserting new data, the target table may need to be cleared. This can be done by deleting or truncating all records in the target table.
• The deletion or truncation step is controlled by a KM option (specific setting in the Knowledge Module).
• Step 2: Insert Data from Staging Area to Target:
• The data from the staging area is inserted into the target table.
• A SELECT statement is executed on the staging area, defined in the "Command on Source" tab of the IKM.
• An INSERT statement is written using bind variables in the "Command on Target" tab and is executed for each batch of data being moved to the target table.
4. Example of IKM:
• A typical example is the IKM SQL to SQL Append, which facilitates the process of appending data from a staging area to a target database using SQL operations.
5. Variation Using Loaders or Database-Specific Methods:
• In some cases, loaders or database-specific methods can be used to move data from the staging area to the target.
• These methods replace the agent’s role, allowing for more optimized or specialized loading procedures.

By using the Server to Server Append method, ODI can efficiently move data from a remote staging area to a target table, ensuring minimal downtime and high performance during data transfers.

Server to File or JMS Append Process in Oracle Data Integrator (ODI)

1. Identify the Target Datastore:
• The target datastore is either a file (e.g., flat file) or a JMS (Java Message Service) queue/topic.
2. Staging Area Configuration:
• The staging area is located in a different location than the target (file or queue). The staging area typically holds the transformed data before it is moved to the target.
3. Use of "Multi-Connection" IKM:
• A Multi-Connection IKM is used to integrate the transformed data from the staging area into the target (file or JMS queue/topic).
• The IKM used will depend on the target technology (file or JMS). It may involve using the agent or specific features of the target technology (such as a Java API for JMS).
4. Typical Steps in the IKM:
• Step 1: Reset the Target:
• Depending on the options in the Knowledge Module (KM), the target file or queue might be reset before loading data. This can involve clearing the file or queue to ensure no conflicting data is present.
• Step 2: Unload Data from Staging Area:
• The data from the staging area is unloaded (moved) to the target file or JMS queue/topic.
• This involves using an appropriate method (e.g., agent or target-specific features) to perform the actual data transfer.
5. Methods for Data Movement:
• Agent-based method: The agent moves the data from the staging area to the target file or JMS queue/topic.
• Target-specific method: This may involve using technology-specific features like a Java API for integrating with a JMS queue/topic.