Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Traces for DB2 commands processing

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Tue Jan 05, 2010 8:59 pm    Post subject: DB2 Traces for DB2 commands processing
Reply with quote

Can someone help me in finding the DB2 traces that might be required for monitoring the DB2 command processing ?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 06, 2010 1:24 am    Post subject:
Reply with quote

Hello,

Maybe it will if you explain what you are looking for in more detail or with some example(s). . .
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Jan 06, 2010 1:40 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jan 06, 2010 12:23 pm    Post subject:
Reply with quote

Rockish,

Quote:
job that consumes 14 CPU minutes has consumed a total elapsed time of around 720 minutes.

For this job check the how much of cpu time it has taken in the past week or month. If its nearly same or around same, problem is not with DB2.

Sushanth
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Jan 06, 2010 9:02 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jan 06, 2010 9:20 pm    Post subject:
Reply with quote

Rockish,

So the problem is not with DB2.

Quote:
But how can we justify that there are no Performance problems with DB2 if it consumes same time always

Other jobs(productive or unproductive) is taking a lot of CPU, which your job is not getting.

You can wait for experts to answer.

Sushanth
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8004
Location: Bellevue, IA

PostPosted: Wed Jan 06, 2010 9:28 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Wed Jan 06, 2010 9:36 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Jan 06, 2010 9:38 pm    Post subject:
Reply with quote

Thanks Robert for your reply.

I will check my team for some discussion in understanding the problem (if there is any) better.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Jan 06, 2010 9:58 pm    Post subject:
Reply with quote

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,

Code:
Name         Description         Percent of Time in WAIT * 10.00%  ±0.3%       
                                     *....1....2....3....4....5....6....7....8.
XXXXXXX-001  TCB=0087BE88       90.43 lllllllllllllllllllllllllllllllllllllllll
± DB2SQL       SQL Processing   90.16 lllllllllllllllllllllllllllllllllllllllll
  ± 00013        XXXXXXXX(2855)  73.82 lllllllllllllllllllllllllllllllllllll   
                 OPEN                                                         
    ± IEAVEPS1     Supervisor   73.80 lllllllllllllllllllllllllllllllllllll   
                   Control                                                     
    ± DSNBBM       DB2 Services  0.00                                         
    ± DSNIDM       DB2 Services  0.00                                         
    ± DSNK2DM      DB2 Services  0.00                                         
    ± 24772xxx     Unresolved    0.00                                         
                   Address                                                     
    ± DSNXGRDS     DB2 Services  0.00                                         

Note - Module name replaced with XXXXXXXX for security reasons

A very high percentage of the the Wait time is attributed the Supervisor Control routine IEAVEPS1. Do u have any idea how to proceed further with this ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 07, 2010 9:24 pm    Post subject:
Reply with quote

Hello,

Quote:
why does it run so long always ?
Due to the volume of data to be processed, the lack of proper keys/indexes, or poorly constructed queries

Quote:
A very high percentage of the the Wait time is attributed the Supervisor Control routine IEAVEPS1. Do u have any idea how to proceed further with this ?
I believe looking for this (the cause of the wait) is looking in the wrong direction. On "things" that are i/o bound there is always a high amount of wait time.

Suggest you look instead at how the process is implemented. I suspect that when this was initially implemented, the testing was done with very small amounts of data and appeared to run ok.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Asynchronous CICS Processing Question moezbud CICS 0 Mon Mar 06, 2017 6:13 pm
No new posts Execute TSO ACF commands from COBOL-I... supreethi.srid TSO/ISPF 0 Tue Feb 21, 2017 5:19 pm
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts Run stats processing on zIIP Engine Virendra Shambharkar DB2 9 Fri Oct 14, 2016 10:24 am
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us