RE: SQL resource usage and monitoring

Vern,

Yes Job Watcher will help but I think the aforementioned visual explain
might be of more use right now.

At V5R4 Job Watcher was an PRPQ from Lab Services so there's a very good
chance he will not have it.

My sense is the changes to the SQL caused a table/index scan or two
somewhere, and visual explain will show that to you.

Also the other wild card is; check the cache batteries to see if they have
timed out. I see systems were normal operations tend to be "OK" if but
speedy and the user just blames it on the old box. Turns out batteries are
timed out, when we replace them, performance goes back to normal. Not
probable here but it's worth the quick look.

--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vernon Hamberg
Sent: Saturday, March 14, 2015 1:03 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL resource usage and monitoring

Darryl

JobWatcher is a tool that can show you wait states and times, and lots more.
I think it is included with the system. Jim Oberholtzer mentioned it
recently in these lists.

HTH
Vern

On 3/14/2015 8:59 AM, Darryl Freinkel wrote:

I have a complex SQL script that used to run in about 3 minutes and
processed through 700k records.



I made a bunch of changes and one of the changes has resulted in the
run time going out to 12 hours. I have all the advised indexes
required by the system.



I am trying to monitor system usage and found the following:

- CPU utilization is constantly around 40% (2 hours)

- Disk IO across all 10 drives is 1% or less (1 hours)



On the face of it, it appears the system is idle whilst it is
processing the data.



Does anyone have any advice on how to track this down. We are on V5r4
so no IBM support?

Do you know how I can use the data base monitor to analyze what is going

on?

Why can I not see where the processing is? It's almost like there is a
wait happening?



TIA



Darryl Freinkel

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.