Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Unanswered: Index Size Calculation

    Using DB2 9.5...
    In the control center I see some statistics related to Index-Sizes. I want to know how the sizes are calculated.
    Can someone tell me what are the queries used to calculate those figures.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    You can query the DB2 catalog tables to retrieve the number of index levels and number of leaf pages. Furthermore, you can determine the average key length and the page size for index pages. Based on that, you can determine the fan-out on a single index level: page size / avg key length. This and the index tree height gives you the estimated number of non-leaf nodes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2009
    I believe I can pick number of index levels (NLEVELS) and number of leaf pages (NLEAF) from SYSCAT.INDEXES and pagesize (PAGESIZE) from SYSCAT.TABLESPACES .
    I am not sure abt the key-length thing that u r talking about. Can u please elaborate..?? Moreover I could not arrange the things into a formula to get those figures I see at my control center.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts