job is running too long



IBM's flagship relational database management system

job is running too long

Postby rahuldba » Tue Oct 11, 2011 12:50 am

There is a job which is running too long.Normally the job takes 2-3 minutes to run but today it took 9 hours to complete.This is an update job.can anyone let me know how from monitoring tool or omegamon can one tell whether its a CPU/IO or some other prob.It is a simple update program with index created on columns.On doing explain on the job shows procms =20 and procsu as 10 service units.

I am using db2 v9.ZOS MAINFRAMES
The job details-

TOTAL ELASPDED TIME=9.55 HOURS
CP CPU UTILIZATION=3.3%
MVS STATUS=USING CPU
TOTAL CP CPU TIME=44 MINS

WAITS COUNT TOTAL CURRENT

SYNCHROUNOUS IO WAITS.... 3522680 52 MINS 0
ASYNCHROUNOUS READ IO WAITS 5899900 5.52 HOURS 0
ASYNCHROUNOUS WRITE IO WAITS 0 0 0
LOCAL LOCK /LATCH WAIT 24370 1.08 HOURS 0
rahuldba
 
Posts: 5
Joined: Wed Aug 17, 2011 12:08 am
Has thanked: 0 time
Been thanked: 0 time

Re: job is running too long

 

Re: job is running too long

Postby dick scherrer » Tue Oct 11, 2011 10:07 am

Hello,

What has changed since the last time this ran as expected?

The answer is NOT nothing. . .

The change(s) might not have been intentional.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: job is running too long

Postby rahuldba » Tue Oct 11, 2011 10:53 pm

The data in the tables havent changed anything.the programmers has complained that data seems to be same,but now the job is taking much time.There is no contentions also as well.i checked in db2pmstr about contentions but cud not see anything.
rahuldba
 
Posts: 5
Joined: Wed Aug 17, 2011 12:08 am
Has thanked: 0 time
Been thanked: 0 time

Re: job is running too long

Postby Robert Sample » Tue Oct 11, 2011 11:08 pm

Have you talked to your site support group? They should be your FIRST place to ask questions since they have the performance data for your system and have, presumably, the analysis tools as well. The numbers you posted included almost 7.5 hours of wait time, so what else was running at the same time? What else was running at the same time that wasn't running the last time you got the "2-3 minute" elapsed time? If you cannot answer these questions, your site support group can.
Robert Sample
Global moderator
 
Posts: 3376
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 224 times

Re: job is running too long

Postby dick scherrer » Wed Oct 12, 2011 12:40 am

Hello,

How many programs were promoted from test recently? How were any of the sql queries changed?

Just because the data might be the same does not prevent bad code from making an impact.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: job is running too long

Postby GuyC » Wed Oct 12, 2011 2:14 pm

you are spending 7hours doing IO. I guess it's IO-bound.
You should be able to see on which resource the IO is done.
Maybe a child-table has been created without a decent index; or an index on a childtable changed ?
If - as you claim - indexes are available for the statements itself, then often R.I. is the culprit.
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 running too long

Postby rahuldba » Sat Oct 15, 2011 1:35 am

@dick scherrer
there is no change in sql.it was simple update sql.
rahuldba
 
Posts: 5
Joined: Wed Aug 17, 2011 12:08 am
Has thanked: 0 time
Been thanked: 0 time

Re: job is running too long

Postby dick scherrer » Sat Oct 15, 2011 9:41 am

Hello,

Something has changed - as we have no access to your system, you will have to do the detecting.

Suggest you involve your dba.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: job is running too long

Postby rahuldba » Wed Oct 19, 2011 2:30 pm

what does synchronous IO waits,asynchronous read IO waits,asynchronous write IO waits signify???
i had the other job which showed the following things-


IO waits Count time
synchronous IO waits 651 3 mins
asynchronous read IO waits 570812 2 hours 14 mins
Asynchrnous write IO waits 0 0

how the following waits different??
sometimes i see synchronous IO waits more while sometimes i see asynchronous read IO waits abnormally more .what does they signify and how do they affect system performance???

Also i got some message like this-

20.32.18 *IEA480E 959F,DASD,SERIOUS ALERT,MT=2107,SER=0507-04221, 682
682 REFCODE=E475-0000-01C2,VOLSER=OTE006,ID=19,SENSE=00001F10 1F
682 33800000 0001C214 0700107D 9582E475 05100200 FE000000

however from DBA side i saw that the tablespace needs to be reorged.but then is it reorg problem or DASD problem????
rahuldba
 
Posts: 5
Joined: Wed Aug 17, 2011 12:08 am
Has thanked: 0 time
Been thanked: 0 time

Re: job is running too long

Postby Akatsukami » Wed Oct 19, 2011 6:20 pm

rahuldba wrote:Also i got some message like this-

20.32.18 *IEA480E 959F,DASD,SERIOUS ALERT,MT=2107,SER=0507-04221, 682
682 REFCODE=E475-0000-01C2,VOLSER=OTE006,ID=19,SENSE=00001F10 1F
682 33800000 0001C214 0700107D 9582E475 05100200 FE000000

Well, I shouldn't worry about it. After all, what could the people who wrote MVS possibly know? :roll:
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1054
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post