MVSFORUMS.com

[ashutosh_agl3]

What are the factors taken in consideration by DB2 to determine the access path ?

Is it possible that with the same index definition DB2 determine different paths in different data environments.

Thank You

[kolusu]

ashutosh_agl3,

Try this link which might answer your queries

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAGH10/5.10.2?DT=20010212102121

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[ashutosh_agl3]

Thanks Kolusu. This certainly is useful information.

However this does not answer my questions as I am looking to know the factors affecting the output of EXPLAIN and if the amount and type of data plays a role.

I know some what about interpreting the results of EXPLAIN but what really bothers is that if the same SQL can give different EXPLAIN output on 2 different environments where there are same indexes and same table structure except for data.

Does the link http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAGH10/5.9.1?DT=20010212102121 provide answer to the question?

Thank You

[CZerfas]

There are three main input factors DB2 has to choose an access path for a SQL statement:
- the coding of the statement
- the structure of the data
- the statistics in the DB2 catalog about the data

The most important factor is the last one: Why bother about choosing an index to access the data if the tablespace only contains three data pages? It is much more efficient to go via a tablespace scan.

This was a trivial example, of course, but it gives you the idea of choosing an access path. Looking at a join situation: Which table should be the outer and which one the inner table? Shurely "number of qualifying rows" is a main parameter to answer this question.

Therefore the essence of cost-based optimizing of access paths is the volume and the distribution of data.

regards
Christian