BulkLoad OCI through Mercator 3

[syammerc]

Hi,

We are using Mercator6.7 version and Iam trying to improve the performance in Loading data into oracle(9i) database.
To load large volume of data mercator is taking more time compared to DataStage.
I tried with so many scenarios like workspace and pagesize and pagecount but I didnot get better performance compared to DataStage.

Can anyone help me in this regard how to make settings in the command line of the outputcard to have BULKLOAD OCI into oralce.

Thankyou in advance

 

[BocaBurger]

DataStage is better at bulk loading than DS TX. DS TX is a realtime environment, where you are not trying to process millions of rows. If there was a complex transformation needed, then TX could be faster, but you need to use the right tool for the job.

To cut up a tree a chainsaw would be great, but your wouldn't want a brain surgeon to use one.



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

 

[janhes]

There's an example with version 7.5 to run a bulk load using a batch file that contains the command

sqlldr dev/dev@oracle9sup_nt control=.\ora9bulk.ctl log=.\ora92_bulk.log bad=.\ora92_bulk.bad

ora9bulk.ctl contains

LOAD DATA
INFILE '.\bulkcopy.dat'
REPLACE
INTO TABLE bulkcopy
(
state     CHAR TERMINATED BY ","  ENCLOSED BY'"',
name     CHAR TERMINATED BY ","  ENCLOSED BY'"',
type     CHAR TERMINATED BY ","  ENCLOSED BY'"',
county     CHAR TERMINATED BY ","  ENCLOSED BY'"',
st_code     INTEGER EXTERNAL TERMINATED BY ","  ENCLOSED BY'"',
county_code     INTEGER EXTERNAL TERMINATED BY ","  ENCLOSED BY'"',
platitude_dms     CHAR TERMINATED BY ","  ENCLOSED BY'"',
plongitude_dms     CHAR TERMINATED BY ","  ENCLOSED BY'"',
platitude_dec     CHAR TERMINATED BY ","  ENCLOSED BY'"',
plongitude_dec     CHAR TERMINATED BY ","  ENCLOSED BY'"',
slatitude_dms     CHAR TERMINATED BY ","  ENCLOSED BY'"',
slongitude_dms     CHAR TERMINATED BY ","  ENCLOSED BY'"',
slatitude_dec     CHAR TERMINATED BY ","  ENCLOSED BY'"',
slongitude_dec     CHAR TERMINATED BY ","  ENCLOSED BY'"',
elevation     CHAR TERMINATED BY ","  ENCLOSED BY'"',
population     CHAR TERMINATED BY ","  ENCLOSED BY'"',
cell_name     CHAR TERMINATED BY ","  ENCLOSED BY'"'
)

 

[eyetry]

If the bulk load doesn't work the way janhes displayed it above make sure sqlldr is installed on the machine executing the map. I am running sqlldr daily in 6.7.*. Pretty fast.

sqlldr will save up to 1/3rd of your load time on larger files depending on you machine.

If you can't use sqlldr, using the Oracle ODBC is a bit faster than the MS ODBC for Oracle.

Neither Mercator nor Ascential were able to solve to long load times for large file in TX (although the standard Ascential line was buy Datastage it not a reasonable or practical expectation. And, based on the cost of the product and how fast it does most other tasks this should have been a slam dunk.)

 

[syammerc]

Hi Janhes/eyetry,

Thankyou for your support.

I am facing a problem while executing the shell script from Mercator using EXIT function.

I given the command in my map as below:
EXIT("<path of my script>< inputparam to script>" ,"","")
but map is not executing this script on my UNIX box.

The same script is running fine when I tried through command line.

Please let me know if Iam missing any thing.

Thanks in advance.

Regards
Syam.

 

[BocaBurger]

If you are calling a shell script, try using the shell script adapter.


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

 

[eyetry]

Wish I help on hte Unix side but..... I am no more than a putz