Hi All
db2 v 8.1.3 Windows
I need to provide following information in single query for an inhouse
tool.
1)Schema Name
2)Table Name
3)Index Name
4)Index Type(Clustered/regular etc)
5)Col Index Type (primary/non Primary)
6)Column Name(columns used in Index)
7)Index Width(Row size of an index)
8)Row Width(Row Size of table)
9)Total Rows(Total rows in table)

I have managed to get 8 points in a single query but I am not able to show
total no. of rows also in the same query.
Here is my query with out "Total Rows"
select
t.tabschema tabschema,
t.tabname tabname,
i.indname indname,
case i.indextype
when 'CLUS' then 'Clustering'
when 'REG' then 'Regular'
when 'DIM' then 'Dimension Block Index'
when 'BLOK' then 'Block Index'
end as Index_type,
case i.uniquerule
when 'P' then 'Primary key'
else 'Non Primary'
end as Col_index_type,
replace(ltrim(replace(i.colnames,'+',' ')),' ',',') Column_name,
IL.index_length index_length,
RL.row_length row_length
from
syscat.tables t, syscat.indexes i ,
(select
i.indname indname,
sum(c.length) index_length,
i1.tabname tabname
from
syscat.indexcoluse i,syscat.indexes i1,syscat.columns c
where
i.indname=i1.indname and
i.indschema=i1.indschema and
i1.indschema=c.tabschema and
i1.tabname=c.tabname and
i.colname=c.colname and
i.indschema not in('SYSCAT','SYSIBM','SYSSTAT')
group by i.indname,i1.tabname) IL,
(select
tabname,
sum(length) row_length
from
syscat.columns
where
tabschema not in('SYSCAT','SYSIBM','SYSSTAT')
group by tabname) RL
where
t.tabschema=i.indschema and
IL.indname=i.indname and
RL.tabname=t.tabname and
t.tabschema not in('SYSCAT','SYSIBM','SYSSTAT')

Please advise.
TIA
Praveen