Dear All,
I have heard DB2 guys talking about Index Leaf Distribution........I m very much interested to know what does this mean ........
This is for mainframe , db2 v 7 ...........

Regards,
Sahana

Index Leaf Pages Out of Sequence
Index RID entries within a page are always in exact order of the index key, but sometimes the pages themselves may be not in optimum physical sequence on the disk. This happens when DB2 does a page split.

A page split happens when DB2 tries to insert a new index entry into a page, but the page is full. Then DB2 creates a new page and moves half of the index entries to the new page, leaving half on the old page. Aside from the problem of having a half empty index page, it usually creates a situation where the new page that is created is put at the end of the index, or not in the correct physical sequence next to the old page. Thus, the pages are out of sequence from a physical standpoint.

LEAFDIST
There is a value in the DB2 catalog called LEAFDIST, which is in the SYSIBM.SYSINDEXPART table (it might be in other tables since the catalog sometimes stores redundant or historical data). This value attempts to give the DBA some quantitative value to measure the degree to which index leaf pages are not in the optimum physical order. The calculation for LEAFDIST is as follows:

“100 times the average number of leaf pages between successive active leaf pages of the index. The value is -1 if statistics have not been gathered.”

If the index has just been reorganized, then there is one page (or zero pages—see next paragraph) between “successive” (logically connected) leaf pages. This assumes that no Freepages have been defined for the index. Freepages (OS/390 only) are completely blank pages created every n pages to facilitate page splits. Freepages (like Percent Free) are created during a reorg of the index. If any logically successive pages are not in order, then the number is greater than one (or zero—see next paragraph) and the average will be higher.

Actually I don’t recall if the successive pages that are physically next to each other count as 1 or as 0 in the above formula, but this could be determined by looking at the value of LEAFDIST after a reorg.

LEAFFAR and LEAFNEAR
However, LEAFDIST can be significantly distorted by the presence of few very stray pages (a very small number of leaf pages which are placed at the end of index). DB2 Version 7 has new statistics called LEAFFAR and LEAFNEAR to more accurately describe the degree of index disorganization.

A non-optimal ordering of more than one index leaf page represents a “jump” forward or backward. LEAFNEAR represents the number of “jumps” within the prefetch quantity, and LEAFFAR represents the number of “jumps” outside the prefetch quantity.

Note that these statistics are more important for queries that scan the entire index (and are likely to use prefetch) rather than using the b-tree (which is usually optimal for a smaller number of rows to be retrieved).

Note however, that when Matching Columns = 0 on an index access in the Explain, DB2 will have to read the entire index. Prefetch also has little effect on indexes that usually stay in the buffer pool, and don’t get flushed out by other index or table pages.

Sorry for the long explanation.

marcus,

Was going thru ur post, could you pls tell with example abt index split happens and how to avoid it. I believe post is for mainframe. Was going thru index split found this post.
regds
paul