DB2 Optimizer

Mike Toppins

DB2 Optimizer
Could someone please give me a brief summary of how the optimizer determines
how to choose access paths. We've got some dynamic SQL jobs that run every
day. One day, they will choose to use the index(es) that we've set up to be
used and the next day, it uses different ones.



Roger Miller

Re: DB2 Optimizer
(in response to Mike Toppins)
The optimizer looks through the query and table statistics at a very
detailed level
to determine costs for the query, then uses the access paths which compute
as
the lowest total cost.

There are significant differences from version to version and sometimes in
PTFs.
For a meaningful description, start with the Administration Guide chapter
"Tuning
Your Queries" for some of the most common situtations and tuning. If you
want to
see the statistics in a bit more detail, that's in the "Maintaining
Statistics in the
Catalog" chapter. To look at detailed analysis, then there is the "Using
EXPLAIN
to improve SQL Performance" chapter.

Every release has a red book that discusses the optimizer improvements with
some measurements. There are classes from IBM and others.

Roger Miller


"Toppins, Mike" <[login to unmask email]>@RYCI.COM> on 12/30/99 08:54:34 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: DB2 Optimizer



Could someone please give me a brief summary of how the optimizer
determines
how to choose access paths. We've got some dynamic SQL jobs that run every
day. One day, they will choose to use the index(es) that we've set up to be
used and the next day, it uses different ones.