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
 

 

Analysis of long running DB2 batch program

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

New User


Joined: 06 May 2005
Posts: 14

PostPosted: Tue Jul 05, 2005 4:37 pm    Post subject: Analysis of long running DB2 batch program
Reply with quote

hi,
Have been asked to Analye the cause of long running DB2 batch program.

Appreciate any useful inputs on this.

Thanks
Back to top
View user's profile Send private message

parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Wed Jul 06, 2005 11:08 am    Post subject:
Reply with quote

Hi,

you should concentrate on queries being issued in the program.

There can be many factors which effects the elapsed time of DB2 program.

For example, there can be unnecessory joins, fetching unnecessory attributes ( say select * from table(s)) ...

You can use strobe utility to have a root cause analysis.

Suggestions/improvements are welcome.
Back to top
View user's profile Send private message
zatish

New User


Joined: 06 May 2005
Posts: 14

PostPosted: Wed Jul 06, 2005 1:08 pm    Post subject:
Reply with quote

Thanks for your response.
Back to top
View user's profile Send private message
alexjp

New User


Joined: 26 Apr 2005
Posts: 1
Location: Austin, TX

PostPosted: Wed Jul 13, 2005 6:43 pm    Post subject:
Reply with quote

zatish,

The first step is to EXPLAIN the program. This gives you a starting point.
This also helps you determine if the problem exists in DB2 or how the program is coded.

If you have access to one of the DB2 monitoring tools, look at the pattern/frequency of SQL execution, this would help you identify the the statement(s) with the most cost impact.

Look for excessive sorts (ORDER BY CLAUSE), OPEN/FETCH/CLOSE (You don't need a cursor definition for a singleton select), selection of unneeded columns, etc. Have your DBA look for bad Table/index design.


[/quote]
Back to top
View user's profile Send private message
Pisces

New User


Joined: 08 Sep 2005
Posts: 1

PostPosted: Fri Sep 09, 2005 7:28 am    Post subject: Re: Analysis of long running DB2 batch program
Reply with quote

Hi,

You could use either strobe or DB2 omegamon to check quickly which particular query is taking a long time.
Once you get the query, you can then check which the indexes on the tables that query is using. You can do a quick explain on that query alone.
Also sometimes the variable declaration of the host variables may be different to the one it is equating to etc

+Sav
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 Batch job tuning sgandhla Testing & Performance analysis 0 Fri Mar 24, 2017 9:41 pm
No new posts Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts MIPS/CPU consumption reduction in Batch vishwakotin DFSORT/ICETOOL 4 Sat Mar 18, 2017 5:46 pm
No new posts Determine Region with max bandwidth f... seahawk789 COBOL Programming 3 Mon Feb 27, 2017 8:26 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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