DSXchange

Hi all,

I have a problem with one job like it take too much time to run for some collection of data,and runs normally for others

Actually it is a c sql script which is called by a datastage job.

Could you explain the possibilities.

There are several possibilities, here are a few:

1) It is taking a reasonable amount of time for the processing it is doing on the platform it is running on. Your expectations are too great.

2) The sql is poorly written.

3) The database is not well tuned or indexed.

4) You try running the sql directly and it takes just as long - the poor performance has nothing to do with DataStage.

5) There is something about the data that takes longer to process that causes. Perhaps it is a larger quantity of data or matches to more records that are already in the database.

I hope this illustrates that it is near impossible to say what is wrong without more information, and that if you try no. 4) you may find it has nothing to do with DataStage.

Yeah, but its always easier to blame data stage and the datastage developer! Right??

There are clients that expect job sequencers containing 10 jobs to finish in 2 seconds, if not they blame the design, as well as the developer and at worst datastage.

One more reason why query can be slow is the network connection between the database server and data stage server.

Can you do a tns ping or ping from data stage server to the database server? Any locks set on the table which is being queried? Any recursive joins in where conditions in SQl??

How long does the query take on db server to return a result set? either to a file or to stdout?



Thanks
Ramesh

rameshrr3 wrote: There are clients that expect job sequencers containing 10 jobs to finish in 2 seconds, if not they blame the design, as well as the developer and at worst datastage.

Hate such clients which expect rocket science from an ETL tool.
They are better off writing thousands of lines of sqls and spending thousands in maintenance there-off.