6.7 Bulk Insert to Oracle

[eyetry]

I've been working populating Oracle tables using ODBC. I copied the command line from 6.5.2 and changed -dbtype odbc to -dbtype oracle. It actually looks like it takes slightly longer to load the tables now.

Someone suggested updating the tables using OCI. How would I change the command line to us the OCI

------

-DBTYPE ORACLE -SOURCE database -TABLE Table_Name -USER user_id -PW password

------

Looks like the adapter guide suggests

-CONNECT instead of -SOURCE

eyetry

 

[BocaBurger]

Best way is to have the target of the map be Oracle, and have the tree properly defined using Oracle in DID.

For Bulk loads Ascential DataStage is a better tool unless you need complex trandformations.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[janhes]

You could use Oracle loader from a batch file. This is much faster.

 

[eyetry]

BocaBurger, "..Bulk loads Ascential DataStage is a better tool..." DataStage isn't an option. Hopefully though Ascential will introduce the superior DataStage performance to TX. Until then......

janhes, I mentioned that to the Architects a while back. I'm sure that if I press the issue I could get them to go that route. But, for now they'd rather I look at what types of improvements I can make with DataStage TX performance. I have reduced load time by half but it's still 13 minutes for a large file. To write the same data to flat files takes a few seconds. I'd be much less concerned if the difference wasn't so substantial.

Also, how well it handles this load will probably have an impact on any DB related activity we use TX for in the future.

eyetry

 

[BocaBurger]

Have you tried burst mode? StreamMaxMemLimit? Tuning the map's page size and count? If you use the native Oracle adapter, it is as fast as any other tool that I am aware of, except Parallel stage. The next version of DS TX and DS will have wire level ODBC connectivity included.

This may be faster than some adapters. We are limited by the 3rd party API code.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[janhes]

Interestingly, the next release of DS TX has an example map to produce a file to be passed to Oracle loader. The map then runs a batch file to run loader.