DSXchange

Hi,

I am using datastage 7.5 server jobs. The stage used to get data from oracle system is ODBC and data pull is very slow there is no network issue something in datastage is slowing it down. Is there any parameter setting like increasing any buffer size that can help.

Thanks,
Vishal Gupta

Run the same SQL outside of DataStage but on the DataStage Server itself manually via the command line, such as by using the Oracle sqlplus utility, and compare the times.

Check if the SQL is doing any joins or using any WHERE clause that may be inefficient or causing full table scans due to lack of index, and have a DBA review it as well.

In the stage look for the array size property and do a search on this site on "array size" to review all the existing topics about tuning it.

All good advice from Eric. Just want to clarify there is no magic parameter but proper setting of the Array Size may help - IF the 'pull' is actually the culprit.

Try this to help with his first bit of advice: add a constraint to your output link of @FALSE so that nothing gets written to the target and see how long / fast / quickly the job runs. If you still end up having issues we'd need to know ALL of the stages in the jobs so that the proper 'bottleneck' can be identified.

Thanks! for advice
The fetch query is very simple select * from a stg table and we have tested he query is super fast at source also in data stage there is no transformation dumping into tmp table. I will check the array size and try to play around with that.

vishu19aug wrote:there is no transformation

I'm guessing that means there is no transformer in the job. If so, for the record:

A Server job always has at least one transformer in it - if you don't have one on the canvas, what is sometimes called an 'invisible' one will be added behind the scenes at runtime. If you don't have any luck with the array size, just for grins put one on the canvas, map everything through and then try the little trick I posted.