Anand and Deepak:
One other thing to add: if you are using DB2 V6, IBM has added
"hints." Using this method of changing the access path, you point
DB2 at an
old access path in a PLAN_TABLE. So, if an access path changes to
is less desirable than a previous access path, simply rebind and
the old PLAN_TABLE rows to tell the optimizer the access path to
the OPTHINT parameter).
Of course, you could also code your own PLAN_TABLE entries to
specify a new
access path and specify that to DB2 using the OPTHINT capability.
need to be careful that the access path you manually code is
possible and actually performs better...
Craig S. Mullins
Director, DB2 Technology Planning
[login to unmask email]
[login to unmask email]
>Date: Thu, 6 Jan 2000 12:45:51 +0530
>From: "Thakral, Deepak Kumar (CTS)"
<[login to unmask email]>
>Subject: Re: Access Paths
>This message is in MIME format. Since your mail reader does not
>this format, some or all of this message may not be
>When do you need to optimize access paths :
>1. Usually, the optimizer in-built in DB2 takes care of
>optimum Access path. Sometimes however, the optimizer is not
>and the application analyst understands the nature of the data
>DB2. Then you can influence the optimzer into choosing an
access path that
>you think is a better one but which the optimizer thinks is
>HOW TO OPTIMIZE ACCESS PATHS
>1. You need to run RUNSTATS to determine accurate statistics
about your SQL
>statements. The following characterstics about DB2 objects are
>2)The cardinality of tablespaces, tables,columns and
>3)The column range.
>All these factors are considered by the optimizer when it
choosed what it
>deems to be the optimal access path for a given SQL
>2. Also DB2 EXPLAIN statement places information about the
access paths in
>PLAN_TABLE , which can be inspected by a technical analyst.
>3. The optimizer takes the size of the bufferpools into account
>determining access paths. As the size of the bufferpool
>assumes that read effeciency increases also. Watch out for
>4. The optimizer also takes into account the type of CPU being
>access path selection.
>There are four ways to influence the optimizer 's access path
>1) Standard DB2-based methods such as using RUNSTATS utility or
>2) Tweaking the SQL statement.
>3) Optimize for n-rows.
>4)Changing DB2 catalog statistics.(usually done only by SYSADM
>5. Last but not the least, make sure you have a plan for
>components of DB2 programs, that is both static and Dynamic SQL
>Static. Also make sure that you follow every SQL statement by a
>SQLCODE or SQLSTATE.
>If you really want to go into details about all these, I guess
>look into Craig Mullins' DB2 Developers Guide, Ch.21 from where
most of my
>knowledge of RUnstats and access path optimization is
>Hope this helps .
>-----Original Message-----From: Anand Jeyapaul
[mailto:[login to unmask email]
>Sent: Thursday, January 06, 2000 12:02 PM
>To: [login to unmask email]
>Subject: Access Paths
>New Year Greetings.
>Can anyone tell me when should we optimize access paths in sql
and how do