DB2 Traces for DB2 commands processing

rockish · Posted: Tue Jan 05, 2010 8:59 pm

Can someone help me in finding the DB2 traces that might be required for monitoring the DB2 command processing ?

dick scherrer · Posted: Wed Jan 06, 2010 1:24 am

Hello,

Maybe it will if you explain what you are looking for in more detail or with some example(s). . .

rockish · Posted: Wed Jan 06, 2010 1:40 am

Hello,

The jobs running in my mainframes were taking longer than required (my understanding) to get completed. For instance, a job that consumes 14 CPU minutes has consumed a total elapsed time of around 720 minutes. I felt this job requires performance tuning and tries to capture some Application profiling reports. I used IBM'S APA and the report shows a very high WAIT Time being attributed to the Supervisor Control routines DB2SQL processing. I was trying to find out the various options that can cause such high WAIT times. When I tries to display the active DB2 traces in the production environment, I found many traces like STAT, ACCT, AUDIT, MON, PERFM all being turned ON. I suggested my DBA to turn OFF the AUDIT and PERFM traces as they need not be ON (i arrived at this conclusion after a bit of googling) all the time. But I got a response claiming PERFM traces are required by Omegamon to obtain reports for two reasons - EDM Pool failures and DB2 Commands processing;

Hence I would like to find out what are the exact traces that might be required for this(specifally if PERFM is needed or not). And even if PERFM traces are required, what are the IFCIDs that can be activated in Class 30 to control the overhead.

sushanth bobby · Posted: Wed Jan 06, 2010 12:23 pm

Rockish,

rockish · Posted: Wed Jan 06, 2010 9:02 pm

The CPU time and elapsed time has been the same for quite some time by now. But how can we justify that there are no Performance problems with DB2 if it consumes same time always !!!! Unless there is some problem, why does it run so long always ?

sushanth bobby · Posted: Wed Jan 06, 2010 9:20 pm

Rockish,

So the problem is not with DB2.

Robert Sample · Posted: Wed Jan 06, 2010 9:28 pm

14 minutes of CPU time in 720 minutes elapsed is about a 2% CPU consumption rate. Considering there's probably 100 to 150 address spaces running on the LPAR (and possibly many more), that consumption ratio is probably normal for your site -- especially since it seems steady. If you see this as an issue, bring it up with your management for discussion with the site support group; they can review the WLM performance and adjust parameters should it be deemed necessary. However this does not sound like an issue an application programmer will (or should) be able to do much about.

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Please check which program is top CPU consumer in the your job by using any one of DB2 performance monitoring tool (such as BMC Mainview DB2, BMC Apptune or Omegamon)

Reason for long elapsed time
====================

Wait time

o CPU wait time – Check with system admin if there is chance to increase the priority in getting CPU ( i.e. changing WLM service class by changing job class ) or reschedule job off peak time window

o Wait time in application
Check where all the time is spending

Reason for long CPU time
=================
Check where majority of the CPU time is spending (Application or DB2 )

If it is in application, try to find if any redundant processing could be eliminated. Re look at your program logic and try to tune it

If majority of CPU time is spending in DB2, try to find the top CPU consuming program and also find the top CPU consuming SQL in it.
o SQL tuning is necessary for the top CPU consuming program
- May be tablespace scan.
· Try to use indexed column to avoid it
-Non-matching index scan
· Try to use indexed column to avoid it
-Complex SQL
· Split the complex SQL into simple SQL
-Avoid no’s of SQL calls
· If any SQL is getting invoked multiple places for the same data, it is better to have it COBOL internal table , it would helpful to avoid no’s of SQLs calls
· Using Multirow fetch for cursors would help to reduce CPU and Elapsed time by 50%. Recommend to use ROWSET size as 100

Thanks
Senthil

rockish · Posted: Wed Jan 06, 2010 9:38 pm

Thanks Robert for your reply.

I will check my team for some discussion in understanding the problem (if there is any) better.

rockish · Posted: Wed Jan 06, 2010 9:58 pm

Thanks Senthil for your reply. My shop has Omegamon installed but I do not have access to it yet and hence have not checked its reports yet!!

I just got APA profiling reports and the WAIT Time is attributed like below,

dick scherrer · Posted: Thu Jan 07, 2010 9:24 pm

Hello,