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 9 Performance Issues

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

New User


Joined: 24 Dec 2007
Posts: 10
Location: India

PostPosted: Wed Mar 10, 2010 12:35 pm    Post subject: DB2 9 Performance Issues
Reply with quote

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

manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Wed Mar 10, 2010 3:01 pm    Post subject: @Bidhu
Reply with quote

HI can you post the query and the indexes declaration?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 10, 2010 5:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
bidhucsc

New User


Joined: 24 Dec 2007
Posts: 10
Location: India

PostPosted: Wed Mar 10, 2010 5:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Wed Mar 10, 2010 9:33 pm    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Mar 11, 2010 4:16 pm    Post subject:
Reply with quote

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
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 BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Faccing issues while creating a GDG V... sravindra_s JCL & VSAM 5 Thu Jun 23, 2016 11:26 am


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