As part of the index clean up I am writing script that would identify indexes that we might not need i.e. index on one column and there is already another index where that same column is already in the first place. have found a few indexes that according to Control Center is over 35GB.

Considering that management enjoys numbers I would like to provide space saving from each of those indexes.

9 has a new views one of them is SYSIBMADM.ADMINTABINFO that supposedly gives index info size. So, two questions on it.

1. It does not look like it breaks down size by each index rather as a summary. Am I correct on this?

2. According to the manual it says "...This value is only reported for non-partitioned tables. For partitioned tables, this value will be 0." For some reason it is returning some value by partition. Looking for clarification.



Searched and can't seam to find a way to calculate index size outside that new view. Please advise. No, I can not use Control Center as there are way too many of them to check.

Thank you.

Hmmm, no one has the answer to the above questions?

I have done some more digging and found a formula that should be used to calculate an individual index size that takes in consideration tablespace size, partitioned or not, nulls or not and one or two more things. Formula is here DB2 Database for Linux, UNIX, and Windows

What I am missing is what unit of measure this value is giving me? Based on my research I think it reports in bytes.

Formula is working but when I am comparing the output to what I get from Control Center they do not match. my formula giving me 228 vs 4KB shown in CC. Again, based on my research and suspicion I think the reason CC gives 4KB is because it is translating pages into the KB. So, if index took page and a ¼ it would report 8KB.

To make it more interesting IBM has introduced new views and one of them is SYSIBMADM.ADMINTABINFO. This view provides sizing information. This new vies does not break down sizing by index, it rolls it up at the table level. So, even so it does not work for me as I need the info per index, it does serve it purpose to confuse as whole tab index size is off from CC and the formula mentioned earlier.

For one particular table with 14 indexes following is reported:

View 5817 KB
5.68MB

CC 5805KB
5.6MB

Formula 20287KB
19.81MB

As you can see while there is a minor difference between view and the CC, there is a major spread in what IBM Formula tells me.

I do not know how IBM calculates those values in View or CC, I do know what goes into the formula and the only thing that is not static is CARD value. Last time this table had stats collected was almost 2-month back. Catalog shows 40137808, while count(*) produces 40127336. That in itself introduces more questions.

Count (*) is smaller which you would think return a smaller value, but it does not.

Anyone can help me unravel this mystery.



Thank you, Alex.