what is different between informatica bulk insert and load

dhwankim · Post by **dhwankim** » Tue May 10, 2005 8:12 pm

Hi Glue.

I wonder what is different between Oracle Enterprise Stage with Write Option(Load) and Informatica Bulk Insert option.

Some Developer said Informatica Bulk Insert Option is not using SQLLDR Utility. but It's functionality is similar to Oracle Enterprise Stage with Write Option(Load). but Problem is informatica is fater than datastage.

So I wonder Why kind of funcitonality is used by informatica. How to speed up the job.

I need your hands.

dhwankim

ray.wurlod · Post by **ray.wurlod** » Tue May 10, 2005 8:18 pm

Hello DaeHwan,

Please check "Glue" in your English-Korean dictionary - it was not quite correct, and was amusing.

I do not think anyone on this forum will have any knowledge about the strategy that Informatica uses, but you might be lucky. I can't believe they would not use SQL*Loader, but they may tweak the control file to take maximum value from its parallel capabilities.

You can do this in DataStage too, but not (yet) automatically. Create your own control file, with all the buffering and parallelism tuned, and use that one rather than the one generated by the stage. If you think there's a business case, submit an enhancement request through your support provider to IBM.

dhwankim · Post by **dhwankim** » Tue May 10, 2005 8:35 pm

Hi Ray

I checked dictionary, so I chance "glue" to "Guru".
Is it right?

Thanks for your comment.

I already came POC with Ascential.

but I did not find good workaground..

If you have any information in detail about Parameter tunning.

Please Let me know it

Thanks

Dhwankim

ray.wurlod wrote:Hello DaeHwan,

Please check "Glue" in your English-Korean dictionary - it was not quite correct, and was amusing.

I do not think anyone on this forum will have any knowledge about the strategy that Informatica uses, but you might be lucky. I can't believe they would not use SQL*Loader, but they may tweak the control file to take maximum value from its parallel capabilities.

You can do this in DataStage too, but not (yet) automatically. Create your own control file, with all the buffering and parallelism tuned, and use that one rather than the one generated by the stage. If you think there's a business case, submit an enhancement request through your support provider to IBM.

ray.wurlod · Post by **ray.wurlod** » Wed May 11, 2005 12:58 am

"Guru" is much better word; your English is still FAR better than my Korean!

I think you will need to ask an Informatica specialist how they get Oracle loaded so quickly, and whether they use SQL*Loader to do it. (If they have created their own software maybe even Oracle would be interested!)

There are various switches you can put into a sqlldr control file. Alas I do not have my Oracle documentation with me, so this is from memory.

Allocate the biggest possible memory.

Enable parallel loading.

Set the buffer size for each column to its correct value. The file created by DataStage uses default buffer sizes (256 bytes) which is wasteful particularly for small data such as integers.

Note that this is nothing to do with DataStage - these are techniques you use when creating a custom sqlldr control file. You would tend to use Sequential File stage to create the data file(s). Perhaps a File Set stage, but then you don't have control over the file names.