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

I am not sure if this makes sense, but i will try.

1. CREATE a PROCEDURE that accepts SCHEMA/TABLE name as IN parameters,
and gives the count as an OUT partameter. (Example below)

2. Wrap your existing query in another PROCEDURE and put the results
into a GLOBAL TEMPORARY TABLE.

3. Using a FOR loop, UPDATE the GLOBAL TEMPORARY TABLE using the
PROCEDURE that gets table row counts.

4. Start a new block in the PROCEDURE and DECLARE a CURSOR WITH RETURN
TO CALLER, that SELECTs everything in the GLOBAL TEMPORARY TABLE.

If that is done, only one statement is required to get the information.
Albeit it is CALL as opposed to SELECT.

--

A PROCEDURE to get row counts can be done as follows:

DROP SPECIFIC PROCEDURE Get_Row_Count

CREATE PROCEDURE Get_Row_Count
(
IN TabSchema VARCHAR(128),
IN TabName VARCHAR(128),
OUT Row_Count INTEGER
)
SPECIFIC Get_Row_Count
BEGIN

DECLARE Query_Text VARCHAR(293);

DECLARE Get_Count CURSOR FOR Query;

SET Query_Text = 'SELECT COUNT(*) FROM ' || TabSchema || '.' ||
TabName;

PREPARE Query FROM Query_Text;

OPEN Get_Count;
FETCH Get_Count INTO Row_Count;
CLOSE Get_Count;

END

HTH,

B.

Thanks Brian
Actually I was just wondering whether db2 keeps the no. of rows for the
tables in any catalog tables.If we can get it then my job is done.

Praveen

Praveen_db2 wrote:

> Thanks Brian
> Actually I was just wondering whether db2 keeps the no. of rows for the
> tables in any catalog tables.If we can get it then my job is done.

The number of rows is not kept in the catalog, at least not in an always
up-to-date manner. The simple reason is that this would be a terrible
bottle-neck as it requires an X-lock on the respective row in the table,
effectively preventing other transactions to access this very row and,
therefore, the access to the table.

If you update the statistics regularly, you can query SYSCAT.TABLES.CARD.
It will contain the number of rows that existed in the table when the
RUNSTATS command was last executed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thanks Knut.