ODI Loading Using Unload/Load

Loading Using Unload/Load

When the source result set is on a remote database server, an alternate solution to using the agent to transfer the data is to unload it to a file and then load that file into the staging area.
This is usually the most efficient method when dealing with large volumes across heterogeneous technologies. For example, you can unload data from a Microsoft SQL Server source using bcp and load this data into an Oracle staging area using SQL*Loader.
The steps of LKMs that follow this strategy are often as follows:

1. Drop and create the loading table in the staging area
   
2. Unload the data from the source to a temporary flat file using either a source database unload utility (such as Microsoft SQL Server bcp or DB2 unload) or the built-in OdiSqlUnload tool.
   
3. Generate the script required by the loading utility to load the temporary file to the loading table.
   
4. Execute the appropriate operating system command to start the load and check its return code.
   
5. Possibly analyze any log files produced by the utility for error handling.
   
6. Drop the loading table once the integration KM has terminated, and drop the temporary file.
   

When using an unload/load strategy, data needs to be staged twice: once in the temporary file and a second time in the loading table, resulting in extra disk space usage and potential efficiency issues. A more efficient alternative would be to use pipelines between the "unload" and the "load" utility. Unfortunately, not all the operating systems support file-based pipelines (FIFOs).