DSXchange

I have a job with DB2 connector writing the output to a sequential file.

First when I run the job it took almost 7 hours to complete. I have fine tuned the SQL and then re-run the job and it completed in less than 5 minutes. When the same job is run after two days, it is taking almost an hour to complete.

Can anyone please explain is there any cache where the data is stored so the run time is less when the job is triggered immediately?

There are far too many variables to list here. Just wanted to note that this is a DB2 / database SQL issue and has nothing to do with DataStage. Yes, a database will typically cache query information so that if it is rerun in close order it could possibly go much faster the second time. However, that won't help you with day over day runs.

Work with your DBA to get an explain plan of your query and see what suggestions they have to improve the performance. Tuning is an iterative process.