Upsert LOB values to Oracle table using Oracle Connector

[dsuser7]

Hi,

I'm trying to read from one oracle table and write to another oracle table.
The source and target tables both contain LOB fields.

The Oracle connector initially gave error that the array size should be made 1 for it to be able to process the LOB values.

There are around 30million records to be processed and setting the array size is making the job run for days.

Please suggest on how to improve performance.

Thanks

[chulett]

Probably through something other than DataStage - for example with PL/SQL, especially if these two tables are in the same instance.

[dsuser7]

Thanks Craig for the quick reply.

These 2 tables are not in the same database. Can't this be handled in Datastage?


-Thanks

[chulett]

Sure and you are... it's just that it is not a very performant option to pull data from the source across the network into DataStage and then push it back out to the target, one record at a time. Perhaps your DBA could suggest an alternate path - dump the contents, move the output file to the other server and then import via sqlldr?

[prasannakumarkk]

Logically, what is the maximum length of LOB values?

[dsuser7]

There are 2 tables - one has CLOB and the other has BLOB datatypes. As Datastage data type for LOB are LongVarChar or LongVarBinary respectively. I haven't defined a size to these fields in the job.

Are there any alternate stage or any properties using which I can improve the upsert performance.

[chulett]

No, there isn't.

And adding in the fact that you are source a CLOB and targeting a BLOB (or even if vice versa) reinforces my advice to use another tool for this. At least it reinforces it in my mind.

[dsuser7]

I'm sorry, I'm misunderstood. There are 2 separate jobs (with 2 separate source and target tables) -

1. source has CLOB which is mapped to target CLOB
2. source has BLOB which is mapped to target BLOB.

[chulett]

Ah... ok. Still doesn't change my answer. Don't let that stop others from chiming in however!