Oracle Bulk Load stage

PBalamurugan · Post by **PBalamurugan** » Tue Apr 15, 2008 9:34 pm

Is it possible to use Oracle bulk load stage if ETL svr and DB svr are in different box ?

If it is possible, does it require any set-up ?

Any advice pls.

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Apr 15, 2008 9:54 pm

You can use the stage to produce the ctl and dat files, but you would then need to move these to the Oracle server (perhaps using an FTP script) and trigger the sqlldr command on that machine (perhaps using rsh).

John Smith · Post by **John Smith** » Tue Apr 15, 2008 10:07 pm

Or you could create a remote mounted filesystem on the ETL server that is actually a filesystem on the Oracle server eg. NFS in Solaris. That way you don't have to ftp files/ctl files.

chulett · Post by **chulett** » Tue Apr 15, 2008 10:13 pm

Neither the 'manual' or 'automatic' options in the Bulk stage require the Oracle database to be co-resident with DataStage, in spite of what the docs say. Nor does sqlldr.

Raju_6609 · Post by **Raju_6609** » Thu Apr 17, 2008 3:35 am

Yeah I agree with Craig.

Recently we have tested bulkloader where DB and DS are on different machine. We tested Automatic mode. It worked fine.

Manual Mode:
We are trying execute a afterroutine in manual orabulk stage job to load data.

But we are unable to trigger sqldr command from DSExecute to perform manual load. Anyone has idea what might be wrong ?

Please help.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 17, 2008 4:27 am

No idea at all. How about you tell us precisely HOW you are attempting to invoke sqllder from DSExecute, and what error messages you get?

chulett · Post by **chulett** » Thu Apr 17, 2008 6:30 am

Excatly... without a complete explanation of how exactly you are trying to run sqlldr and what errors you are seeing, we can't help much.