Access Path Selection - Help Needed

chinnielr · New User Joined: 11 Jul 2005 Posts: 87

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

sambit_mech · New User Joined: 14 Jul 2010 Posts: 13 Location: India

I think your bind card contains VALIDATE(RUN) instead of VALIDATE(BIND).

chinnielr · New User Joined: 11 Jul 2005 Posts: 87

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

Regards,
Chinni

dbzTHEdinosauer · Posted: Wed Jul 28, 2010 2:10 pm

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.

GuyC · Posted: Wed Jul 28, 2010 4:38 pm

afaik Rexx is always dynamic.

Access path should/can only change if some statistics have been run on the underlying tables

chinnielr · New User Joined: 11 Jul 2005 Posts: 87

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.

GuyC · Posted: Wed Jul 28, 2010 6:55 pm

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 : publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.admin/p9li375.htm#p9li375

dbzTHEdinosauer · Posted: Wed Jul 28, 2010 6:56 pm

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

article on db2 optimization hints