Monthly Archives: November, 2016

informix:dbspace size

Note: The below query lists all the available dbspaces and their storage allocated/free in MB. This query is tested on IDS 12.1

database sysmaster;
select name dbspace, sum(chksize) allocated, sum(nfree) free,
round(((sum(chksize) – sum(nfree))/sum(chksize))*100) pcused ,
sum(chksize) *2/1024 MB_Allocated,
sum(nfree)*2/1024 MB_Free,
round (((sum(chksize) – sum(nfree))/sum(chksize))*100 ,2) per_used
from sysdbspaces d, syschunks c
where d.dbsnum = c.dbsnum
group by name
order by pcused desc

Advertisements

Informix: database size query

Note: The below query gives database(s) size in MB. This is tested on IDS 12.1

 

database sysmaster;
SELECT stn.dbsname[1,35] db_name,
SUM
(
sti.ti_npused *
(
select pagesize from sysdbspaces where name
= dbinfo(‘dbspace’, sti.ti_partnum)
)/1024/1024
) mb_used,
SUM
(
sti.ti_nptotal *
(
select pagesize from sysdbspaces where name
= dbinfo(‘dbspace’, sti.ti_partnum)
)/1024/1024
) mb_total
FROM systabnames stn, systabinfo sti, sysdatabases sdb
WHERE stn.partnum = sti.ti_partnum AND stn.dbsname
= sdb.name GROUP BY 1 ORDER BY 1;

 

Output:

db_name      mb_used                   mb_total

setdb              5.16210937500000 6.34375000000000
sysadmin      5.66210937500000 7.83593750000000
sysmaster     3.06835937500000 4.19531250000000
sysuser          2.38476562500000 3.45312500000000
sysutils          2.44921875000000 3.74414062500000
testdb2           2.27539062500000 3.31250000000000