ODI 11g - Loading More Files Faster

This post shows you how to leverage more of your Oracle system, you can load more data and faster using these kind of best practices. The LKM here, lets you leverage the Oracle database's preprocessor to load MORE data faster. The KM also has the capability to leverage a regular expression to identify the file or files so that you can load much much more, it also sorts the files based on size to comply with the most efficient way of loading into Oracle via the external table. The preprocessor can basically do anything, but is commonly used to uncompress data on the fly - so you can eliminate transferring large uncompressed files around or needlessly uncompressing them! There are various creative blog posts around showing you what some customers have done - some even include the transfer in the preprocessing phase.

I posted the LKM on the java.net site here

You can see the LKM configured below, I have used the preprocessor and a regular expression to load all gzipped files in the directory; 

When the code is generated below you can see the PREPROCESSOR directive used, plus you can see that the file names are going to be generated from a Java API call where the regular expression and directory are passed.

My gunzipdb.bat script has the following definition, wrappering the gzip utility (note the use of the -d -c directives);

  • @echo off
  • c:\ext_tab_test\gzip -d -c %1

Check this creative use of the PREPROCESSOR directive here (External table to load compressed data residing on remote locations) which does some creative work in the preprocessing script. This could do pretty much anything you want including transforming from some complex file to project through an external table.

Comments:

Hi David,

Thanks for sharing this excellent KM.
Unfortunately the download link requires authentication. java.net login doesn't work.

Could you share it elsewhere please ?

Thanks,
JeromeFr

Posted by JeromeFr on December 20, 2012 at 11:33 PM PST #

Hi Jerome

You can create a java.net account and get access, there is an ODI project with other collateral at java.net/projects/oracledi

I will also post the IKM and link from the connects when I get access today.

Cheers
David

Posted by David on December 21, 2012 at 07:27 AM PST #

Hi Jerome

I have uploaded below, but it's best to get from java.net/projects/oracledi since it will/may be updated there;

https://blogs.oracle.com/dataintegration/resource/odi_11g/KM_LKM_Files_to_Oracle__EXTERNAL_TABLE_.xml

Cheers
David

Posted by David on December 21, 2012 at 09:22 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today