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:
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).
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 (its 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.
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.