IBM Mainframe Forum

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

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.

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.

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

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.

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.

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