CLOB issues

asaf_arbely · Post by **asaf_arbely** » Thu Jun 23, 2011 4:04 am

Hi
I need to move 2.5 million records per day from one oracle DB to another oracle DB.
The source and target table are the same
The tables have 3 CLOB fields all of them must move to the targent
After reading the forum and IBM docs I defined a parallel job with 2 oracle connectors. The only way I can make the job to run is by keeping only one CLOB column
But still the job runs in an outrageous rate (20 rps)
So I have 2 questions:
1. How can I load more than one CLOB in the same process?
2. How can I increase the processing rate?

Thanks

chulett · Post by **chulett** » Thu Jun 23, 2011 7:04 am

Bottom line is CLOBs are not supported. What happens when there is more than one CLOB in your job? Have you try setting your Array Size to 1? I'd suggest looking into other tools... pl/sql perhaps? Have a chat with your DBA and see what ideas they have.

chulett · Post by **chulett** » Sun Jun 26, 2011 7:17 am

As far as I know, DataStage doesn't do anything magical with CLOBs so not really sure what "moves in a different section" might mean. Regardless, you've been asked for a solution using a tool that doesn't support CLOBs. Have you checked with your official support provider, passed along that error, seen if they have any suggestions? I think you are out of luck but you never know...

chulett · Post by **chulett** » Sun Jun 26, 2011 7:22 am

You might want to google for "ORA-03127", looks like it is an Oracle client / sqlnet issue where *it* rather than DataSTage may be doing something multi-threaded (which may line up with your "different section" thought) and failing in that attempt. From what I saw, upgrading your Oracle client may help.