Analysis of long running DB2 batch program

zatish · New User Joined: 06 May 2005 Posts: 14

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

Appreciate any useful inputs on this.

Thanks

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

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.

zatish · New User Joined: 06 May 2005 Posts: 14

Thanks for your response.

alexjp · New User Joined: 26 Apr 2005 Posts: 1 Location: Austin, TX

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]

Pisces · New User Joined: 08 Sep 2005 Posts: 1

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