Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 The same REXX program doesnt work for... jackzhang75 CLIST & REXX 5 Wed Dec 06, 2017 2:51 am
No new posts CA Intertest Batch 555shanth CA Products 0 Tue Nov 28, 2017 11:10 am
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Calling IKJEFT01 program from Rexx upendrasri CLIST & REXX 8 Wed Nov 15, 2017 4:27 pm
No new posts Job is running with no response!!! Vignesh Sid DB2 3 Fri Oct 27, 2017 6:28 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us