DB2 Update job taking too long - COMMIT happening slowly.

JayC · New User Joined: 27 Sep 2008 Posts: 70

Hi,

I have a production job that usually run for not more than 1.5 hrs. There seems to be something wrong now - since the same job which is a DB2 update job is running for almost 12 hrs after which I had to cancel.
After cancelling I looked at that no. of COMMITs that had happened over the last 12 hours and it was compared to the normal run and found to be very slow.
What could be the reasons for a Db2 update job to run slower ? Rate of commit to decrease ? The input volumn has not increased dramatically- this was checked.
What other reasons could be there ? What else can I check ?
Kindly help.

Thank you.

dbzTHEdinosauer · Posted: Mon Jan 19, 2009 9:35 pm

input volume may not have increased, how about the size of your table?

JayC · New User Joined: 27 Sep 2008 Posts: 70

The commit frequency is 1 luw. This hasn't changed.
No, the run time did not increase immediately.
REORG and RUNSTATS are done as a scheduled activity on a routine 'weekly' basis and this is a weekly run job.
I used the time to measure the rate of the job - since on a AVERAGE the job runs for 1.5 hrs with just about the same about of data.
I can't see any other yard-stick for measurement at the moment.

dick scherrer · Posted: Tue Jan 20, 2009 1:56 am

Hello,

Either some code, the data, or the environment has changed.

It is possible that additional jobs have been scheduled to run concurrently with this job causing the process to run slower (database contention of some kind).

It is possible that the data content has changed while the overall volume has not.

It is possible (and more likely) that something has been changed in one or more sql statement in the code and the new sql does not perform acceptably (or the code has been changed so that an innocent query is now executed millions of times instead of a few).

If the problem persists thru the week, suggest you run a full-volume test over the weekend at the least-busy time on the system. If it still runs the full 12+ hours, run a full-volume test of the version of the code prior to the latest sql change(s).

"Rate of commit" is the symptom not the problem. There is no such tunable parameter. There is also no "Clock-time" tuning. Most often the code or sql must be tuned. Sometimes the database needs to be changed (as in adding one or more indexes).

Once the code/database has been improved/corrected, the time between commits and the run-time can improve. Keep in mind that there are some terrible designs that still give correct results.

dbzTHEdinosauer · Posted: Tue Jan 20, 2009 1:00 pm

a COMMIT requires aloooooooooooooooooot of resources.

your commit frequency is 1 what?
an LUW is a local unit of work - whether 1 row is affected or 10,000.
so, what does your LUW involve?

JayC · New User Joined: 27 Sep 2008 Posts: 70

Hi Dick...

Thank you for the info.

The issue has been resolved. The problem was not with the data or with any of the SQL codes/DB2 program but with the environment.

It appears that there was something worng with REORG jobs that ran last time. The re-creation of indexes after the REORG didn't happen. This is the reason for the long runtime of my update job as pointed out by our DA.

The Indexes were recreated and the job ran fast & fine.
Thanks !

dick scherrer · Posted: Tue Jan 20, 2009 8:34 pm

Good to hear it has been resolved - thank you for letting us know what caused the problem

d