Table/Database Size?

[nilin]

How to get Table / DataBase Size in Informix 7.3?

 

[vpshriyan]

Hi,

The following script maps database, table name, number of extents and their size:

database sysmaster;
set isolation to dirty read;
select
dbsname,
tabname,
count(*) num_of_extents,
sum(pe_size) total_size
from
systabnames, sysptnext
where
partnum = pe_partnum
and partnum > 99
and dbsname <> &quot;sysmaster&quot;
and dbsname <> &quot;sysutils&quot;
and tabname <> &quot;TBLSpace&quot;
group by 1,2
order by 1,4 desc ;

Regards,
Shriyan

 

[nilin]

Hi Shriyan,

Thanks for reply
But can you explain me this SQL (num_of_extents & total_size) ? like what this count(*) returns and what is the relation between num_of_extents & total_size?

Is it in KB , MB or i need to multiply both to find my table and database size in total? does it gives size of indexes too?

I need to put this in 4GL and print report out of it. and very important i need to understand it.

sorry to bother you,
thanks in advance
Sandeep/Nilin

 

[vpshriyan]

Hi Sandeep/Nilin,

The sizes are in Pages. You need to multiply reported figures with 2 or 4 K, as the case may be, in order to get the size in Kb.

Count(*) reported in the SQL is the number of extents spawned and in use for an object. You may ignore it, if it is irrelevant to you. However, it is to be noted that the higher the number of extents, poor will be the object performance. Further, a 2K page size hardware can only accept max of 200 of extents in an object, after that the object need to be altered. Yes, the SQL reports indices too.

Below is an altered version of same SQL to report size of each databases that you may be having in an instance:

set isolation to dirty read;

-- database size (includes indices)
-- columns are:
-- dbname pages_allocated pages_used (all in Page Size)

select
dbsname,
sum(ti_nptotal) pages_allocated,
sum(ti_npused) pages_in_use
from sysmaster:systabnames, sysmaster:systabinfo
where partnum = ti_partnum and tabname != &quot;TBLSpace&quot;
group by 1

Regards,
Shriyan