Job is taking too long



IBM's flagship relational database management system

Job is taking too long

Postby abhi.d.leo » Fri May 13, 2011 10:53 am

Hi,

Currently, I am working on a release and changed a DB2 program.
During the Unit testing every thing went fine and Job finished in reasonable time i.e. around 1 hour and 22 minutes.

After the code is promoted to system test, Job is executing very long unexpectedly. We had to cancel after waiting around 48 hours. It took around 29MIN 57.44SEC of CPU time. While in Unit testing it took only 02 Minutes and 01 sec of CPU Time.

Volume of data is not changed much as Tables for unit test has almost 60% of data as System test.
Also, I checked the Job while it was executing. It was getting very less of CPU %. i.e. les than .5% of CPU. Can anyone please help to find out the reason and how?

Thank you! in advance.

~Abhi
abhi.d.leo
 
Posts: 6
Joined: Tue Oct 12, 2010 11:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Job is taking too long

Postby GuyC » Fri May 13, 2011 11:53 am

You would need an explain of the program both in Unit test and System Test.
Hopefully someone at your site knows how to do that.
Then you can compare and find what's different, and hopefully understand why the difference exist.
60% to 100% could easily give a different access path.
Different statistics, filter factors, reorgs not run, missing indexes are just some of the possible causes.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Job is taking too long

Postby mongan » Fri May 13, 2011 12:15 pm

Do you have monitors? It sounds to me like you are accessing a large amount of data, or that you are accessing DB2 with a poor performance situation, like not using indexes and therefore reading the entire DB2 file. Without any specific information we can only guess.
User avatar
mongan
 
Posts: 211
Joined: Tue Jan 11, 2011 8:32 pm
Has thanked: 1 time
Been thanked: 5 times

Re: Job is taking too long

Postby abhi.d.leo » Fri May 13, 2011 2:43 pm

Thank you! for responding.
I dont have much Idea of monitiors. How can I use them?
Yes, you are correct I am using large amount of data and I too suspect poor performance issue.
What specific information you need.

Please respond.

~Abhi
abhi.d.leo
 
Posts: 6
Joined: Tue Oct 12, 2010 11:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Job is taking too long

Postby BillyBoyo » Fri May 13, 2011 4:34 pm

It looks pretty solidly IO bound. Even the extra CPU it has used might just be for servicing the IO. See if you can find the total EXCP's in the output messages. I'd suspect they are huge. Check against proportion of CPU/EXCP for your test jobs against this run. If proportions are similar, you probably have a similar problem, just less visible.

I think Guy is asking for you to run an EXPLAIN DB2 command/function whatever is correct terminology. Go to your DBA, tell him the situation you have and ask them nicely if they could look at the databases to suggest to you anything which might be making them do such IO. I suggest you don't tell them their job as there is no need to annoy them. They should know what they are doing. Whatever new work you have put in is what is causing the problem, so be ready with details of that as it applies to the database.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Job is taking too long

Postby BillyBoyo » Fri May 13, 2011 5:16 pm

And whose bright idea was it to let it run for 48 hours? Not much use for production, is it? Have an estimate for how long your job should run, and chop it pretty close to that estimate (10-20%, say). I've never chopped a job that was going to actually finish reasonably, if it runs too long, it is just not going to be acceptable. Two days is ridiculous, not your fault, whoever is managing that leg of the testing.

You had a clue from the low CPU usage. If you get the OPs to up the priority of the job and the CPU usage doesn't move much, then you know immediately it is IO bound and is going to continue to be. Possible you even have a loop that is doing IO.

A Monitor is something that lets you look, at a greater or lesser amount of detail, at what a job is actually up to while it is running. Like your CPU usage. Ask colleagues/techs what you have that might help.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Job is taking too long

Postby abhi.d.leo » Mon May 16, 2011 3:48 pm

Thank you! Every one for your time and response.

Yes you are right two days is too much and it was executed by testing team in test region and they kept on waiting. Since changed by me I was asked to analyze.

I rexecuted the same job in development region and again it went too quick. As suggested by GUY C suspecting the performance and went to DBA. It was index problem. they have fixed it. Hopefully it should work now.

For Billy:
Yes, this is how I deal with DBA's ..:) . I tell them the problem I am facing with the statistics and let them play. If something is really there they come back with a fix otherwise they tell us to check the changes.

Thanks ! Again.
~Abhi
abhi.d.leo
 
Posts: 6
Joined: Tue Oct 12, 2010 11:16 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post