Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6

    Unanswered: Estimate index size in db2

    dear guru

    how to calculate index size in db2
    Is ther any seperate calculation for clustered index in db2

    i used the following procedure to calculate index & temp tablepspace size

    For estimating index and tablespace size in db2 i used the formula
    ------------------------------------------------------------------------------
    1) Average Row Size: The average row size is the sum of the average column sizes

    2) For each user table in the database, the number of 4 KB pages can be estimated by calculating:

    ROUND DOWN(4020/(average row size + 10)) = records_per_page

    3) Then use records_per_page with:

    (number_of_records/records_per_page) * 1.1 = number_of_pages
    The factor of "1.1" is for overhead

    4)For each index, the space needed can be estimated as:

    (average index key size + 9) * number of rows * 2

    5)(.9 * (U - (M*2))) * (D + 1)
    ----------------------------
    K + 7 + (5 * D)
    (L + 2L/(average number of keys on leaf page)) * pagesize

    For DMS table spaces, add together the sizes of all indexes on a table, and round up to a multiple of the extent size for the table space on which the index resides.

    i ran load for this table with 5,00,000 records with an tablespace of 30mb the results were

    list tablespaces show detail

    used space free space No of recordsloaded
    ------------- -------------- ------------------------
    13 MB 17 MB 5,00,000

    it was able to insert 5,00,00 records in 13MB but index estimate calculation shows 170MB .

    pl advice me on this . I had also attached an txt file with the detailed output


    Thanks
    thiru
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your post is a bit confusing to me. I have no idea what the calculations in 5) represent. You also list some strange numbers like 5,00,000 and 5,00,00 which I have idea what you really meant by these.

    In looking at your txt file, the following makes no sense since the numbers 32.42486191 bytes and 77.81966858 MB are not even close for index IDX_SCLM9973_CID.

    There is no separate calculation for a clustering index, although the amount of freespace defined for a clustering index may be different (either more or less freespace) than the amount of freespace defined for the other indexes (assuming that the freespace was defined in a rational manner by someone who knew what they were doing).

  3. #3
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6
    Dear Marcus_A

    Thanks for the reply . I accept that i had confused you.
    i am new to db2 dba . I will be gratefull if you could advice me on how to proceed with index calculations.

    Main issue is i need to insert 5 lakh records in to a table by load utility

    My index calculation was done with guidence from the below reference
    Ref:[http://www-3.ibm.com/cgi-bin/db2www/...n=c0004929.htm]

    the index tablespace size estimate goes to 170MB with this calculation for inserting 5 lakh records.

    [ Space requirements for indexes ]
    For each index, the space needed can be estimated as:
    (average index key size + 9) * number of rows * 2

    but practically i ran a load with 5 lakh records with index tablespace size of 30mb. it loaded succesfully. it was used only 13mb to insert all
    5 lakh record.


    what formula should i apply to calculate an estimated index tablespace size.


    regards
    thiru

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I looked up lakh in the dictionary (never heard of it before) and it appears that it represents 100,000 so I assume you have 500,000 rows in the table (and 500,000 entries in each index).

    As I tried to point out, the calculation on the third index on the attached txt file is in error. Here are your calculations:

    INDEX NAME ________ KEY SIZE _INDEX SIZE __ (Size In MB)

    PK_SCLM9973_CTRID ___18 ____ 26999946 ____ 25.74915504
    IDX_SCLM9973_CED _____4 ____ 12999974 ____ 12.39774132
    IDX_SCLM9973_CID ____ 25 ___ 32.42486191 __ 77.81966858

    __________________________________ TOTAL: 170 MB

    I don't know where you got these numbers, but the calculations for the last index are all wrong. This is obvious if you compare them to the calculations of the first two indexes. Even if the calculation of 77.81 MB was correct (it’s not), the total of 170MB is also calculated incorrectly.

    If you specify a separate space for indexes during the create table, then the indexes (all of them) will occupy one tablespace and the table will be in a different tablespace. Otherwise, the table and all in the indexes will occupy the same tablespace.

    Using the IBM supplied algorithm for estimating index space size, the amount of space estimated may be about 1.9 the actual space used right after a load (or a reorg). IBM suggests using a factor of 2 to account for page overhead, for disorganized (fragmented) indexes, the amount of freespace defined for the index, and for growth. The actual overhead factor may be lower in many cases.

    Nevertheless, your calculations appear to be way off even using the algorithms you say you are using.

  5. #5
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    6
    Dear Marcus_A

    i admit my mistakes done. i am very thankfull to you for advicing me and taking me to the best path .
    correct one is
    INDEX NAME ________ KEY SIZE _INDEX SIZE __ (Size In MB)

    PK_SCLM9973_CTRID ___18 ____ 26999946 ____ 25.74915504
    IDX_SCLM9973_CED _____4 ____ 12999974 ____ 12.39774132
    IDX_SCLM9973_CID ____ 25 ___ 33999932______32.42486191

    i assure there are no mistakes in the excel sheet
    i had attached an excel file in "ZIP"format which containg the formula in the cells and the value got. pl go throug this and advice.

    Today i was again able to load 5 lakh entries in each index using 13Mb of 30Mb tablespace size for the above columns.
    used space free space
    ------------- --------------
    13 MB 17 MB (all rounded)

    i found another index calculation method from this same forum
    ref:http://dbforums.com/t844838.html

    regards
    thiru
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    Also please keep in mind when you are calculating tablesize that regardless of how much space is left on a page, each page can only hold a max of 256 rows. So if a tablespace is defined with a pagesize of 16k and a fairly narrow table is placed in it, it is possible that a fair amount of space will remained unused.

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Marcus_A View Post
    I looked up lakh in the dictionary (never heard of it before) and it appears that it represents 100,000 so I assume you have 500,000 rows in the table (and 500,000 entries in each index).
    Your guess was right.

    1 lakh = 100,000
    Some people write it as lac


    http://www.merriam-webster.com/dictionary/lakh

    Regards

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    And you do realize that you have resurrected a six-year old thread ????

    Quote Originally Posted by DBFinder View Post
    Your guess was right.

    1 lakh = 100,000
    Some people write it as lac


    Lakh - Definition and More from the Free Merriam-Webster Dictionary

    Regards
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Sorry,
    I read the date on wrong order. I took it to be 30 march 2008

Posting Permissions

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