DSXchange

Hello,

I am using ODBC stage to extract the Data from Oracle and Job is RCP enabled. No Metadata defined..

The source table is having approx.30 columns and hacing 3.5 milliion records.

My Job is running very slow extraction 300 records per second.

Is there any environment variable which I can use which can help in making the extraction process faster??

The design is as below

ODBC --->> Transformer---->DataStage
(No Where clause)


Regards
Neeraj

Why ODBC? Why not the Oracle Connector stage?

Due to some restrictions, we are not able to update the TNSNAmes.ora and using ODBC stage.

What is your target? Saying "DataStage" there doesn't really mean anything. To verify that the extraction is the bottleneck, add a contraint out of the transformer of @FALSE so nothing passes to the target and then see what the speed is.

Your organization needs to remove those "constraints" and get that file updated.

Contact your Oracle DBAs. They typically handle updating that file. Unix Admins can do it in a pinch, since they are root.

See which ID owns the file, contact the owner.

No way should a shop neuter their ETL environment by forcing ODBC because they don't want to update a text file.

Since you indicated that you are not able to update the file, I assume the file is there (otherwise you would have said so), so your Oracle client is present at least.

A generic ODBC extract with RCP will be slow.
You could parallelize the ODBC connection and fake out a partition read using modulus on an numeric (keyed) column... but that means you can't use a generic RCP job since you need the column name.


slap someone harder to get them to allow you to use the native Oracle tools.

Are you using the ODBC Connector stage or the old ODBC stage?

Have you thought about gathering an angry mob to get your tnsnames.ora file updated?

Is your stage running sequentially or in parallel? If in parallel, on how many nodes?