Increase in DB2 CPU utilization

Matthew Sherman

Increase in DB2 CPU utilization
This past Monday, I noticed a decrease in the Performace Index for our DB2
Stored Procedure Period 2 Service Class. It was suddenly missing it's
goal regularly (PI > 1.0), where before its PI had been around .5 - .7.
Some changes were implemented over the weekend; a patch for HSM and an
implementation of some new Stored Procedures and Tables. However, these
new Tables each had less than 1000 rows and the new Stored Procedures were
not being executed frequently. Within TMON MVS, I am seeing our DB2WLM
address space being frequently delayed by DFHSM, but I wouldn't think that
would account for the increase in CPU utilization. A general CPU increase
(which seems to be attributed to this DB2 Stored Procedure period 2
Service Class) is evident when looking at TMON MVS, SDSF, and the IBM
canned report "Workload Utilization for System: ???". What I'm seeing in
TMON DB2's RWRP12 (Package Summary) report is a definite increase in
elapsed time, but not in TCB time. Now this could be because the TCB time
on this report does not include enclave time, but I see very large numbers
for TCB time for some Stored Procedures (hours worth) so I wouldn't think
this is the case. Using the RMF "Workload Activity Report", it does not
appear that the USING% field includes enclave time for Stored Procedures,
either.

I'm trying to find a way to track this down using the tools I have (TMON
DB2 & RMF) but haven't been successful. I believe I really need to see
the enclave times and trend those, to see where the increase is. What's
strange is this occurred all at once, on Monday, and I see no individual
culprit. It just looks like a general increase in CPU and a slow down of
our high-volume Stored Procedures. I'm looked at CICS, MQ, and DFHSM and
the only negitive PI impact was on DFHSM. I don't have any previous
numbers (other than PI) for DFHSM, but it does look like it is delaying
the DB2WLM (where the Stored Procedures run) and DB2WLM is delaying
everything else, when looking in the TMON MVS online Delay Monitor.

If anyone has any ideas or previous experience with a similar problem, I'd
be very interested in them.

Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Venkat Srinivasan

Re: Increase in DB2 CPU utilization
(in response to Matthew Sherman)
Inspite of the many numbers, it is still too little and leaves room to
guess.
1.0 I am not able to comprehend increased CPU utilization while missing PI.

What is the difference in Absorption rate?. Did SU's increase?.

2.0 More than the WAC report itself, you should be looking at Overview
reports. Concentrate on MPLDELAY, UNKNOWN state and ENDED transactions.
Use WAC report with SCPER so you are looking at all service classes.

3.0 If you had increased UNKNOWN then you need to look at enques, pending
replies for MVS and Reacall and tape mount delays. Since you are
mentioning HSM, what about migrated datasets?. If what is attributed to
UNKNOWN increases, it could result in missed goals.

4.0 When you say, DB2WLM is delaying everything how have you classified in
WLM?.
5.0 You also said in your note HSM is delaying DB2WLM?. How are they
classifed with respect to each other?. Is it in such a way that HSM is
delaying DB2WLM and DB2WLM is delaying everything else?. That is a vague
combination.

6.0 Are you able to compare DB2 accounting numbers?. Any changes in class-
2 clapsed time -(class 3 + class 2 cpu time)?.

7.0 You said the new tables have less than 1000 rows. Could it be so that
most of the workload is just scan in which case DB2 can have increased cpu
utilization?. If you scan a 1000 row table 10000 times, it has to be done
with quite some CPU.

I think you need to see the unknown state first and see any apparent
cause. Absorption rate should be compared to see increases in msu.

What do you see in RMF mon-3, primary reason for job delays?.,,,That
should show you some pointers.

Regards,
Venkat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm