Parallel job hangs

ke.rstin_ · Post by **ke.rstin_** » Mon Feb 25, 2013 2:58 am

Hi everybody,
there's something curiously going on in my datastage jobs:

I have some (data intensive) SCD1-jobs with a lot of look ups. Most of the time they finish, but very often I have the last log entry for 6 o'clock when I check it at 9 o'clock and it should run approx. 1 hour. When I stop and restart the job, it works without any problems.

This problem does not only concern one job, on Monday it's job 1 which doesn't finish, on Friday it's job 2 which doesn't finish,...

Do you have any ideas?

Best regards,
Kerstin

prasson_ibm · Post by **prasson_ibm** » Mon Feb 25, 2013 3:36 am

Hi,
What is your source,target and lookup stages?

ke.rstin_ · Post by **ke.rstin_** » Mon Feb 25, 2013 3:50 am

Hey,
they are all Oracle Connector Stages (database behind is Oracle 11g)

chulett · Post by **chulett** » Mon Feb 25, 2013 8:22 am

Any time I see symptoms like that - a random issue appears that does not repeat when the job is rerun on its own - it says "resource issue" to me. How many jobs are running each night? Knowing nothing else about your situation, first suggestion would be to scale back how many run at the same time.

ke.rstin_ · Post by **ke.rstin_** » Mon Feb 25, 2013 8:49 am

Hi,
at the same time there's mostly running only this one job, but it can happen that it overlaps with two or three other jobs which end two or five minutes after the start of the problem-job.

Today it even doesn't finish when I start it manually without having done any changes in the job.

chulett · Post by **chulett** » Mon Feb 25, 2013 9:15 am

OK. When it "hangs" have you ever just let it go to see how long it takes? Have you involved your DBA and/or SysAdmin to monitor your resources while the job runs?

ke.rstin_ · Post by **ke.rstin_** » Mon Feb 25, 2013 10:10 am

It started on 23 February at 05:31 and the last log entry was on 23 February at 06:18. I stopped it on 25 February in the morning.

No, I've not talked to the DB admin or SYS admin so far but I'll try to reach them to test it and keep you informed.

priyadarshikunal · Post by **priyadarshikunal** » Fri Mar 01, 2013 1:43 pm

In cases where it works sometimes it may be resource issue so talk to your database admin and system admin to see if there is any such issue they see in Trace file/ASH Report. SGA/PGA value can also play a part in such scenario.

I had similar issues with Oracle Connector even if there is 1K to 2K records and used to finish in 1 second using oracle enterprise stage. But in my case it stopped working completely for few jobs. I did raise a PMR with IBM and instead of fixing the issue, they asked us to use Oracle enterprise stage as Oracle connector is new and may create problems sometimes. This happened on 8.5, I believe they don't want to release a patch since 8.7 was already in market. Since we were planing to move to 8.7, we did not pursue it any further.

ke.rstin_ · Post by **ke.rstin_** » Tue Mar 26, 2013 4:58 am

We made an additional limitation for the data to work with and the error has never come again. So I agree with you, that it is a space limitation/performance problem. Unfortunately I have no access or answer from a db admin, which would have been very interestering and helpful for the upcoming projects. Nevertheless thanks a lot for your help!