|
View previous topic :: View next topic
|
| Author |
Message |
bidhucsc
New User
Joined: 24 Dec 2007
Posts: 10
Location: India
|
|
|
|
Hi,
I have 2 issues :
1. In my shop we are upgrading from DB2 V8 to DB2 9. After installation and binds, it seems DB2 is changing the access paths. The new DB2 is using different indexes than its previously using in V8.
Viz, In V8 for a program CAPPPOL for a query, it was using PCBIB091 index with MATCHCOLS 02/04. In DB2 9, its using PCBIB092 index with MATCHCOLS 01/03.
I have 100s examples like this. Is there a way to know the performance state with new DB2 9. I want to without running the transactions, if the performance has degraded or improved bu look at above info. Of any other info is required. Please let me know.
2. For same scenario, for some programs and some queries, Indexed were not use in V8 and now DB2 9 is using some index and MATCHCOLS 00/04 for a query. Due to that the batch program is giving dead lock.
Any help on above two issues will be appreciated.
Thanks
Bidhu |
|
| Back to top |
|
 |
manikawnth
New User
Joined: 07 Feb 2007
Posts: 61
Location: Mumbai
|
|
|
|
| HI can you post the query and the indexes declaration? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
This is because the statistics might have changed since your V8 bind and currently DB2 9 bind might have found the new indexes to be better than the previous one.
Do a RUNSTATS and bind again. |
|
| Back to top |
|
|
bidhucsc
New User
Joined: 24 Dec 2007
Posts: 10
Location: India
|
|
|
|
This is after fresh RUNSTATS and REORGS. I think DB2 9 is using new algorithms to determine the access paths.
Is it possible to determine the query performance look at the EXPLAIN table data ?
There are 1000s of queries so it will not be able to run through the transactions.
manikawnth, I will give you some queries in some more time.
Thanks
Bidhu |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I think DB2 9 is using new algorithms to determine the access paths. |
Without a doubt . . .
Every database software product vendor (that i've ever worked with anyway) touts improvements to the optimizer with every new release of their product  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
of course DB2 9 causes changes in Accesspaths.
Unfortunately sometimes not the best one.
IBM didn't introduce Plan Stability in DB2 9 for fun. It is exactly to counter such problems.
Make sure your PTF-Level is up to date, a lot of PTFs and new ZPARMS affect the way the optimizer works.
Estimated costs in DSN_STATEMNT_TABLE is something to look at. But it is only an ESTIMATE based on runstats & default guesses. |
|
| Back to top |
|
|
|
|
