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
 

 

Access Path Selection - Help Needed

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

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Wed Jul 28, 2010 10:46 am    Post subject: Access Path Selection - Help Needed
Reply with quote

Hi,

One of my job uses a REXX utility step which executes a DB2 query. It was running for more than 5 hours and we cancelled the job.

When we ran the same query again after sometime it ran within 1 hour.

We did a EXPLAIN and we did not see any hot spots.

Our DBA tells us that

Quote:
"It looks like the BD2 access path took a turn for the worse which can happen from time to time with dynamic sql".


But the query by itself is not dynamic only the plan get created dynamically. Can we still say it is dynamic query?

Code:
My question is will access path gets changed dynamically? if so can we force Db2 to select the optimal access path?

Can some please help me in understand the selection of access path.

PS: I think DB2 optimizer will select the best available optimal path?

Your help is really appreciated.

Regards,
Chinni
Back to top
View user's profile Send private message

sambit_mech

New User


Joined: 14 Jul 2010
Posts: 13
Location: India

PostPosted: Wed Jul 28, 2010 11:04 am    Post subject:
Reply with quote

I think your bind card contains VALIDATE(RUN) instead of VALIDATE(BIND).
Back to top
View user's profile Send private message
chinnielr

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Wed Jul 28, 2010 11:18 am    Post subject: Reply to: Access Path Selection - Help Needed
Reply with quote

No there is no BIND card for this. This is just a query getting executed from a step.

Regards,
Chinni
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Jul 28, 2010 2:10 pm    Post subject:
Reply with quote

a little reading is in order, for all of you:

start with the IBM REDBOOK Squeezing the Most Out of Dynamic SQL with DB2 for z/OS and OS/390

I believe a thorough read will answer all of your questions,
and correct your miss-conceptions.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jul 28, 2010 4:38 pm    Post subject:
Reply with quote

afaik Rexx is always dynamic.

Access path should/can only change if some statistics have been run on the underlying tables
Back to top
View user's profile Send private message
chinnielr

New User


Joined: 11 Jul 2005
Posts: 87

PostPosted: Wed Jul 28, 2010 6:47 pm    Post subject:
Reply with quote

Thanks for your quick responses. Can you please let me know whether we can force DB2 to select the opimal access path? Not sure if we can do so.

Regards,
Chinni.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jul 28, 2010 6:55 pm    Post subject:
Reply with quote

there are some techniques to make DB2 favor some indexes or some tables as outer table like " or 0=1"

otherwise SET CURRENT OPTIMIZATION HINT might do the trick if you can figure it out : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.admin/p9li375.htm#p9li375
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Jul 28, 2010 6:56 pm    Post subject:
Reply with quote

Giving optimization hints to DB2 VSN 8
Giving optimization hints to DB2 VSN 9

article on db2 optimization hints
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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts IMS DC screen Access sivasaras IMS DB/DC 3 Fri Nov 18, 2016 6:38 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am
No new posts RACF profile access vasanthz All Other Mainframe Topics 11 Fri Sep 23, 2016 5:51 am


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