Job running for a longer time all of a sudden

We have daily batch jobs running on Informatica server. It fetches the data from Db2 AIX server. All of a sudden the jobs which took 2 hrs runs for 6 Hrs and a job running for 4 hrs runs endlessly. It gets stuck at the point where it needs to create a lookup of a file. This is happening only from the start of this week. Please let me know what would be the cause of such issues. DB2 support team says it is not a db2 issue. It would be only the query tuning issue since it reads 11 billion records per execution. However, the same jobs with no change was running only for 2 and 4 hrs respectively earlier. If anyone faced the same issue or if you know the possible issues please let me know..Thanks in advance.

Popular White Paper On This Topic

This could be due to environment where job is running, just be watchful
during the job run time..like who others are using the same table, or is
there any job conflict means some other application or infa session using
same table..also take help from Infa admin guy when job runs.

We checked that no other application or job is using the same table. Job is getting stuck at a lookup cache creation. Could you tell any possible reasons for the same. Its been 20 hrs and I could spot the cache file growing in size in Cache folder. The size of the table in the last week and this week is same still.

Make sure that LKP cache space is there in the Infa server and so that LKP
cache happens.
If the Session log says that LKP cache is causing the issue, request the
Infa Admins to have some spaces for them.

Admin confirmed it there are enough space for LKP cache files. Would increasing the lkp cach inx n help?? Session log shows only information like Creating the look up cache using defaiult sql. That's it,after that its running for almost 20 hrs and no other info or error. When i saw the cache directory, the cache files are increasing in size still. Does that mean still the records are fetched from lookup table and save din Cache files, And it seems like the size of the file did not change significantly than b4..still it happens.

You can compare the session log of session when it completed within 2 hr and current session log. Compare all the values. If you found that only difference is while lkp chache creation then it is an issue with database. There must be some new job creating deadlock with your session. You can monitor your database or ask DBA to monitor the database activity on this particular table.

One more thing you can try, try to run the SQL query used in LKP directly on database and check how much time it take to complete.

Thanks. so far there are no indexes build on this table. Only the Row_id seems to be the primary key... Anyway I have asked the DB to do the runstat as of now. Do any one know the same job in UAT takes lesser time than in production provided the size is nearly equal.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.