IBM Mainframe Forum

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

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.

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.

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.

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.

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.

@dick scherrer
there is no change in sql.it was simple update sql.

Hello,

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

Suggest you involve your dba.

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 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?