Size table and database in db2 9.5

georgipa · #1 (**permalink**) 03-02-11, 10:14

Hi Colleagues,

Somebody can say me how to know the size the table and database in db2 9.5. if you can send me a example is welcome.

Thank you for advenced.

ARWinner · #2 (**permalink**) 03-02-11, 10:48

Providing that your runstats are current:

Code:

with t1 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
(select 1,'Table',t.tabschema,t.tabname,t.tabschema,t.tabname,t.stats_time,t.card,ts.pagesize,double(t.fpages) from syscat.tables as t inner join syscat.tablespaces as ts on (t.tbspace = ts.tbspace) where t.type in ('T','S','H','U') 
) ,
t2 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
( select 2,'Index',i.indschema,i.indname,i.tabschema,i.tabname,i.stats_time,cast(null as bigint),ts.pagesize,double(i****eaf) from syscat.indexes as i inner join syscat.tablespaces as ts on (i.tbspaceid = ts.tbspaceid) 
) ,
t3 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages) as 
( select * from t1 
  union all 
  select * from t2 
),
t4 (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages,bytes) as 
( select t3.*,case when fpages < 0 then 0 else fpages*pagesize end as bytes from t3 
) ,
t4a (object_type_code,object_type,schema,object,tabschema,tabname,stats_time,card,pagesize,fpages,bytes) as 
( select * from t4 
  union all 
  select 3,'Total',tabschema,tabname,tabschema,tabname,cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
  group by (3,'Total',tabschema,tabname,tabschema,tabname,cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
  union all 
  select 4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
  group by (4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
  union all 
  select 5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4 
  group by (5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double)) 
  
)select t4a.*,bytes/1024 as KB,(bytes/1024)/1024 as MB,((bytes/1024)/1024)/1024 as GB from t4a 
order by tabschema,tabname,object_type_code,object

Andy

georgipa · #3 (**permalink**) 03-02-11, 11:06

Thank you AR_Winner,

You can explain me how to execute this you send me. Please.

Greethings.

ARWinner · #4 (**permalink**) 03-02-11, 11:15

It is a query. Run it like you would any other.

Andy

db2girl · #5 (**permalink**) 03-14-11, 11:25

Andy, I'm trying to execute your query and getting:

SQL0104N An unexpected token "**" was found following "s.pagesize,double(i*".
Expected tokens may include: "<factor>". SQLSTATE=42601

What is "double(i****eaf) from syscat.indexes" ?

ARWinner · #6 (**permalink**) 03-14-11, 11:31

It must have been some sort of cut and paste error. it should be: "double(i****eaf)"

Andy

ARWinner · #7 (**permalink**) 03-14-11, 11:35

It is some sort of posting error/bug. When I put the text in the message it looks fine, but when I post it it makes it look like that. I will try again. It should be: double(i. nleaf)

I had to put a blank space before nleaf so it would come out properly. Remove it before you run the script.

Andy

tonkuma · #8 (**permalink**) 03-14-11, 11:53

Quote:

... double(i. nleaf)
... Remove it before you run the script.

Blanks before or after a period which separates a qualifier and a object name would be allowed in SQL syntax.
So, removing blanks would be not neccesary.

ARWinner · #9 (**permalink**) 03-14-11, 11:58

Yes, but the blank makes it looks funny (at least to me).

Andy

db2girl · #10 (**permalink**) 03-14-11, 23:26

Thanks, I should have realized it was a posting error.

How do you get the size of LF/LOB/XML columns? Do you use some table function / admin view? I used the inspect utility in the past.

Mathew_paul · #11 (**permalink**) 03-15-11, 05:25

hi
off topic question.
is there way to find out how many % temp space is been used by particular application (agentid).

regds
Paul

nvk@vhv · #12 (**permalink**) 03-15-11, 12:47

Yep,

check "get snapshot for table.." output. It also lists Temp-Tables and the Agentid of the Owner. Tried to find an example on my databases, but had no luck.