Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Update job taking too long - COMMIT happening slowly.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Mon Jan 19, 2009 9:03 pm    Post subject: DB2 Update job taking too long - COMMIT happening slowly.
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Mon Jan 19, 2009 9:35 pm    Post subject:
Reply with quote

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

Quote:

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


I find that to be a useless observation, as stated.
slow? HTFDYK?
how do you know? wall clock - uuussseeelllleeeesss measurement.
that only measures your anxiety!

did the run time increase immediately from 1.5 to 12, or has this been a progression over a period of time?

what does the explain tell you?
do you reorg and run-stats regularly?

are your updates modifiying indexes or primary key values?

are the updates single, mass?

imbedded/store proc/?? what kind of process?
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Mon Jan 19, 2009 9:49 pm    Post subject:
Reply with quote

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.
Quote:

are the updates single, mass?

How can I check this ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 20, 2009 1:56 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Jan 20, 2009 1:00 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
JayC

New User


Joined: 27 Sep 2008
Posts: 70

PostPosted: Tue Jan 20, 2009 4:20 pm    Post subject: Issue resolved.
Reply with quote

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 !
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 20, 2009 8:34 pm    Post subject: Reply to: DB2 Update job taking too long - COMMIT happening
Reply with quote

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

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us