informix space usage

[noober]

Hi again. My boss wants me to make him a little graph showing (1) how much space we have allocated for our informix database (2) how much is actually in use and (3) how much is free. We run informix on AIX/RS6000s with a raw disk configuration.

Knowing just a little about how tables are allocated chunks and extents, I think you dont really allocate space to the database, but instead dole it out to tables...right?Ddoes this request make sense? I mean can you really know something meaningful at the database level or do we really need to be looking at least down to the table level?

I am perfectly willing to be wrong here and seek enlightenment.

Also, if anyone has any good queries that will help me measure our space utilization that would be wonderful.
-thanks

 

[appi]

Hi,

why'd you try the old onstat -d command ?
Within the chunks area you can see how much space is allocated (size) and how much is free (free)but be careful, the number you got is pages, so you need to multiplicate the value with your page-size to get the bytes ...
hth

 

[vpshriyan]

Hi,

Well, the dbspaces are always allocated and attached at instance level than at database or table level. Disk component is one of the siblings hanging around the processes and shared memory and objects like databases and tables are subset of the dbspaces.

The script below shows the summary of dbspaces in 4 columns:
Note: Page size assumed as 2K, change it according to your system.

database sysmaster;
set isolation to dirty read;

select
sysdbstab.name,
sum(syschktab.chksize*2048) total,
sum(chksize - nfree)*2048 nused,
sum(syschktab.nfree*2048) free
from
syschktab, sysdbstab
where
syschktab.dbsnum = sysdbstab.dbsnum
group by 1 ;

A similar output can be genereated by:
onstat -d

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
&quot;To live is to change; and to be perfect is to have changed often.&quot;

 

[tm88gt]

Send me an email and I can send you a perl script that I use to monitor space usage. The output shows the dbspace#, dbspace name, # of chunks, # of pages allocated, # of pages free, and % full.

The output looks similar to this:

DB spaces report Thu Oct 23, 2003 22:47

DB Space Owner Chks Pages FreePages %Full Flgs
====================== ========== ==== ============ ============ ====== ====
23 llog informix 1 250000 4947 98.02 N
11 rpttbl_large informix 2 150000 50992 66.01 N
27 cshlob_paymodel informix 3 287454 112982 60.70 SB
19 cshtbl_gameoc informix 1 250000 107099 57.16 N
25 cshlob_gconfig informix 4 937561 427864 54.36
16 rptidx_large informix 1 25000 11821 52.72 N
20 cshidx_gametxn informix 1 50000 23978 52.04 N
9 cshtbl_medium informix 2 150000 72458 51.69 N
2 cshtbl_gametxn informix 1 150000 76731 48.85 N
4 cshidx_medium informix 1 50000 25871 48.26 N
24 cshlob_transform informix 3 718756 375979 47.69
15 cshidx_small informix 1 25000 13702 45.19 N
14 cshtbl_large informix 3 300000 166390 44.54 N

 

[tm88gt]

it looks better on a bigger window:

 

[noober]

Hi tm88gt...is there a way in the forum to get you email address? I clicked on your profile name but did not see it anywhere. Thanks for trying to go out of the way to help!

 

[rhenderson2]

Hi tm88gt,

I'd sure to get your email address too. Sounds like a great perl script.

Rudy.